count and sum Db query

I would group directly in the database:

$bookings = DB::table('bookings')
    ->select([
        'org_id',
        'payment_gateway',
        DB::raw('CAST(created_at AS DATE) as day'),
        DB::raw('COUNT(*) AS bookings_per_day'),
        DB::raw('SUM(commison) AS comission_per_day'),
    ])
    ->where('pay', '>', 0)
    ->groupBy(['org_id', 'payment_gateway', DB::raw('CAST(created_at AS DATE)')])
    ->get()
    ->toArray();

@rodrigo.pedra

thanks very much it works, i want to not repeat org_id

instade of this

0 => {#789 ▼
    +"org_id": 1
    +"payment_gateway": "LocalBook"
    +"day": "2020-03-16"
    +"bookings_per_day": 5
    +"comission_per_day": 560.0
  }
  1 => {#794 ▼
    +"org_id": 1
    +"payment_gateway": "selfBook"
    +"day": "2020-03-16"
    +"bookings_per_day": 1
    +"comission_per_day": 120.0
  }

i want this

0 => {#789 ▼
    +"org_id": 1
    +"payment_gateway": "LocalBook"
    +"day": "2020-03-16"
    +"bookings_localBook_per_day": 5
    +"comission_localbook_per_day": 560.0

    +"payment_gateway": "selfBook"
    +"bookings_selfbook_per_day": 1
    +"comission_selfbook_per_day": 120.0
  }

Well, there are tow records because the payment_gateway is different for each record.

Do you still want to group it? How do you want to handle fields with the same name?

One options is to group by org_id and date after retrieval from database in another array inside that record.

Tell me if you still want to do it

@rodrigo.pedra

Do you still want to group it? No sir

i want

-org_id, 
-date,
- nobooking by localbook,
- total commision by localbooking,
- noboking by selfBook,
- total commison by selfbook and 
*totalBook(sum both total).

yes i love if you did for me !

Ok, we can do it both in the DB query or in the collection.

I find doing in the DB easier:

$bookings = DB::table('bookings')
    ->select([
        'org_id',
        DB::raw('CAST(created_at AS DATE) as day'),
        DB::raw("SUM(CASE WHEN payment_gateway = 'LocalBook' THEN 1 ELSE 0 END) AS bookings_localbook_per_day"),
        DB::raw("SUM(CASE WHEN payment_gateway = 'LocalBook' THEN commison ELSE 0 END) AS commison_localbook_per_day"),
        DB::raw("SUM(CASE WHEN payment_gateway = 'selfBook' THEN 1 ELSE 0 END) AS bookings_selfbook_per_day"),
        DB::raw("SUM(CASE WHEN payment_gateway = 'selfBook' THEN commison ELSE 0 END) AS commison_selfbook_per_day"),
    ])
    ->where('pay', '>', 0)
    ->groupBy(['org_id', DB::raw('CAST(created_at AS DATE)')])
    ->get()
    ->toArray();

@rodrigo.pedra thanks very much !! the total commission add localbook and selfbook every date ?

any recommendation for reading Db query tutorial pdf or site

They will be available for every date on the bookings table, for example:

• In Day 1 there were 1 localbook order and no selfbook orders: there will be a record for day 1
• In Day 2 there were 1 localbook order and 1 selfbook order: there will be a record for day 2
• In Day 3 there were no localbook orders and 1 selfbook order: there will be a record for day 3
• In Day 4 there were no localbook orders and no selfbook order: there won't be a record for day 4

If you need to have all days regardless of days with no orders from both sellers you can do it in the DB query (kind of hard, but doable), but my suggestion is handling this on the PHP side.

Regarding any reading material on SQL, unfortunately I don't have any material to recommend. Laracasts has a series on SQL, very good one, but it doesn't cover this kind of subject (aggregating over values or handling missing records).

@rodrigo.pedra one more thing we select db builder org_id , how can I access the relationship and get org name from org tables

booking and org has 1 to many relationship

->select([ 'org.org_name', 
...

it is possible or is there any method ??