How to concatenate strings on related column like STRING_AGG with sort?

I have a simple has_many relation with parts:

has_many :parts, Part

has_many :parts, Part

And each part has a simple text field and a part number:

attribute :text, :string
attribute :part_number, :integer do

attribute :text, :string
attribute :part_number, :integer do

I'd like to concatenate all text fields, sorted by part number, similar to how one would do it with STRING_AGG in postgres, preferably in a calculation. I was hoping a simple string_join would do it:

calculate :text, :string, expr(string_join(parts.text))

calculate :text, :string, expr(string_join(parts.text))

But alas that doesn't work:

* ** (Postgrex.Error) ERROR 42883 (undefined_function) function array_to_string(text, unknown) does not exist

* ** (Postgrex.Error) ERROR 42883 (undefined_function) function array_to_string(text, unknown) does not exist

Any ideas?

10 Replies

barnabasj•2mo ago

Looking at the error message, have you tried

calculate :text, :string, expr(string_join(parts.text, ","))

calculate :text, :string, expr(string_join(parts.text, ","))

or whatever seperator you want

jonas_hOP•2mo ago

Yeah sorry, that gives me the same error message

barnabasj•2mo ago

ah, I think you need an aggregate

jonas_hOP•2mo ago

Maybe I'm supposed to use aggregates instead. The documentation has some elusive reference to a "StringAgg" here: https://hexdocs.pm/ash/dsl-ash-resource.html#aggregates-custom There's a hint in AshPostgres too but that doesn't help me very much: https://hexdocs.pm/ash_postgres/AshPostgres.CustomAggregate.html

barnabasj•2mo ago

calculate :text, :string, expr(string_join(list(parts, field: :text), ","))

calculate :text, :string, expr(string_join(list(parts, field: :text), ","))

jonas_hOP•2mo ago

Yes! Thank you! And now I also need to keep it sorted. I've tried to add sort under the has_many relationship:

has_many :parts, Part do
  sort part_number: :asc
end

has_many :parts, Part do
  sort part_number: :asc
end

That sorts fine when I fetch the parent record and related fields, but it doesn't seem to have any effect on the calculation My mistake! Sorting in the relation does work, I just messed up my tests 🙂 As an aside, you can accomplish the same with an inline postgres fragment like so:

    calculate :text,
              :string,
              expr(
                fragment(
                  "SELECT STRING_AGG(text, '' ORDER BY part_number ASC)
                   FROM parts
                   WHERE parent_id = ?",
                  id
                )
              )

    calculate :text,
              :string,
              expr(
                fragment(
                  "SELECT STRING_AGG(text, '' ORDER BY part_number ASC)
                   FROM parts
                   WHERE parent_id = ?",
                  id
                )
              )

Although using string_join is a lot nicer

barnabasj•2mo ago

it looks like you should be able to pass a sort to the aggregate

calculate :text, :string, expr(string_join(list(parts, field: :text, sort: [part_number: :asc]), ","))

calculate :text, :string, expr(string_join(list(parts, field: :text, sort: [part_number: :asc]), ","))

you should be able to pass the options you see for aggregates in the dsl to inline aggregates using the keyword list syntax allthough you could argue that we should honor the sort of the relationship. would you mind opening an issue on github for that please

barnabasj•2mo ago

jonas_hOP•2mo ago

Yeah sorry, it does work I just messed up my tests Everything works great now 🙂 Although I'm not able to find the documentation for the list inside the expression

barnabasj•2mo ago

yeah, the docs are a bit scattered for those, there is a small section about inline aggregates in the calculation guide and the rest is in the dsl docs. But it's not that easy to discover how that works.

Gaming

Programming

How to concatenate strings on related column like STRING_AGG with sort?

Did you find this page helpful?