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
- /**
- * Sales Deb Export.
- *
- * @param
- * @return. csv
- *
- */
- Private function _salesdebexport ()
- {
- $objects = db::table (' orders ')
- ->join (' order_products ',' orders.id ',' = ',' order_products.order_id ')
- ->join (' products ',' order_products.product_id ',' = ', 'products.id ')
- ->leftjoin (' categories ',' products.category_id ',' = ',' categories.id ')
- ->select (
- ' orders.external_id ',
- ' Orders.address_billing_name ',
- ' Categories.customs_code ',
- ' Orders.external_sale_date ',
- ' orders.invoice_external_id ',
- ' Orders.payment_method ',
- ' Orders.address_shipping_country ',
- Db::raw ('
- SUM (order_products.amount_base) as Amount_base,
- SUM (Order_products.amount_tax) as Amount_tax,
- SUM (order_products.amount_total) as Amount_total
- ‘)
- )
- ->groupby (' orders.external_id ',' Categories.customs_code ')
- ->wherein (' orders.object_state_id ', Array (2,3))
- ->where ('orders.created_at ', ' like ', Input::get (' year '). '-'. Input::get (' month '). '-% ')
- ->get ();
- if (empty ($objects))
- {
- Notification::error ("aucune donnée disponible.");
- return redirect::to (URL::p revious ());
- }else{
- $headers = Array (
- ' content-type ' = ' text/csv ',
- ' content-disposition ' = ' attachment; filename= ' Sales_deb_export_ '. Input::get (' month ').' _‘. Input::get (' year '). CSV "',
- );
- $output = RTrim (Implode (', ', Array_keys ((array) $objects [0])). "\ n";
- foreach ($objects as $object)
- {
- $output. = RTrim (Implode (', ', (array) $object)). "\ n";
- }
- return Response::make ($output, $headers);
- }
Laravel Database Aggregation +join lookup statements.