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