Use PostgreSQL array_to_string in Phoenix

Question

I have 2 n-n relationships between posts and tags tables. This is my query in Postgres:

SELECT t0.*, array_to_string(array_agg(t2.tag), ', ')
FROM "posts" AS t0
INNER JOIN "posts_tags" AS t1 ON (t0.id = t1.post_id)
INNER JOIN "tags" AS t2 ON (t1.tag_id = t2.id)
GROUP BY t0.id

I tried to use something similar in Ecto:

Repo.all(
  from p in Post,
    join: a in Post_Tag, on: p.id == a.post_id,
    join: t in Tag, on: a.tag_id == t.id,
    select: {p, array_to_string(array_agg(t.tag),', ')},
    limit: ^limit,
    offset: ^offset,
    group_by: p.id
)

But I get this error:

(Ecto.Query.CompileError) `array_to_string(array_agg(t.tag()), ', ')` is not a valid query expression.

Justin Wood · Accepted Answer · 2018-10-30 17:20:47Z

2

You will need to use a fragment/1 for the array_to_string(...) portion of your query. I have not tested it, but it should look something like:

fragment("array_to_string(array_agg(?), ', ')", t.tag)

answered Oct 30, 2018 at 17:20

Justin Wood

10.1k2 gold badges35 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Use PostgreSQL array_to_string in Phoenix

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related