I have a table called sales with the following columns.
transaction_id
transaction_refund_id
price
id | transaction_id | transaction_refund_id | price |
---|---|---|---|
1 | tr_001 | 0 | 5 |
2 | tr_002 | tr_001 | 5 |
3 | tr_003 | tr_003 | 5 |
I want to query the sales table and join the records that match values (transaction_id = transaction_refund_id) then ->count()
I have tried whereColumn but discovered that it’s for comparing rows in the same record.
So the value for the count, in this case, is 1 as I am only able to join tr_001
Also tried:
Sales::all()->groupBy(['transaction_id', 'transaction_refund_id'])
2
Answers
u can just write a raw query for that
Based on your requirements, you can use a self-join to solve this problem.
Try this:
This query joins the
sales
table to itself ontransaction_id = transaction_refund_id
and counts the resulting rows.