Referring to relationships in calculations

Ash Forum Ash Questions / Help
,
1

Where Post has_many Comments, I’d like to define a calculation in Post that essentially produces the following query. What’s the expression that I should define?

select *, coalesce(c.title, p.title) as title from Post p
 left join Comments c
    on c.post_id = p.id

Apparently, this doesn’t work.

calculate :combined_title, :string, expr(coalesce(comments.title, title))

2

:thinking: so the post can have multiple comments, what should its combined_title be if it has multiple?

3

Sorry I meant has_one. :frowning:

4

For referring to has_one or belongs_to relationships, you can refer to them as references, i.e post.title

For coalescing, you have two options:

The || operator

calculate title_or_author_name, :string, expr(title || author.name)

Fragments

calculate title_or_author_name, :string, expr(fragment("coalesce(?, ?)", title, author.name))
5

Thank you. Is it possible to refer to the calculated field in a fragment in the same query? The following code results in an error “** (Postgrex.Error) ERROR 42703 (undefined_column) column “date” does not exist”. The SQL code that Ash generates does seem to define a calculated field but with no alias name “date”, and that’s why the query execution fails.

calculate :date, :utc_datetime, expr(instance.revised_date || original_date)

Post
|> Ash.Query.load([:date, :instance])
|> Ash.Query.filter(fragment("date_part('year', date)") == 2023")
|> Api.read()
6

You can reference it, but you can’t terence it “raw” like that. You use the reference in the expression, and interpolate that into the fragment string with ?. In that specific instance it would look like this:

calculate :date, :utc_datetime, expr(instance.revised_date || original_date)

Post
|> Ash.Query.load([:date, :instance])
|> Ash.Query.filter(fragment("date_part('year',?)", date) == 2023)
|> Api.read()
1 Like
7

Thank you, and Happy New Year!!

8

My pleasure! You as well :bowing_man:

1 Like