Custom Ecto column for the purpose of grouping

Hi,

I’ve a multilingual table, I’d like to group the search result by multiple values. For example, in the UI the result would be like this:

English - Spanish
    Hello: Hola
English - Italian
   Hello: Ciao!
English - Portuguese
  Hello: Olá
English - German
  Hello: Hallo

I can’t think of an example but there’ll be same words (in multiple language) but has a different meaning in the other language. So that’s why it needs to bey group by like this. It will be clear after you see the query which is broken actually.

  defp starts_with(query) do
    query =
      from v in Vocabulary,
        where: ilike(v.word_unaccent, fragment("unaccent(?)", ^"#{query}%")),
        order_by: v.word,
        select: %{
          id: v.id,
          word: v.word,
          descp: v.descp,
          lang: v.lang,
          to_lang: v.to_lang,
          lang_code: v.lang_code,
          to_lang_code: v.to_lang_code,
          fav_count: v.fav_count,
          group: "#{v.lang_code}_#{v.to_lang_code}"
        }

    Repo.all(query)
  end

The error:
(Ecto.Query.CompileError) “#{v.lang_code}_#{v.to_lang_code}” is not a valid query expression. Only literal binaries and strings are allowed, dynamic values need to be explicitly interpolated in queries with ^

I know the ^ is the not solution. I couldn’t find a way to create a custom column like that. group which is not a table column, it’s a column that I want to create virtual in order to make the grouping like this.

  def group_by_lang(vocabularies) do
    vocabularies
    |> Enum.group_by(fn v -> v.group end)
  end

Probably there’s a more efficient way in this functional language Elixir, but what’s it? Map, reduce? What’s the correct solution for both problems?

Thanks in advance.

:wave:

What’s the table structure? Have you tried grouping in SQL?

It’s not an aggregating query though to group by.

Well, then you can use || operator.

1 Like

Unfortunately, Ecto doesn’t support interpolation for strings out of the box. You have to use fragments for that:

fragment("? || '_' || ?", v.lang_code, v.to_lang_code)

or other postgres functions like CONCAT or CONCAT_WS.

Anyway, it is not necessary to use this at all if grouping is performed on Elixir side.
Just use complex data structures

vocabularies |> Enum.group_by(fn v -> {v.lang_code, v.to_lang_code} end)
1 Like

Perfect! That’s what I want! Didn’t know that I can group by a complex structure… Going to implement it!!

Thank you!