Making use of Aliases in ecto

How can i make use of aliases in ecto.

 def get_query(bits) do
    from(
      t in Alpha,
       select: [t.id, fragment("bit_count(? # ?)", t.db_bits, ^bits)],
       where:
         fragment("bit_count(? # ?)", t.db_bits, ^bits) <
           100
     ) |> Repo.all
   end

i am using fragment in both select as well as in where clause. need to make it run at one one time, as bit_count is the stored procedure

Maybe like this as you’re already using fragments? I’m not aware of any way to handle that as part of the ecto query itself.

def get_query(bits) do
    from(
      t in Alpha,
       select: [t.id, fragment("bit_count(? # ?) AS bitcount", t.db_bits, ^bits)],
       where:  fragment("bitcount") < 100
     ) |> Repo.all
   end

Tried this. getting error.
(Postgrex.Error) ERROR 42703 (undefined_column): column "bitcount" does not exist

So it seems you can’t use aliases for that in sql at all.

May be use the subquery can solve the problem.

main_query = 
    from(
      i in Alpha,
      select: %{id: i.id, bitcount: fragment("bit_count(? # ?)", i.db_bits, ^bits)}
    )
  from(t in subquery(main_query),
    select: [t.id, t.bitcount],
    where:
      t.bitcount < 100

SELECT List:

Apparently following the SQL standard, computed fields haven’t been calculated by the time WHERE and HAVING are applied - they are present however for ORDER BY and GROUP BY (and even a plain alias seems to count as a computed field).

2 Likes

Thanks for clarifying. Can we have any alternative better than using the subqueries, that i have mentioned in the above comment, in these types of situatons

What constitutes “better” in your particular circumstance? The subquery is a viable solution and bitcount has to be calculated before the rows can be filtered anyway.

According to the rules of SQL you would have the restate the expression in the WHERE clause to make it work. At that point it becomes an issue with the optimizer whether it detects that it already evaluated the expression for the WHERE clause so that it can reuse the result for the bitcount value in the SELECT - I don’t know whether PostgreSQL has that level of sophistication.

That could be approached this way:

  @field_id "cast(? as varchar(255)) || ?"

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

    suffix = "M"
    query =
      from(a in Artist,
        select: [a.id, fragment(@field_id, a.id, ^suffix)],
        where: fragment(@field_id, a.id, ^suffix) == "2M"
      )

    # Ecto.Adapters.SQL.to_sql(:all, Repo, query)

    query
    |> Repo.all()
  end

At this point, benchmark both approaches and see which one comes out on top.

2 Likes