Can I use subquery in fragments?


I am stuck with a problem and can’t seem to find an easy solution.

I have a column on postgres, it is an array of integers ([]int4), and I want to check if one array overlaps the other. I was able to do this with fragments:

group_ids = [1,2]
|> where([e], fragment("? && ?", , e.group_ids))

But the problem is that group_ids is from another query, and I want to use as a subquery, like this:

|> where([e], fragment("? && ?", subquery(group_ids_query), e.group_ids))

The only solution that worked for me was querying first the group_ids and then do the main query. But it is something that I can do in a single query with SQL, but can’t do it with Ecto.

Is there a way to use subqueries inside fragments?
If not, is there any alternative?

Maybe you can solve this by using with_cte?

|> with_cte("group_ids", as: ^subquery(group_ids_query))
|> where([e], fragment("(SELECT * FROM group_ids) && ?.group_ids", e))

Not sure. I’d be surprised if this worked. :grinning_face_with_smiling_eyes: