php - Doctrine get entities distributed over hours -

my problem: need count(orders) several shops distributed on hours. ofcourse order contain createdat field, , bedirectional manytoone relation shop.

can done through dql or should use plain sql, or should orders , sort them in loop?

what best practices? expected result in excel below.

my current solution:

repository:

public function findsortedtoday() {     $orders = $this->createquerybuilder('o')         ->select('hour(o.createdat) ocreated')         ->addselect('sum( od.dishprice ) odsum')         ->addselect('(os.name) oshop')         ->innerjoin('o.shop', 'os')         ->innerjoin('o.dishes', 'od')         ->groupby('ocreated')         ->addgroupby('os')         ->getquery()         ->getresult();     return $orders; } 

controller:

    ...     $ordersbyhourarray = $this->getdoctrine()->getrepository("terminalbundle:order")->findsortedtoday();             foreach ($ordersbyhourarray $data)     {         $resbyhourarray[$data["oshop"]][$data["ocreated"]] = $data["odsum"]*1;     }     foreach ($resbyhourarray $shop => $data)     {         ($h = 0; $h <= 23; $h++){             if (!array_key_exists($h, $data)) $resbyhourarray[$shop][$h] = 0;         }         ksort($resbyhourarray[$shop]);     }     ... 

if want number of orders split horizontally, i.e., row-wise.

select s.id shop_id,        concat(case when date_format(now(),'%h') > 12         concat(date_format(now(),'%h') - 12,' pm')         else concat(date_format(now(),'%h'),' am') end        ,' - ',case when date_format(now(),'%h') + 1 > 12         concat(date_format(now(),'%h') + 1 - 12,' pm')         else concat(date_format(now(),'%h') + 1,' am') end) window,        count(*) orders orders o left join shops s on s.id = o.shop_id group s.id, window; 

if want number of orders split vertically, i.e., column-wise.

select s.shop_id,        count(distinct case when hour(now()) = 0 o.order_id else 0 end) 'midnight - 1 am',        count(distinct case when hour(now()) = 1 o.order_id else 0 end) '1 - 2 am',               count(distinct case when hour(now()) = 2 o.order_id else 0 end) '2 - 3 am',               count(distinct case when hour(now()) = 3 o.order_id else 0 end) '3 - 4 am',               count(distinct case when hour(now()) = 4 o.order_id else 0 end) '4 - 5 am',               count(distinct case when hour(now()) = 5 o.order_id else 0 end) '5 - 6 am',               count(distinct case when hour(now()) = 6 o.order_id else 0 end) '6 - 7 am',               count(distinct case when hour(now()) = 7 o.order_id else 0 end) '7 - 8 am',               count(distinct case when hour(now()) = 8 o.order_id else 0 end) '8 - 9 am',               count(distinct case when hour(now()) = 9 o.order_id else 0 end) '9 - 10 am',               count(distinct case when hour(now()) = 10 o.order_id else 0 end) '10 - 11 am',               count(distinct case when hour(now()) = 11 o.order_id else 0 end) '11 - noon',               count(distinct case when hour(now()) = 12 o.order_id else 0 end) 'noon - 1 pm',               count(distinct case when hour(now()) = 13 o.order_id else 0 end) '1 - 2 pm',               count(distinct case when hour(now()) = 14 o.order_id else 0 end) '2 - 3 pm',               count(distinct case when hour(now()) = 15 o.order_id else 0 end) '3 - 4 pm',               count(distinct case when hour(now()) = 16 o.order_id else 0 end) '4 - 5 pm',               count(distinct case when hour(now()) = 17 o.order_id else 0 end) '5 - 6 pm',               count(distinct case when hour(now()) = 18 o.order_id else 0 end) '6 - 7 pm',               count(distinct case when hour(now()) = 19 o.order_id else 0 end) '7 - 8 pm',               count(distinct case when hour(now()) = 20 o.order_id else 0 end) '8 - 9 pm',               count(distinct case when hour(now()) = 21 o.order_id else 0 end) '9 - 10 pm',               count(distinct case when hour(now()) = 22 o.order_id else 0 end) '10 - 11 pm',               count(distinct case when hour(now()) = 23 o.order_id else 0 end) '11 pm - midnight'       shops s inner join orders o on o.shop_id = s.shop_id;