Select Merge with Map of Max field

Hi, I want to display some fields and only when value when there exists multiple. Currently, I have:

|> select_merge([m, md], map(m, ^[:a, :b, :c]))

And I want to have the max() for each field so it doesn’t show multiple values when they are a little different (same id).

I think I will probably need to extend the Query.API to do something like this, I was looking if there was some alternative to do this with the available functions.

I’m not entirely sure I understand what you’re asking for. Can you perhaps sketch the SQL that you’d use and then we can suggest how you’d achieve that with the query DSL?

Sorry for not being clear enough.

I have some fields (from an Ecto Schema) that I want to be in a query. Currently, this is done with select_merge (because I have another select) and with map from Query.API. I also have a group_by with the same fields, but that doesn’t matter in this case.

The query is pretty much the line bellow,

|> join(...)
|> select([m, md], map(m, ^get_decoder_fields(true))
|> select_merge([m, md], map(m, ^get_decoder_extra_fields()))

This is working for most of the scenarios, where I have two fields with an id and a description. In some cases, there is more than 1 description for the same id, and that will generate duplicated entries.

What I want to do is to just display one of the descriptions for that given id and do not display duplicated entries. I think I can manage that in the map function that has a list of fields, but instead of just put the fields, also use an aggregator function max.

If I had to implement it would be something like map_max(m, ^get_decoder_extra_fields()) and instead of generate [m.field_a, m.field_b, m.field_c] will generate [max(m.field_a), max(m.field_b), max(m.field_c)].

I think your issue is your are using map/2 instead of map literals. Does this suit your needs:

|> select([...], some fields)
|> select_merge([...], %{field: max(something), field2: max(something else)})`

This is what I meant about the SQL though. You can’t return rows AND aggregates of rows in the same query. You’ll have to compute the max based on the results yourself, or run two different SQL queries.

Oh I assumed he is grouping by the right columns. But if that’s not the case then yeah there is a problem.