CY-Left

PHP开发语言未分类

laravel DB eloquent 查询

laravel eloquent

一 分组求和

$list = User::where('renzheng', '<>', 3)
    ->with(['hasManyComplaint' => function ($query) {
        $query->select('user_id', 'reason', DB::raw('sum(times) as allTimes'))
        ->groupBy('reason');
    }])
    ->whereHas('hasManyComplaint', function ($query) {})
    ->select('fp_user.id', 'name', 'renzheng')
    ->paginate(10);

foreach ($list as $key => $val) {
    $list[$key]->complaint_reason = array_column($val->hasManyComplaint->toArray(), 'allTimes', 'reason');
    unset($list[$key]->hasManyComplaint);
}

小结

get() 反馈的是一个 collection 对象数组, 可以被 foreach 循环遍历

first() 反馈的是单个 collection 对象, 不需要 foreach

paginate() 反馈的是一个包装好的 collection 对象数组, 所谓包装好的, 是添加了一些诸如 #total: 1, #lastPage: 1 等的分页属性

  1. 这里展示一个 collection 对象(删除了一些内容)
User {
  #table: "fp_user"
  #primaryKey: "id"
  #attributes: array:3 [
    "id" => "47225"
    "name" => "相旭"
    "renzheng" => "1"
  ]
  #original: array:3 [
    "id" => "47225"
    "name" => "相旭"
    "renzheng" => "1"
  ]
  #relations: array:1 [
    "hasManyComplaint" => Collection {
      #items: array:3 [
        0 => DemandComplaint {#1364 
          #table: "demand_complaint"
          +timestamps: false
          #connection: null
          #primaryKey: "id"
          +incrementing: true
          #attributes: array:3 [
            "user_id" => "47225"
            "reason" => "0"
            "allTimes" => "1"
          ]
          #original: array:3 [
            "user_id" => "47225"
            "reason" => "0"
            "allTimes" => "1"
          ]
        }
    }
  ]
}
User::find(1)
->select('id', 'name', 'renzhegn')
->with('hasManyComplaint', function($query){
    $query->select('user_id', 'reason', 'allTimes');
})
->first()

可以看到, 一个 collection 对象有 table, primaryKey 的属性, 另外还有我们关心的被查询出来的数据 attributes [id, name, renzheng], 调用这些数据只需要类似 $res->name 即可

另外在 collection 对象中, 关联查询出来子 collection 对象需要一般是使用驼峰式命名的方法名, 调用的时候需要使用 ->hasManyComplaint

  1. 展示 toArray() 序列化之后的数据
[
  "id" => "47225"
  "name" => "相旭"
  "renzheng" => "1"
  "has_many_complaint" => array:3 [
    0 => array:3 [
      "user_id" => "47225"
      "reason" => "0"
      "allTimes" => "1"
    ]
    1 => array:3 [
      "user_id" => "47225"
      "reason" => "4"
      "allTimes" => "4"
    ]
    2 => array:3 [
      "user_id" => "47225"
      "reason" => "5"
      "allTimes" => "13"
    ]
  ]
]

通过 toArray() 序列化之后, 关联查询出来的 collection
对象的键会被强制转义成下划线式, 通过 ['has_many_complaint'] 方式调用

本文虽拙,却也系作者劳动,转载还请保留本文链接: http://cyleft.com/?p=928