Help with complex query (select, group and where by an undetermined number of fields)

Questions / Help
,
1

Basically I need know how to select, group and where by an undetermined number of fields, my problem is, if I select using an approach like this where I can select fields dynamically:

asset_fields = [:name, :nemo]
price_fields = [:value, :generated_price_date,  :price_date]
bonus_fields = [:value, :factor_date]
unit_fields = [:name, :iso]

from [a, u, p, b] in query,
  select: {  struct(a, ^asset_fields),
                 struct(p, ^price_fields),
                 struc(b, ^bonus_fields),
                 struct(u, ^unit_fields) }

I got an error:

** (Ecto.QueryError) it is not possible to return a map/struct subset of a fragment, you must explicitly return the desired individual fields in query:

And yes one of my joins is a subquery, and I can not do something like this sum(b.value) and fragment(“sum(? * ?)”, b.value, p.price), wich I need to calculate.

If I go with an approach like this:

  from [a, u, p, b] in query,
   select: %{ asset_name: a.name, asset_nemo: a.nemo,
                    price_value:  p.price, price_generated_price_date: p.generated_price_date,
                    bonus_value: b.value, bonus_date: b.factor_date,
                    sum_quantity: sum(b.value),
                    sum_total:    fragment("sum(? * ?)", b.value, p.price),
                    unit_name: u.name }

I can select all the fields that I want from the subquery (and not all of them) and I can do sum(b.value) and fragment(“sum(? * ?)”, b.value, p.price), but the problem is that I can not select the fields dynamically.

On the where part of my querie I’m going to do something like this:

attributes = [attribute_one: [1, 2], attribute_two: [3, 4]]

filter_by_multiple_attributes(query, attributes) do
  Enum.reduce attributes, query, fn {k, v}, query ->
    from [a, u, p, b] in query, where; field(a, ^k) in ^v
  end
end

The group by part I think that could be very similar to the where part.

1 Like
2

Yeah I’ve ended up having to build a complete field list at every iteration, it gets very verbose, it would be useful if we had a way to just pass maps through out it all and combine them trivially (preferably with the ability to rename some keys to fix duplicates and such).

But yeah, selecting the fields dynamically like that was about half of my SQL work the past week, it is painful. Can you give a complete reduced example and I can help walk through what I’ve ended up doing (which is not that simple without that method of combining subquery maps)?

2 Likes
3

Sorry for the late response, but I have no idea how should I start writing the select part of my query :confused:. This is what I got so far:

  def main_select_two_filter(query, first_filter_attribute, second_filter_attribute) do
   from [a, u, p, b] in query,
    select: %{ asset_name: a.name, asset_nemo: a.nemo,
                price_value:  p.price, price_generated_price_date: p.generated_price_date,
                bonus_value: b.value, bonus_date: b.factor_date,
                sum_quantity: sum(b.value),
                sum_total:    fragment("sum(? * ?)", b.value, p.price),
                unit_name: u.name,
                first_filter_attribute:  field(a, ^first_filter_attribute),
                second_filter_attribute: field(a, ^second_filter_attribute)}

  end

This method is working but what I receive as params is something like this: attributes = [attribute_one: [1, 2], attribute_two: [3, 4]… atributte_n: [n, n]]. So I need a way to generate those keys dynamically.

Its very difficult to me to give you a reduced example because the querie es very large.

2 Likes