Laravel Eloquent get results grouped by days

Question

I currently have a table of page views that records one row for each time a visitor accesses a page  recording the user s ip id and the id of the page itself  I should add that the created at column is of type  timestamp  so it includes the hours minutes seconds  When I try groupBy queries  it does not group same days together because of the seconds difference  created at         page id       user id                                          10-11-2013            3            1 10-12 2013            5            5 10-13 2013            5            2 10-13 2013            3            4                                        I d like to get results based on views day  so I can get something like    date          views                       10-11-2013       15 10-12 2013       45                       I m thinking I ll need to dig into DB  raw   queries to achieve this  but any insight would help greatly  thanks Edit  Added clarification of created at format

User · Answer

Like most database problems  they should be solved by using the database   Storing the data you want to group by and using indexes you can achieve an efficient and clear method to solve this problem   Create the migration       table- gt tinyInteger  activity year  - gt unsigned  - gt index         table- gt smallInteger  activity day of year  - gt unsigned  - gt index      Update the Model   lt  php  namespace App Models     use DB    use Carbon Carbon    use Illuminate Database Eloquent Model     class PageView extends Model       public function scopePerDay  query          query- gt groupBy  activity year          query- gt groupBy  activity day of year          return  query          public function setUpdatedAt  value              date   Carbon  now          this- gt activity year    int  date- gt format  y         this- gt activity day of year    date- gt dayOfYear       return parent  setUpdatedAt  value        Usage      viewsPerDay   PageView  perDay  - gt get

User · Answer

You can filter the results based on formatted date using mysql  See here for Mysql Mariadb help  and use something like this in laravel-5 4   Model  selectRaw  COUNT    views  DATE FORMAT created at    Y  m  e   date       - gt groupBy  date       - gt get

User · Answer

I know this is an OLD Question and there are multiple answers  How ever according to the docs and my experience on laravel below is the good  Eloquent way  of handling things  In your model  add a mutator Getter like this  public function getCreatedAtTimeAttribute        return  this- gt created at- gt toDateString        Another way is to cast the columns in your model  populate the  cast array   casts         created at    gt   string      The catch here is that you won t be able to use the Carbon on this model again since Eloquent will always cast the column into string  Hope it helps

User · Answer

I built a laravel package for making statistics   https   github com Ifnot statistics  It is based on eloquent  carbon and indicators so it is really easy to use  It may be usefull for extracting date grouped indicators    statistics   Statistics  of MyModel  query       statistics- gt date  validated at      statistics- gt interval Interval   DAILY  Carbon  createFromFormat  Y-m-d    2016-01-01    Carbon  now      statistics- gt indicator  total   function  row        return  row- gt counter        data    statistics- gt make     echo  data  2016-01-01  - gt total

User · Answer

Using Laravel 4 2 without Carbon  Here s how I grab the recent ten days and count each row with same day created at timestamp    q   Spins  orderBy  created at    desc   - gt groupBy DB  raw  DATE FORMAT created at    Y- m- d      - gt take 10  - gt get array        DB  raw  Date created at  as date          DB  raw  COUNT    as  views            foreach   q as  day       echo  day- gt date    Views       day- gt views   lt br gt         Hope this helps

User · Answer

Warning  untested code    dailyData   DB  table  page views       - gt select  created at   DB  raw  count    as views        - gt groupBy  created at       - gt get

User · Answer

Here is how I do it  A short example  but made my query much more manageable   visitorTraffic   PageView  where  created at     gt      Carbon Carbon  now- gt subMonth                            - gt groupBy DB  raw  Date created at                             - gt orderBy  created at    DESC  - gt get

User · Answer

You could also solve this problem in following way    totalView    View  select DB  raw  Date read at  as date    DB  raw  count    as Views            - gt groupBy DB  raw  Date read at             - gt orderBy DB  raw  Date read at             - gt get

User · Answer

I had same problem  I m currently using Laravel 5 3   I use DATE FORMAT     - gt groupBy DB  raw  DATE FORMAT created at    Y- m- d        Hopefully this will help you

User · Answer

To group data according to DATE instead of DATETIME  you can use CAST function    visitorTraffic   PageView  select  id    title    created at   - gt get   - gt groupBy DB  raw  CAST created at AS DATE

User · Answer

this way work properly and I used it in many projects  for example I get data of views the last 30 days   viewsData   DB  table  page views       - gt where  page id    page- gt id      - gt whereDate  created at     gt     now  - gt subDays 30       - gt select DB  raw  DATE created at  as data    DB  raw  count    as views        - gt groupBy  date       - gt get     If you want to get the number of views based on different IPs  you can use the DISTINCT like below    viewsData   DB  table  page views       - gt where  page id    page- gt id      - gt whereDate  created at     gt     now  - gt subDays 30       - gt select DB  raw  DATE created at  as data    DB  raw  count DISTINCT user ip  as visitors        - gt groupBy  date       - gt get     You can easily customize it by manipulating the columns name

User · Answer

You can use Carbon  integrated in Laravel       Carbon use Carbon Carbon      visitorTraffic   PageView  select  id    title    created at       - gt get       - gt groupBy function  date            return Carbon  parse  date- gt created at - gt format  Y       grouping by years           return Carbon  parse  date- gt created at - gt format  m       grouping by months

User · Answer

I believe I have found a solution to this  the key is the DATE   function in mysql  which converts a DateTime into just Date   DB  table  page views         - gt select DB  raw  DATE created at  as date    DB  raw  count    as views          - gt groupBy  date         - gt get      However  this is not really an Laravel Eloquent solution  since this is a raw query The following is what I came up with in Eloquent-ish syntax  The first where clause uses carbon dates to compare    visitorTraffic   PageView  where  created at     gt      Carbon Carbon  now- gt subMonth                                - gt groupBy  date                               - gt orderBy  date    DESC                               - gt get array                                  DB  raw  Date created at  as date                                    DB  raw  COUNT    as  views

User · Answer

in mysql you can add MONTH keyword having the timestamp as a parameter in laravel you can do it like this  Payement  groupBy DB  raw  MONTH created at    - gt get

User · Answer

PageView  select  id   title   DB  raw  DATE created at  as date              - gt get             - gt groupBy  date

[php] Laravel Eloquent get results grouped by days

Create the migration

Update the Model

Usage

Examples related to php

Examples related to mysql

Examples related to sql

Examples related to laravel

Examples related to laravel-4