Warm tip: This article is reproduced from serverfault.com, please click

eloquent-在laravel使用join和按月计数嵌套选择

(eloquent - Count nested select using join and by month at laravel)

发布于 2020-11-28 09:28:34

嗨,如何将这个查询转换为laravel ORM或Eloquent?并且oy字段现在返回月份数,我想获取月份的名称。

SELECT c.name as company, i.title_uz as internet, EXTRACT(MONTH FROM  ci.created_at) as oy, COUNT(*) AS miqdor
FROM count_internets AS ci
JOIN companies as c ON  ci.company_id = c.id
JOIN internets as i ON  ci.u_id = i.id
GROUP BY company, internet, oy
Questioner
Hayrulla Melibayev
Viewed
11
OMR 2020-11-28 17:41:23

对于月份名称,可以使用MySQL MONTHNAME()函数

  DB::table("count_internets")->join("companies","companies.id",'=','count_internets.company_id')
           ->join("internets",'internets.id','=','count_internets..u_id')
           ->select(["count_internets.name as company",'internets.title_uz as internet',DB::raw("MONTHNAME(companies.created_at) as oy")
               ,DB::raw("COUNT(*) AS miqdor")])
           ->groupBy(['company','internet','oy'])->get();