Laravel Database Aggregation +join lookup statements.

Aggregate

The Query Builder also provides a variety of aggregation methods, such as,, count max min , avg and sum .

Using Aggregation Methods #

$users=Db::Table(' Users ')->Count();$price=Db::Table(' Orders ')->Max(' Price ');$price=Db::Table(' Orders ')->Min(' Price ');$price=Db::Table ( ' orders ' ->avg  ( ' price ' ;  $total = db::table (" Users ' ->sum ( ' votes '           

But for complex situations (such as the result after a join, the result is not a objet). You cannot use the above method directly

Workaround:

Db::raw (")

Raw expressions#

There are times when you need to use raw expression in a query statement, such expressions become strings inserted into the query, so be careful not to establish any SQL injection attack points. To create raw expression, you can use the DB::raw method:

Using Raw expression#

$users=Db::Table(' Users ')->Select(Db::Raw(' Count (*) as User_count, status ') ->where(' status ', ' <> ', 1) ->  GroupBy(' status ') ->get();       

Actual code:

[PHP]View PlainCopy

2. /**
4. * Sales Deb Export.
6. *
8. * @param
10. * @return. csv
12. *
14. */
16. Private function _salesdebexport ()
18. {
20. $objects = db::table (' orders ')
22. ->join (' order_products ',' orders.id ',' = ',' order_products.order_id ')
24. ->join (' products ',' order_products.product_id ',' = ', 'products.id ')
26. ->leftjoin (' categories ',' products.category_id ',' = ',' categories.id ')
30. ->select (
32. ' orders.external_id ',
34. ' Orders.address_billing_name ',
36. ' Categories.customs_code ',
38. ' Orders.external_sale_date ',
40. ' orders.invoice_external_id ',
42. ' Orders.payment_method ',
44. ' Orders.address_shipping_country ',
46. Db::raw ('
48. SUM (order_products.amount_base) as Amount_base,
50. SUM (Order_products.amount_tax) as Amount_tax,
52. SUM (order_products.amount_total) as Amount_total
54. ‘)
56. )
58. ->groupby (' orders.external_id ',' Categories.customs_code ')
60. ->wherein (' orders.object_state_id ', Array (2,3))
62. ->where ('orders.created_at ', ' like ', Input::get (' year '). '-'. Input::get (' month '). '-% ')
64. ->get ();
68. if (empty ($objects))
70. {
72. Notification::error ("aucune donnée disponible.");
74. return redirect::to (URL::p revious ());
76. }else{
78. $headers = Array (
80. ' content-type ' = ' text/csv ',
82. ' content-disposition ' = ' attachment; filename= ' Sales_deb_export_ '. Input::get (' month ').' _‘. Input::get (' year '). CSV "',
84. );
88. $output = RTrim (Implode (', ', Array_keys ((array) $objects [0])).  "\ n";
90. foreach ($objects as $object)
92. {
94. $output. = RTrim (Implode (', ', (array) $object)).  "\ n";
96. }
98. return Response::make ($output, $headers);
102. }

Laravel Database Aggregation +join lookup statements.