Dynamic key (field mapping - not field it self) in select

Hello,

I’m looking for a way to make a mapping key dynamic. Something like this:

    field = "field"

    qq =
      from [q] in Reservation,
        select: %{^field => q.id}

    # "SELECT $1, r0.\"id\" FROM \"reservations\" AS r0"
    # ["field"]

Unfortunately, as you can see Ecto inserts $1 instead of making a mapping.

Here is an example of expected result

    qq =
      from [q] in Reservation,
        select: %{"field" => q.id}

    # "SELECT r0.\"id\" FROM \"reservations\" AS r0"

You should use field/2 to dynamically reference fields.
https://hexdocs.pm/ecto/Ecto.Query.API.html#field/2

The Ecto field function allows you to specify column dynamically, but this not what I need.

I need dynamic field mapping. In my case it will be like:

qq =
      from [q] in Reservation,
        select: %{
           "id_1" => q.id,
           "id_2" => q.id,
           "id_3" => q.id,
        }

And I want it like:

      from [q] in Reservation,
        select: %{
           "id_#{n}" => q.id
        }

The example might look strange to you, but imagine a complex sum(case ... then ... else) instead of just q.id

Where does this n come from?

This is just predictable index.

Here is like my real peace of code look like:

    field = "sum_num_people_all_shop_id_1"
    field2 = "sum_num_people_all_shop_id_2"
    field3 = "sum_num_people_all_shop_id_3"
    from [q] in query,
      select_merge: %{
        ^field =>
          case(q.shop_id == ^shop_id1, q.all_num_people, 0.0)
          |> sum()
          |> coalesce(0),
        ^field2 =>
          case(q.shop_id == ^shop_id2, q.all_num_people, 0.0)
          |> sum()
          |> coalesce(0),
        ^field3 =>
          case(q.shop_id == ^shop_id3, q.all_num_people, 0.0)
          |> sum()
          |> coalesce(0)
      }

but I need to generate sum_num_people_all_shop_id_1. As amount of shop_ids is variable and is a user input.

So my idea is to iterate over shop_ids and put required amount of cases

We are sending it to the database but does it change the result? If not, is the current behaviour a blocker? Ecto can optimize this and not send it in the future though.

2 Likes

My bad, I have to clarify.

So, we are using Redshift (which is old PG forked by Amazon). With some tweaks to Elixir PG library it works.

In this example I passed only one case with dynamic name, so there is only one $1. From the error message I see it wants 5 == 6 and seems like $1 confuses it.

Also it looks like a bug in Redshift I started a thread on forum with them, but as this is a critical ticket for me right now I’m looking for anything to help with. As the alternative for now I see is to define a bunch of functions…, hope this won’t happen.

This is the error I got:
error:  Assert
  code:      1000
  context:   handled_cols == m_num_colflds - handled_cols:5 m_num_colflds:6
  query:     3555961
  location:  tbl_trans.cpp:865
  process:   padbmaster [pid=21613]


Here is my query:
SELECT $1, coalesce(sum(CASE WHEN r0."shop_id" = $2 THEN r0."all_num_people" ELSE 0.0::float END), 0),
coalesce(sum(CASE WHEN r0."shop_id" = $3 THEN r0."all_num_people" ELSE 0.0::float END), 0), 
coalesce(sum(CASE WHEN r0."shop_id" = $4 THEN r0."all_num_people" ELSE 0.0::float END), 0), 
r0."shop_id", r0."status"
FROM "reservations" AS r0
WHERE (r0."local_start_at_with_day_offset"::date BETWEEN $5::date AND $6::date)
AND (r0."shop_id" IN ($7,$8,$9))
GROUP BY r0."status", r0."shop_id"


Here is params
["sum_num_people_all_shop_id_1", "5184018070f78ebe1600004b", "5d43f5ebc9714e49f60000e5",
 "5184018070f78ebe16000048", ~D[2000-09-17], ~D[2019-10-16], "5184018070f78ebe1600004b",
 "5d43f5ebc9714e49f60000e5", "5184018070f78ebe16000048"]

As a temporary step, maybe you are able of returning them as a list or a tuple, and then build the map on the result afterwards? Or that wouldn’t solve it either?

I was thinking about it. One of my ideas was to use array_agg but it is not available for Redshift and available kind-of replacement is not OK.

Probably I’m missing something but select_merge wants me to pass a map. And it is a long chain of select_merges (we are using GraphQL)

Also I’m trying to solve issue with Redshift support. They asked me for types passed for prepared statement (“Do you have the entire PREPARE statement that you are using? Any chance it is passing in UNKNOWN as one of the data types?”).

i.e. having this all is OK:

PREPARE name1(char, char, char) AS
SELECT $1,
       coalesce(sum(CASE WHEN r0."shop_id" = $2 THEN r0."all_num_people" ELSE 0.0::float END), 0),
       r0."provider",
       r0."shop_id"
FROM "reservations" AS r0
WHERE (r0."shop_id" IN ($3))
  AND (NOT (r0."provider" IS NULL))
GROUP BY r0."provider", r0."shop_id";


EXECUTE name1('sum_num_people_all_shop_id_1', '5184018070f78ebe1600004b', '5184018070f78ebe1600004b');

and this doesn’t:

PREPARE name3(unknown, char, char) AS
SELECT $1,
       coalesce(sum(CASE WHEN r0."shop_id" = $2 THEN r0."all_num_people" ELSE 0.0::float END), 0),
       r0."provider",
       r0."shop_id"
FROM "reservations" AS r0
WHERE (r0."shop_id" IN ($3))
  AND (NOT (r0."provider" IS NULL))
GROUP BY r0."provider", r0."shop_id";


EXECUTE name3('sum_num_people_all_shop_id_1', '5184018070f78ebe1600004b', '5184018070f78ebe1600004b');

Could you please point me where can I find out what types (char, char, char) ecto/postgrex is sending?
I digged postgres for a while, but no success, I think it is somewhere in postgrex/lib/postgrex/protocol.ex

Finally found a way to make this working with Redshift - type(^shop_id, :string). This was pretty unobvious.

select_merge: %{type(^shop_id, :string) => case(q.shop_id == ^shop_id, q.all_num_people, 0.0)
|> sum()
|> coalesce(0)}

plus Enum.reduce by shop_ids

1 Like