在Laravel中如何在查询中无参数地对Url进行分组 [英] In Laravel how to groupBy Url without parameter in the query

问题描述

下面的查询有很多连接表和DB :: raw查询，这是按预期工作的。我想通过一个登陆这个表格中的字段来存储URL。
但是我想在没有参数的情况下通过URL进行分组。我怎样才能做到这一点？

下面的查询试图使用SUBSTRING_INDEX，但没有奏效。

 。'（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限制1）AS文件名，'
'（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 fu *'））
  - > 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 Group By 。

您在选择中提到的每个字段都需要与您放入 groupBy


您可以使用：
- > groupBy（DB :: raw（'some_alias'））


$ b

在我的情况中：



  $ 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/zh-CN/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]）//给定一个endDate（从午夜开始），只包含结果LESS Than午夜后1天
  - > whereNotNull（'clarityCallTimeUtc'）
  - > groupBy（DB :: raw（DayOfWeek，Hour））; 
  

看看 COUNT（）是我唯一的聚合函数吗？我的 select 是我在 groupBy 中使用的（别名）。

我认为这对我有用。

The below query is with lots of join table and DB::raw query, Which is working as expected. I would like to groupBy a "landing" which is a field in the table has URL stored. But I would like to groupBy URL without parameter. How can I achieve this?

Query below trying to use SUBSTRING_INDEX but didn't worked.

$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(); 

解决方案

I think I figured it out, and now it makes sense to me that Laravel doesn't offer parameterization with groupBy.

First, I needed a refresher on MySql Group By.

Every field mentioned in your select needs to be compatible with what you put in your groupBy.

And you can use: ->groupBy(DB::raw('some_alias'))

In my case:

$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() is my only aggregate function? And the only other fields mentioned in my select are the ones I use (the aliases of) in the groupBy.

I think this is working for me.

这篇关于在Laravel中如何在查询中无参数地对Url进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！