Raw SQL query for searching for an item in array

Questions / Help
1

Hi fellas,

It’s my first time asking something here, here it is:

I’ve checked couple of resources (here and the official docs), yet could not succeeded on writing a proper query for searcing an item on an array field (genres).

Below works,

iex(21)> Ecto.Adapters.SQL.query(ArtistsApp.Repo, "select * from musicians where array_to_string(genres, ',') like 'Alter%'")
[debug] QUERY OK db=1.5ms queue=3.4ms idle=1035.1ms
select * from musicians where array_to_string(genres, ',') like '%lternative%' []
{:ok,
 %Postgrex.Result{
   columns: ["id", "name", "discogs_id", "details", "countries", "genres",
    "external_urls", "inserted_at", "updated_at", "image_cover",
    "image_thumb"],
   command: :select,
   connection_id: 31704,
   messages: [],
   num_rows: 1,
   rows: [
     [
       1,
       "Gaye Su Akyol",
       123123,
       "dasdasd",
       ["'Turkey'"],
       ["'Rock'", "'Alternative'"],
       nil,
       ~N[2020-09-10 15:10:30.000000],
       ~N[2020-09-10 15:10:30.000000],
       nil,
       nil
     ]

However when I want to query with variable, it returns zero rows.

Ecto.Adapters.SQL.query(ArtistsApp.Repo, “select * from musicians where array_to_string(genres, ‘,’) like ‘$1’”, [“Alter” <> “%”])

So far I’ve tried with like, ILIKE and without quotes around $1 to experiment. But they’ve failed.

Could you help me on how to make it work?

Thank you very much!

2 
iex(21)> Ecto.Adapters.SQL.query(ArtistsApp.Repo, "select * from musicians where array_to_string(genres, ',') like 'Alter%'")
[debug] QUERY OK db=1.5ms queue=3.4ms idle=1035.1ms
select * from musicians where array_to_string(genres, ',') like '%lternative%' []

^ Those are two different queries: 'Alter%' vs. '%lternative%'. The latter one works because it doesn’t need to match those inner single quotes you seem to have for the genres: "'Alternative'" vs. "Alternative". I’d suggest cleaning those up before proceeding.

1 Like
3

Hi Kobrakai,

Thank you for your reply. As you’ve pointed out, it required me to include %s on both sides.

Finally managed to make it work with a query something like this:


query = "select * from musicians where array_to_string(genres, ',') like '%#{sanitized_prefix}%' and "  <>
                                            "array_to_string(countries, ',') like '%#{locality}%'"

{:ok, result} = Ecto.Adapters.SQL.query(MyApp.Repo, query, [])
4

Your problem is probably not the query, but that your genres column does include single quotes, which likely shouldn’t be there. Essentially you’re searching for 'Alternative' and not Alternative.

1 Like
5

Not directly related to your question, but take note of the “Tip” at the bottom of 8.15.5 “Searching Arrays” in the Postgres docs. Matching the results of array_to_string is going to be difficult-to-impossible to use an index for…

6

Thank you for the recommendation, will totally re-design that part!