从原始DB到雄辩的Laravel [英] Laravel From Raw DB to Eloquent

问题描述

我试图改变我的Laravel原始查询，以雄辩，因为现在我有一些雄辩的基础，我已经尝试在laravel做博客。
但是一些复杂性仍然是一样的。

首先：这是我的原始SQL：

  select group_concat DISTINCT q.sku SEPARATOR，）作为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```````````````````````````````````````````````````````````````````````````` sales_flat_order.created_at）从`sales_flat_order`的AS created_at将`sales_flat_order_item`作为`````````````````````````````````````````````````` parent_id` =`sales_flat_order`.`entity_id` group by`sales_flat_order`.`increment_id` order by`sales_flat_order`.`increment_id` asc 
  

我的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，）作为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.city'，'d.postcode'，DB :: raw（'group_concat（DISTINCT q.name SEPARATOR，）as name'），DB :: raw（'concat（sales_flat_order.created_at）AS （$ sales_flat_order.increment_id）
  - > groupBy（'sales_flat_order.increment_id' 
  

现在我试图将这个整个原始查询更改为雄辩。我已经做了模型。所以以下是我的控制器中的雄辩查询。

  public function detailed（）{
 $ sales = SalesFlatOrder ::与（ '地址'） - > GROUPBY（ 'increment_id'） - > ORDERBY（ 'increment_id'） - >分页（10）; 
 
 return View :: make（'detailed'） - > with（'sales'，$ sales）; 
} 
  

我的问题：1：
我有组合（特殊的q.sku）。那么如何用雄辩的方式呢呢因为有时你需要按日期进行分组。或按订单分组。所以如何显示出不同的数据，所以sku列。那么如何将这个完整的原始查询转换成Eloqeunt，在那里我们有group_concat，对于这么多列的DB来说是不同的。

解决方案

首先创建一个自定义集合：

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

然后，您可以这样做：

  $ user = new User; 
 $ user-> setRawAttributes（（array）$ userRawData）; 
 $ customCollection-> add（$ user）; 
  

请问！

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

解决方案

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!

这篇关于从原始DB到雄辩的Laravel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！