本文介绍了ECTO模型-选择中的子查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要使用Ecto进行此SQL查询:
I need to make this SQL query with Ecto:
SELECT users.*, (select count(0) from money_transactions where from_id = users.id AND created_at > '2016-1-25 0:00:00.000000') as money_transactions_today_db FROM "users" WHERE "users"."client_token" = '123'
我尝试做这样的事情,但是不起作用:
I try to do something like this but it doesn't work:
query = from users in Like4uElixir.User,
where: users.client_token in ^tokens,
select: {users, (from money_transactions in Like4uElixir.MoneyTransaction,
where: money_transactions.from_id == users.id,
select: count(0))}
Ecto是否支持子查询?如果没有,该如何执行查询?
Does Ecto support subqueries? If not, how can I execute the query?
推荐答案
您可以使用查询片段:
query = from users in Like4uElixir.User,
where: users.client_token in ^tokens,
select: {users, (fragment("(SELECT COUNT(0) FROM money_transactions
WHERE money_transactions.from_id == ?)", users.id))}
尽管在这种情况下,也可以使用常规联接和group_by编写查询. Ecto当前对子查询的支持是有限的.
Although in this case the query can also be written using regular joins and group_by. Current support for subqueries in Ecto is limited.
这篇关于ECTO模型-选择中的子查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!