Laravel From Raw DB to Eloquent

Question

i m trying to change my laravel raw query to eloquent, since now i have got some basics of eloquent and i have tried making blogs in laravel. But some complexity has remain the same.

First: this is my Raw SQL:

select group_concat(DISTINCT q.sku SEPARATOR ", ") as sku, `sales_flat_order`.`status`, `sales_flat_order`.`increment_id`, `sales_flat_order`.`shipping_description`, `sales_flat_order`.`subtotal`, `sales_flat_order`.`customer_email`, `d`.`country_id`, `d`.`region`, `d`.`city`, `d`.`postcode`, group_concat(DISTINCT q.name SEPARATOR ", ") as name, concat(sales_flat_order.created_at) AS created_at from `sales_flat_order` left join `sales_flat_order_item` as `q` on `sales_flat_order`.`entity_id` = `q`.`order_id` left join `sales_flat_order_address` as `d` on `d`.`parent_id` = `sales_flat_order`.`entity_id` group by `sales_flat_order`.`increment_id` order by `sales_flat_order`.`increment_id` asc

My Raw query in Laravel:

SalesFlatOrder::leftJoin('sales_flat_order_item as q','sales_flat_order.entity_id', '=','q.order_id')
                        ->leftJoin('sales_flat_order_address as d', 'd.parent_id', '=', 'sales_flat_order.entity_id')
                        ->select((array(DB::Raw('group_concat(DISTINCT q.sku SEPARATOR ", ") as sku'),'sales_flat_order.status','sales_flat_order.increment_id', 'sales_flat_order.shipping_description','sales_flat_order.subtotal','sales_flat_order.customer_email','d.country_id', 'd.region', 'd.city','d.postcode',DB::raw('group_concat(DISTINCT q.name SEPARATOR ", ") as name'),DB::raw('concat(sales_flat_order.created_at) AS created_at'))))
                        ->groupBy('sales_flat_order.increment_id')
                        ->orderBy('sales_flat_order.increment_id')
                        ->paginate(10);

Now i am trying to change this whole raw query into eloquent. I have already made models. So following is my eloquent query which is in my controller.

public function detailed(){
$sales = SalesFlatOrder::with('address')->groupBy('increment_id')->orderBy('increment_id')->paginate(10);

return View::make('detailed')->with('sales', $sales);
}

My Problem: 1: I do have group concat (Distinct q.sku) . So how to do that with eloquent. Because sometimes you need to do group by Date. or group by Orders. So how to show Distinct data so sku column. So how to convert this full fledge raw query into Eloqeunt where we have group_concat, Distinct for so many columns of DB

Answer 1

First create a Custom Collection:

$customCollection = new \Illuminate\Database\Eloquent\Collection;

Then, you can do this like that:

$user =new User;
$user->setRawAttributes((array) $userRawData);
$customCollection->add($user);

Regards!

Answer 2

You can move from RAW to Eloquent elegant queries with models relationship, eloquent DB tables naming convention (without specifying table name in model), route data binding crom DB and scopes.

But still, sometimes you will have to help youself with some RAW for more complicated operations.