Question 176927 in Laravel Question posted in
The official Laravel Documentation can be found here.

Joining two rows with the same value and then using Count in Laravel

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'])
Tags:

2

Answers


  1. 0

    u can just write a raw query for that

    Sales::join("sales as refunded", "sales.transaction_id", "refunded.transaction_refund_id")
->groupBy("sales.transaction_id")
->selectRaw("sales.transaction_id, count(refunded.transaction_refund_id) as count")
->get()
    Login or Signup to reply.
  2. 0

    Based on your requirements, you can use a self-join to solve this problem.

    Try this:

    $salesCount = DB::table('sales as s1')
    ->join('sales as s2', 's1.transaction_id', '=', 's2.transaction_refund_id')
    ->count();

    This query joins the sales table to itself on transaction_id = transaction_refund_id and counts the resulting rows.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search