在 Laravel 中如何在查询中不带参数的 groupBy Url

下面的查询包含大量连接表和 DB::raw 查询，它按预期工作。我想 groupBy 一个“landing”，它是表中存储 URL 的字段。 但我想对不带参数的 URL 进行分组。我怎样才能实现这个目标？

下面的查询尝试使用 SUBSTRING_INDEX 但没有成功。

$adverts = DB::table('adverts')                     
              ->select(DB::raw('count(*) as total'),DB::raw('(SELECT DATE_FORMAT(max(instances.date), \'%d-%m-%Y\') FROM instances WHERE instances.ad_uid = adverts.ad_uid ORDER BY id DESC limit 1) AS Last_seen_date,'
                      . '(SELECT instances.country FROM instances WHERE instances.ad_uid = adverts.ad_uid ORDER BY id DESC limit 1) AS country,'
                      . '(SELECT advertsstorage.filename FROM advertsstorage where uid_dir = adverts.ad_uid ORDER BY id ASC limit 1) AS filename,'
                      .'(SELECT IF(ext = \'jpeg\', CONCAT(fullpath, \'_1.\', ext), (CONCAT(fullpath,\'.\',ext))) as fullpath FROM advertsstorage where uid_dir = adverts.ad_uid ORDER BY id ASC limit 1)as fullpath, adverts.*'))
               ->join('domains','adverts.domain', '=' ,'domains.domain') 
               ->join('advertiser_domains','domains.id', '=' ,'advertiser_domains.domain_id')
                 ->join('advertisers','advertiser_domains.advertiser_id', '=' ,'advertisers.u_id')   
              ->where('advertisers.u_id', '=',$advertiserID)
              ->orderBy('Last_seen_date', 'DESC')
              ->groupBy(DB::raw("SUBSTRING_INDEX(adverts.landing,'?',1)"))                    
             ->get(); 

我想我已经明白了，现在 Laravel 不提供参数化对我来说是有道理的groupBy.

首先，我需要回顾一下MySql 分组依据 https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html.

您中提到的每个字段select需要与您放入的内容兼容groupBy.

你可以使用：->groupBy(DB::raw('some_alias'))

就我而言：

$statsQuery = DB::table('contacts')
                ->selectRaw("SUBSTR(DAYNAME(CONVERT_TZ(created_at, 'UTC', ?)), 1, 3) AS 'DayOfWeek', HOUR(CONVERT_TZ(created_at, 'UTC', ?)) AS 'Hour', COUNT(id) AS 'CallsScheduled'", [$reportingClientTimeZone, $reportingClientTimeZone])
                ->whereIn('adId', $adIds)
                ->whereRaw("CONVERT_TZ(created_at, 'UTC', ?) >= STR_TO_DATE(?, '%Y-%m-%d')", [$reportingClientTimeZone, $startDate]) //https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_convert-tz
                ->whereRaw("CONVERT_TZ(created_at, 'UTC', ?) < DATE_ADD(STR_TO_DATE(?, '%Y-%m-%d'), INTERVAL 1 day)", [$reportingClientTimeZone, $endDate]) //Given a certain endDate (which begins at midnight), only include results LESS THAN 1 day after that midnight
                ->whereNotNull('clarityCallTimeUtc')
                ->groupBy(DB::raw("DayOfWeek, Hour"));

See how COUNT()是我唯一的聚合函数吗？我的文章中唯一提到的其他领域select是我在中使用的（别名）groupBy.

我认为这对我有用。