Ecto.FIlter/2 trouble with Ecto query implementation

Questions / Help
,
#1

Hi!
I have a trouble with implementation this PostgreSQL query in Ecto:

select count(*) filter (where status = 'verified') as verified,
count(*) filter (where status = 'unverified') as not_verified
from translations
where locale_id = 1 and is_removed = false;

My Ecto implementation:

from(
        t in Translation,
        where: [locale_id: ^locale_id],
        where: [is_removed: ^false],
        select: %{
          verified: count(t.id) |> filter(status: "verified"),
          unverified: count(t.id) |> filter(status: "unverified")
        }
      )

But I get next compilation error:

== Compilation error in file lib/api/translations/statistics.ex ==
** (Ecto.Query.CompileError) `filter(count(t.id()), status: "verified")` is not a valid query expression.

That I did wrong?

UPD trouble reason:
Phoenix.Ecto used as dependency Elixir.Ecto 2.2.9
but Ecto 2 doesn’t support filter

#2

Try:

from(t in Translation,
  where: t.locale_id == ^locale_id,
  where: t.is_removed == ^false,
  select: %{
    verified: count(t.id) |> filter(t.status ==  "verified"),
    unverified: count(t.id) |> filter(t.status == "unverified")
  }
)
#3

Yes, i tried it earlier.
Moreover, I tried to transfer a variable from the outside, like this:

value = "verified"
    from(
    ...
            select: %{
              verified: count(t.id) |> filter(t.status == ^value)
            }
    ...
    )

And i tried write without pipe.

Problem in filter, because similar code with count(…) and without filter/2 is worked. And similar params at where/3 is worked too.

#4

Do you import Ecto.Query? Do you have Ecto version 3 (Ecto 2.2.11 doesn’t support filter)?

# file: music_db/priv/repo/playground.exs
#
# http://www.pragmaticprogrammer.com/titles/wmecto
# https://pragprog.com/titles/wmecto/source_code
# http://media.pragprog.com/titles/wmecto/code/wmecto-code.zip
#
# pg_ctl -D /usr/local/var/postgres start
# mix format ./priv/repo/playground.exs
# mix run ./priv/repo/playground.exs
#

defmodule AppInfo do
  def string() do
    Application.loaded_applications()
    |> Enum.map(&to_app_keyword/1)
    |> Enum.sort_by(&map_app_name/1)
    |> Enum.map_join(", ", &app_keyword_to_string/1)
  end

  defp to_app_keyword({app, _, vsn}),
    do: {app, vsn}

  defp app_keyword_to_string({app, vsn}),
    do: Atom.to_string(app) <> ": " <> to_string(vsn)

  defp map_app_name({app, _}),
    do: app
end

defmodule Playground do
  import Ecto.Query
  alias MusicDB.Repo
  alias MusicDB.{Album}

  def play do
    IO.puts(AppInfo.string())

    # Ecto.Adapters.SQL.to_sql(:all, Repo, query)
    from(a in Album,
      join: g in assoc(a, :genres),
      where: a.artist_id == 1,
      select: %{
        live: count(1) |> filter(g.name == "live"),
        jazz: count(1) |> filter(g.name == "jazz")
      }
    )
    |> Repo.all()
  end
end

IO.inspect(Playground.play())

$ mix run ./priv/repo/playground.exs
asn1: 5.0.7, compiler: 7.2.7, connection: 1.0.4, crypto: 4.3.3, db_connection: 2.0.3, decimal: 1.6.0, ecto: 3.0.5, ecto_sql: 3.0.3, elixir: 1.7.4, hex: 0.18.2, inets: 7.0.2, kernel: 6.1.1, logger: 1.7.4, mix: 1.7.4, music_db: 0.1.0, poison: 3.1.0, postgrex: 0.14.1, public_key: 1.6.3, ssl: 9.0.3, stdlib: 3.6, telemetry: 0.2.0

06:12:45.383 [debug] QUERY OK source="albums" db=0.9ms decode=0.6ms queue=0.8ms
SELECT 
  count(1) FILTER (WHERE g1."name" = 'live'), 
  count(1) FILTER (WHERE g1."name" = 'jazz') 
FROM "albums" AS a0 
  INNER JOIN "albums_genres" AS a2 ON a2."album_id" = a0."id" 
  INNER JOIN "genres" AS g1 ON a2."genre_id" = g1."id" 
WHERE (a0."artist_id" = 1) []
[%{jazz: 2, live: 1}]
$
4 Likes
#5

Yes, I used:

      elixir: "~> 1.4",
     {:phoenix_ecto, "~> 3.2"}

and this code work correctly:

  import Ecto.Query, warn: false

     from(
        t in Translation,
        where: [locale_id: ^locale_id],
        where: [is_removed: ^false],
        select: %{
          verified: count(t.id),
          unverified: count(t.id)
        }
      )

Hmmm, phoenix_ecto used as dependency ecto 2.2.9
Thanks! I think I understand.