CODEWITHSUNDEEP
phpmysqlsqllaravel
Doomish
Doomish

Reputation: 91

How can i convert an SQL query to laravel elequent

I have the following sql query working fine in a php app, but i can't figure it out in laravel elequent

The sql is basically about retrieving the number of views per day over the course of 30 days

SELECT
DATE('created_at') AS 'day',
COUNT(*) AS 'titles'
FROM `views`
where view_type ='App\\Title'
GROUP BY DATE('created_at')
order by DATE('created_at') desc
limit 30

Any help

Edit 1: DB structure

`id` bigint(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NOT NULL,
  `view_type` varchar(255) NOT NULL,
  `view_id` bigint(20) UNSIGNED NOT NULL,

Upvotes: 1

Views: 58

Answers (2)

Reza ghz
Reza ghz

Reputation: 41

you can use from DB::raw in laravel .

$results = DB::select(
DB::raw("SELECT
DATE('created_at') AS 'day',
COUNT(*) AS 'titles'
FROM 'views'
where view_type ='App\\Title'
GROUP BY DATE('created_at')
order by DATE('created_at') desc
limit 30");

Upvotes: 1

Yazan Fouad Aldbaisy
Yazan Fouad Aldbaisy

Reputation: 621

DB::table("views")
->select("date ('created_at') as `day`", "count (*) as `titles`")
->where("view_type", "=", 'App\\Title')
->limit(30)
->orderBy("created_at","desc")
->groupBy("created_at")
->get();

Upvotes: 1

Related Questions