Custom Queries in Laravel

Question

How can I write queries for multiple tables e.g

"select  items.id, items.name, sum(qty) as qty  from dispatch_main 
    inner join dispatch_detail on dispatch_main.id = dispatch_detail.id
    inner join items on items.id = dispatch_detail.item_id
    left outer join customers on dispatch_detail.customer = customers.id
    where dispatch_main.entry_type in ('Purchase','Return','Confirmed') and 
    dispatch_main.to_=$location and items.for_customer in ($types)
    group by dispatch_detail.item_id
    order by  items.id
    ";

OR

"select items.id, items.name, sum(qty) as qty  from dispatch_main 
    inner join dispatch_detail on dispatch_main.id = dispatch_detail.id
    inner join items on items.id = dispatch_detail.item_id
    left outer join customers on dispatch_detail.customer = customers.id
    where dispatch_main.entry_type in ('Dispatch','Confirmed') and 
    dispatch_main.from_=$location and items.for_customer in ($types)
    group by dispatch_detail.item_id
    order by  items.id
    "

in laravel 5.4? DB::statement can run this type of queries? If i write same type of query in DB::statement('');

Why aren't you utilising models and relationships and using `Eloquent` to do these queries? — Josh Bolton, Aug 03 '17 at 12:16
relevant (no dupe): https://stackoverflow.com/questions/28481321/how-to-do-a-left-outer-join-with-laravel — online Thomas, Aug 03 '17 at 12:18
`SELECT items.id, items.name .... GROUP BY dispatch_detail.item_id` is invalid SQL on the newer MySQL servers.. please read this . https://www.psce.com/en/blog/2012/05/15/mysql-mistakes-do-you-use-group-by-correctly/ — Raymond Nijland, Aug 03 '17 at 12:21
I don't want to use Eloquent here I just want to run Raw query just like we do in simple PHP. — Aihtsham Ali, Aug 03 '17 at 12:43

score 1 · Answer 1 · answered Aug 03 '17 at 12:25

1

  1 DB::table('dispatch_main')
  2   ->innerJoin('dispatch_detail', 'dispatch_main.id', '=', 'dispatch_detail.id')
  3   ->innerJoin('items', 'dispatch_detail.item_id', '=', 'items.id')
  4   ->leftJoin('customers', 'dispatch_detail.customer', '=', 'customers.id')
  5   ->whereIn('dispatch_main.entry_type', ['Purchase','Return','Confirmed'])
  6   ->where('dispatch_main.to_', $location)
  7   ->whereIn('items.for_customer', $types)
  8   ->groupBy('dispatch_detail.item_id')
  9   ->orderBy('items.id')
 10   ->get()->toArray();
 11
 12

try this and ALWAYS, ALWAYS avoid writing RAW queries until you absolutely have too.

answered Aug 03 '17 at 12:25

Praveen Kumar

125
1
11

@ParveenKumar But this time I want to write raw queries can you please tell me how can i do that. I don't want to use Laravel functions like inner join where etc – Aihtsham Ali Aug 03 '17 at 12:28
It is also pretty simple, u just need to call a static method of the type of query you are posting. like the query for the above problem would be – Praveen Kumar Aug 03 '17 at 12:33
DB::select("select items.id, items.name, sum(qty) as qty from dispatch_main inner join dispatch_detail on dispatch_main.id = dispatch_detail.id inner join items on items.id = dispatch_detail.item_id left outer join customers on dispatch_detail.customer = customers.id where dispatch_main.entry_type in ('Purchase','Return','Confirmed') and dispatch_main.to_=? and items.for_customer in (?) group by dispatch_detail.item_id order by items.id", [$location, $types]); – Praveen Kumar Aug 03 '17 at 12:35

score 0 · Answer 2 · answered Aug 03 '17 at 12:53

You should try this:

DB::table('dispatch_main')
   ->select('items.id',DB::raw('SUM(dispatch_main.qty) as qty'))
   ->join('dispatch_detail', 'dispatch_main.id', '=', 'dispatch_detail.id')
   ->join('items', 'dispatch_detail.item_id', '=', 'items.id')
   ->join('customers', 'dispatch_detail.customer', '=', 'customers.id')
   ->whereIn('dispatch_main.entry_type', ['Purchase','Return','Confirmed'])
   ->where('dispatch_main.to_', $location)
   ->whereIn('items.for_customer', $types)
   ->groupBy('dispatch_detail.item_id')
   ->orderBy('items.id')
   ->get()
   ->toArray();

Hope this work for you !!!

Custom Queries in Laravel

2 Answers2