Join with array - from sql to ecto?

Questions / Help
,
1

Hi,

i´ve a working query with:

select u.id, u.lastname, array_agg(t.name)
from users as a
cross join unnest(tag_ids) as tag
join tags t on t.id = tag
group by u.id;

How would i put this with ecto?

So far i have found out i probably (not sure though) need something like:

fragment("unnest(?)", u.tag_ids) 
fragment("array_agg(DISTINCT ?)", t.name)

Problem: I don´t know how and where to put it.

Please help me.

1 Like
2

I am new to this too but I found a blog post where the ecto joins are being explained. I am not sure if the way described in the article is the best way or a best practice.

Here is the link https://www.pompecki.com/post/sql-joins-with-ecto/

Hope it helps you.

3

Thanks, but this special case is not mentioned there.

4

Are you 100% sure you need CROSS JOIN? Because from what I see you need INNER JOIN tags t ON t.id = ANY(tag_ids) and this is supported by Ecto via in/2.

4 Likes
5

@hauleth no :slight_smile:

thank you