Viewed   370 times

i'm trying to group my data by month and year.

$data ->select(DB::raw('count(id) as `data`'),DB::raw('YEAR(created_at) year, MONTH(created_at) month'))

The output is :

"data": 19215,
"year": 2016,
"month": 10

if i group only by month, i don't know from which year belong this month, my expected output is :

"clicks": 19215,
"month": 11-2016,
"clicks": 11215,
"month": 12-2016,

i want to do it in sql, not in php.



You can try as:

->select(DB::raw('count(id) as `data`'), DB::raw("DATE_FORMAT(created_at, '%m-%Y') new_date"),  DB::raw('YEAR(created_at) year, MONTH(created_at) month'))
Friday, October 21, 2022

You'll need to overwrite Eloquent model's getCastType() method in your model class:

protected function getCastType($key) {
  if ($key == 'value' && !empty($this->type)) {
    return $this->type;
  } else {
    return parent::getCastType($key);

You'll also need to add value to $this->casts so that Eloquent recognizes that field as castable. You can put the default cast there that will be used if you didn't set type.


The above works perfectly when reading data from the database. When writing data, you have to make sure that type is set before value. There are 2 options:

  1. Always pass an array of attributes where type key comes before value key - at the moment model's fill() method respects the order of keys when processing data, but it's not future-proof.

  2. Explicitely set type attribute before setting other attributes. It can be easily done with the following code:

    $model == (new Model(['type' => $data['type']))->fill($data)->save();
Tuesday, December 20, 2022

Try to call Collection groupBy instead. Just put groupBy after get(). It should work.

    ->whereRaw($where['rawQuery'], isset($where['bindParams']) ? $where['bindParams'] : array())
Friday, September 2, 2022

If I understand correctly, you'd like to fetch a list of Content objects together with their children Content objects, correct?

Easiest way to do that is to create a parent-child relation in your Eloquent Content model and then use that to load parents with children:

class Content extends Model {
  public function children() {
    //this defines a relation one-to-many using parent_id field as the foreign key
    return $this->hasMany(Content::class, 'parent_id'); 

  public function parent() {
    return $this->belongsTo(Content::class, 'parent_id'); 

  public function section() {
    return $this->belongsTo(Section::class);

Then, if you want to list Content objects their Section together with with their children and their sections, you can fetch the data like that:

$contents = Content::with(['children', 'section', 'children.section'])->whereNull('parent_id')->get();

$contents will contain a collection of all Content objects that have no parent. Each of the objects will have a $content->children attribute that holds a collection of all children Content objects. All children objects will also hold a reference to their parent in $childContent->parent. Both parents and children will have their corresponding section in ->section attribute.

If you wanted to display some Content hierarchy now in your Blade template, you can pass the $contents variable to the view and do the following:

@foreach($contents as $content)
  @if($content->children->count() > 0)
      @foreach($content->children as $childContent)

I noticed that you have a sequence field in your model. I assue that you want content to be sorted by that field. In this case you'll need modify the way you fetch the data:

$contents = Content::with(['children' => function($builder) {
  $builder->orderBy('sequence', 'desc');
}, 'section', 'children.section'])->whereNull('parent_id')->get();
Wednesday, September 28, 2022

I just used:

use DB;

and in my query I used


Sunday, October 30, 2022
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :