Ruby on Rails has a good support for Postgres Array type. I really like using this feature when creating a separate database table sounds like over-engineering. In this short post I want to share my solution for the following problem: “find a record for which any of its tags contains a given string”.
Book model with
categories array field as an example. We start by generating a migration file:
and then we have to change it and specify
array: true, default: .
1 2 3 4 5 6 7 8 9 10
As a next step we create a few records for testing:
1 2 3
If we want to find records by the exact name of the category it is very simple. Postgres has it covered with
How about making our search a little bit more robust and supporting pattern matching? In case of a regular field we would do that using
Unfortunately this approach does not work with
This query returns zero results, because the pattern containing
% has to be on the right hand side of
And this is an unsupported syntax producing a syntax error.
We can work around this problem by using
array_to_string concatenates all the categories, using
|| as a separator between them and then we can use
ILIKE in a usual way. The assumption here is that the pattern will never contain
This approach may not be the best idea if the number of rows is big. However, for a table with a couple hundreds rows it was the simplest solution I could come up with.