I think something like this should work (not tested):
from entry in Entry,
preload: [:tags],
join: tag in assoc(entry, :tags),
group_by: entry.id,
having: fragment("? <@ array_agg(?)", ^tags, tag.name)
michalmuskala
9
holandes22
Your solution worked perfectly as well @michalmuskala but with a much more cleaner query, thank you very much!
2
kickinespresso
@OvermindDL1 I’m trying to do something similar with a many to many relationship but instead of filtering, I want to calculate the virtual field based on other relationships.
I have many to many relationships of User-Transactions-Merchant, where the user has many Merchants through Transactions and the Merchant has many customers through Transactions. Each Transaction has a value, and the sum of transactions associated between a specific user and a specific merchant is the balance for that users relationship with that merchant.
I use this query to calculate the balance to for user based on the merchant
def customer_balance(user_id: user_id, merchant_id: merchant_id) do
q = from t in Transaction,
select: fragment("SUM(CASE WHEN ? = 'credit' THEN (?) ELSE - (?) END)", t.type, t.amount, t.amount),
where: t.user_id == ^user_id and t.merchant_id == ^merchant_id
balance = Repo.one(q) || 0
do_balance(balance, "asset")
|> Money.new(:USD)
end
I’ve been trying to do something like this but I haven’t had success.
squery =
from u in User,
join: m in assoc(merchant, :merchants),
from sq in subquery(squery),
select: %{ sq | balance: fragment("SUM(CASE WHEN ? = 'credit' THEN (?) ELSE - (?) END)", t.type, t.amount, t.amount)},
where: sq.user_id == ^user_id and sq.merchant_id == ^merchant_id
I think I may may need to preload the transactions prior to the outer query. When I look at just raw SQL nested subqueries, it looks right but I think I’m over thinking it.
In the end I should have a list of users for a specific merchant with the users balance calculated for that merchant. Am I going the right direction here?
0
OvermindDL1
What issues or errors? Have you tried to formulate your SQL in raw SQL before converting it to Ecto?
Looks right on a cursory look so far yep?
0
kickinespresso
@OvermindDL1 - @mbuhot answered my question on StackOverflow a few weeks ago. (Thanks @mbuhot !!)
Here is the response:
Move the fragment into a macro to keep the code clear:
defmacro balance_amount(transaction) do
quote do
fragment("CASE WHEN ? = 'credit' THEN (?) ELSE - (?) END",
unquote(transaction).type, unquote(transaction).amount, unquote(transaction).amount)
end
end
Create a subquery with %{user_id, merchant_id, balance}
def user_merchant_balance do
from t in Transaction,
select: %{user_id: t.user_id, merchant_id: t.merchant_id, balance: sum(balance_amount(t))},
group_by: [t.user_id, t.merchant_id]
end
Join to the subquery from the main query, use the map update syntax %{|} to populate the virtual field:
def merchant_customers(merchant_id) do
from u in User,
join: b in subquery(user_merchant_balance()), on: u.id == b.user_id,
where: b.merchant_id == ^merchant_id,
select: %{u | balance: b.balance}
end
This is the final code I ended up using:
@doc """
Retrieves all customers with balances for a merchant
"""
@spec find_merchant_customers_with_balance(struct) :: [%MyApp.User{}]
def find_merchant_customers_with_balance(%{"merchant_id" => id}) do
q = merchant_customers(id)
Repo.all(q)
end
@doc """
User balance calcuation macro
"""
defmacro balance_amount(transaction) do
quote do
fragment("CASE WHEN ? = 'debit' THEN (?) ELSE - (?) END",
unquote(transaction).type, unquote(transaction).amount, unquote(transaction).amount)
end
end
@doc """
Query for retrieving user balance for merchant
"""
@spec user_merchant_balance :: %Ecto.Query{}
def user_merchant_balance do
from t in Transaction,
select: %{user_id: t.user_id, merchant_id: t.merchant_id, balance: sum(balance_amount(t))},
group_by: [t.user_id, t.merchant_id]
end
@doc """
Query for retrieving all the merchants for a customer with abalance
"""
@spec merchant_customers(number) :: %Ecto.Query{}
def merchant_customers(merchant_id) do
from u in User,
join: b in subquery(user_merchant_balance()), on: u.id == b.user_id,
where: b.merchant_id == ^merchant_id,
select: %{u | balance: b.balance}
end
I still need to figure out if I can get the balance attribute in the returned query returned as a Money.Ecto struct instead of a raw number. Any ideas on how I might be able to do that?
On the customer_balance function I transform it afterward into Money, but that doesn’t return as a User
def customer_balance(user_id: user_id, merchant_id: merchant_id) do
q = from t in Transaction,
select: fragment("SUM(CASE WHEN ? = 'credit' THEN (?) ELSE - (?) END)", t.type, t.amount, t.amount),
where: t.user_id == ^user_id and t.merchant_id == ^merchant_id
balance = Repo.one(q) || 0
do_balance(balance, "asset")
|> Money.new(:USD)
end
2
mbuhot
Looks like you can populate a virtual field with a custom type in ecto 2.2:
Edit: Not quite, can’t use type and sum together, but another layer of subquery does the trick:
@spec user_merchant_balance :: %Ecto.Query{}
def user_merchant_balance do
from record in subquery(
from t in Transaction,
select: %{user_id: t.user_id, merchant_id: t.merchant_id, balance: sum(balance_amount(t)},
group_by: [t.user_id, t.merchant_id])
select: %{user_id: record.user_id, merchant_id: record.merchant_id, balance: type(record.balance, Money.Ecto.Type)}
end
0
onnimonni
This conversation was a top result in Google with “Ecto many to many relationship filtering”.
I tested performance a bit today in many to many case where I have apartments table, amenities table and then join table between them.
using array_agg() was 10x slower than doing:
SELECT a.*
FROM apartments a
JOIN apartment_amenities aa ON a.id = aa.apartment_id
JOIN amenities am ON aa.amenity_id = am.id
WHERE a.city = 'Chicago'
AND am.name IN ('fridge', 'gym')
GROUP BY a.id
HAVING COUNT(DISTINCT am.name) = 2;
Or:
SELECT a.*
FROM apartments a
JOIN apartment_amenities aa1 ON a.id = aa1.apartment_id
JOIN amenities am1 ON aa1.amenity_id = am1.id
JOIN apartment_amenities aa2 ON a.id = aa2.apartment_id
JOIN amenities am2 ON aa2.amenity_id = am2.id
WHERE a.city = 'Chicago'
AND am1.name = 'fridge'
AND am2.name = 'gym';
I don’t yet know how to turn this into Ecto but I would highly advice anyone from using a virtual array_agg aggregation because Postgres can’t use any indexes properly with that.
Hope this is useful for others too 
0