当前位置: 动力学知识库 > 问答 > 编程问答 >

phoenix framework - Ecto Model - subquery in select

问题描述:

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))}

Does Ecto support subqueries? If not, how can I execute the query?

网友答案:

You can use query fragments:

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))}

Although in this case the query can also be written using regular joins and group_by. Current support for subqueries in Ecto is limited.

分享给朋友:
您可能感兴趣的文章:
随机阅读: