Laravel Eloquent get results grouped by days

by

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')
      ->select(DB::raw('DATE(created_at) as date'), DB::raw('count(*) as views'))
      ->groupBy('date')
      ->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', '>=', \Carbon\Carbon::now->subMonth())
                            ->groupBy('date')
                            ->orderBy('date', 'DESC')
                            ->get(array(
                                DB::raw('Date(created_at) as date'),
                                DB::raw('COUNT(*) as "views"')
                            ));

More Related Contents:

  1. How to bind parameters to a raw DB query in Laravel that’s used on a model?
  2. belongsToMany relationship in Laravel across multiple databases
  3. How Can I Set the Default Value of a Timestamp Column to the Current Timestamp with Laravel Migrations?
  4. Change name of Laravel’s created_at and updated_at
  5. How to create a database-driven multi-level navigation menu using Laravel
  6. Laravel one to many relation with a one to one relation
  7. How can create Database using url or other method [duplicate]
  8. Problame with files on SQL
  9. Im trying to create some temporary tables in mysql using php
  10. How can I prevent SQL injection in PHP?
  11. SQL injection that gets around mysql_real_escape_string()
  12. How to include a PHP variable inside a MySQL statement
  13. How to apply bindValue method in LIMIT clause?
  14. Achieve hierarchy, Parent/Child Relationship in an effective and easy way
  15. Laravel blank white screen
  16. How to search for slash (\) in MySQL? and why escaping (\) not required for where (=) but for Like is required?
  17. Laravel 4: how to “order by” using Eloquent ORM [duplicate]
  18. MYSQL Select from tables based on multiple rows
  19. Laravel Migration table already exists, but I want to add new not the older
  20. How to execute raw queries with Laravel 5.1?
  21. Laravel preg_match(): No ending delimiter ‘/’ found
  22. How do you do multiple SQL statements in one mysql_query?
  23. How can I install mcrypt under PHP7? Laravel needs it
  24. Fatal error: Call to a member function query() on null
  25. Laravel Eager Loading – Load only specific columns
  26. Mysql InnoDB engine in Laravel
  27. Laravel catch Eloquent “Unique” field error
  28. Do I have to use mysql_real_escape_string if I bind parameters?
  29. PDO and MySQL ‘between’
  30. How Can I Remove “public/index.php” in the Laravel Url generated? [duplicate]

Leave a Comment