How to select_merge with fragment?

Questions / Help
,
1

I have a list like this

selected_fields = [:inbox_count, :phone_number_count, :comment_count, ...] #this list contains fields of a table, and can change by the needed

group_query = 
    from(
        ps in PageStatistic,
        where: ps.id in ['1', '2']
        group_by: fragment("date(?)", ps.hour)
        select: %{
            day: fragment("date(?)", ps.hour),
        }

final_query = 
    select_fields
        |> Enum.reduce(group_query, fn field, query ->
            field_string = to_string(field)
            select_merge(query, [ps], %{^field_string => fragment("sum(?)", field(ps, ^field))})
        )

Repo.all(final_query)

but i got result like this
[
%{
    :day => ~D[2023-01-01],
    "inbox_count" => 3,
    ...
},
...
]

Is there any way make key “inbox_count” is an atom same as :day when query(i don’t want to loop the result to convert string to atom)
And is there any solution to select sum all fields that exist in select_fields when i make a query to database

P/s: i’m using Ecto v3.0

2

You’re explicitly calling field_string = to_string(field) so that’s why you’re getting a string. I don’t see any reason you need to do this so just remove that line.

I’m missing something why you do need it or if you are ever in a situation where you have a string you don’t control and want it as an atom, you can write it like this:

%{^String.to_atom(field_string) => ...)

PS, welcome to Elixir Forum!

3

thank you for response, reason why i need convert field to field_string is that if the query when i bind atom to field need select, will like this (you can notice new_customer_count and other field need sum)

test_query: ecto.Query<from p0 in PancakeV2.PageStatistic,
where: p0.page_id in [1] and p0.hour >= ^~N[2023-11-23 17:00:00] and p0.hour < ^~N[2024-02-21 16:00:00],
group_by: [fragment(“DATE(?)”, p0.hour)],
order_by: [desc: fragment(“DATE(?)”, p0.hour)],
select: merge(merge(merge(merge(merge(merge(merge(merge(merge(merge(merge(%{day_time: fragment(“DATE(?)”, p0.hour)}, %{^:new_customer_count => fragment(“SUM(?)”, p0.new_customer_count)}), %{^:phone_number_count => fragment(“SUM(?)”, p0.phone_number_count)}), %{^:uniq_phone_number_count => fragment(“SUM(?)”, p0.uniq_phone_number_count)}), %{^:customer_comment_count => fragment(“SUM(?)”, p0.customer_comment_count)}), %{^:customer_inbox_count => fragment(“SUM(?)”, p0.customer_inbox_count)}), %{^:page_comment_count => fragment(“SUM(?)”, p0.page_comment_count)}), %{^:page_inbox_count => fragment(“SUM(?)”, p0.page_inbox_count)}), %{^:new_inbox_count => fragment(“SUM(?)”, p0.new_inbox_count)}), %{^:inbox_interactive_count => fragment(“SUM(?)”, p0.inbox_interactive_count)}), %{^:today_uniq_website_referral => fragment(“SUM(?)”, p0.today_uniq_website_referral)}), %{^:today_website_guest_referral => fragment(“SUM(?)”, p0.today_website_guest_referral)})>

and then i got this error:

** (DBConnection.EncodeError) Postgrex expected a binary, got :new_customer_count. Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.

  1. “256469571178082”, “100747749482464” ↩︎

4

That sounds more like the value of the field you are trying to select into is the wrong type, although you are returning a bare map so that shouldn’t matter :thinking:

I am, however, realizing that I mistakingly read Ecto 3.0 as 3.10, so that likely has something to do with it because I can’t for the life of me reproduce this in 3.11. Very sorry about that :grimacing: :disappointed:

1 Like
5

Upgrade to the latest version of ecto and use ^field instead of ^field_string.
I created the issue on Github Unable to set keys dynamically using select_merge · Issue #4326 · elixir-ecto/ecto · GitHub
And it was resolved in this PR Don't send dynamic map keys to the database by greg-rychlewski · Pull Request #4327 · elixir-ecto/ecto · GitHub

4 Likes