Eloquent: How to groupby ('year') a column with date format, ignoring month and day

I am building an app with Laravel. I am using Eloquent and MySql Database. In my MySQL database I have a table EVENTS like this:

id EVENT YEAR 1 event1 2000-05-27 2 event2 2018-01-01 3 event3 2000-02-12 4 event4 2000-06-03

This example is just for simplicity, obviously my database has hundreds of events ocurring at any time.

The data in the column year has the 'date' format

I need to get from this MySQL table a resume of events ocurred per year. In the example would be

year number of events 2000 3 2018 1

I did this:

$years = Event:selectRaw("DATE_FORMAT(YEAR, '%Y') AS year, COUNT(*) AS total") ->groupBy('year') ->get();

and I get this:

year number of events 2000 1 2018 1 2000 1 2000 1

Because each date in the column is different (In the example table: 2000-02-12 !== 2000-06-03 !== 2000-05-27).

I need to group together all events happening in each year together without taking into account mont and day.

The question is:

How do I group by colum 'year' but taking into account just the year even if the events ocurred in different days and months?

lostdreamer_nl

7 years ago

Level 53

It should be working as you described (just tested it on my users table). You could try (as a test) to see what happens when you use the DB facade instead of a model directly:

// $years = \DB::table('events')->selectRaw("DATE_FORMAT(YEAR, '%Y') AS year, COUNT(*) AS total") ->groupBy('year') ->get();

// output:
Collection {#1001 ▼
  #items: array:2 [▼
    0 => {#947 ▼
      +"year": "2017"
      +"total": 3
    }
    1 => {#948 ▼
      +"year": "2018"
      +"total": 11
    }
  ]
}

fatenfalfoul

7 years ago

Level 1

I used that query to do some thing same:

$visits = Visit::select(DB::raw('year(created_at) as year'), DB::raw("count(*) as total") ) ->orderBy("created_at") ->groupBy(DB::raw('year(created_at)'))->get();

RafaelMunoznl

7 years ago

Level 8

@lostdreamer_nl

I tested that also, but I get the 552 Items in Database, each item separated per year (one per year) without grouping.

Please or to participate in this conversation.