Laravel根据groupBy获取结果计数 [英] Laravel get count of results based on groupBy

问题描述

以下是我的数据库的结构

The following is the structure of my database

id  name  country  province   status  updated_at    
1   A     US       TX         2       [timestamp]
2   B     UK       LON        1       [timestamp]
3   C     US       TX         2       [timestamp]
4   D     US       WA         2       [timestamp]
5   E     US       WA         3       [timestamp]
6   F     US       WA         2       [timestamp]
7   G     US       TX         1       [timestamp]

表格中的信息(搜索条件)

Information from the form (search criteria)

在此示例中为
• 国家(美国)
• 开始日期，结束日期

我需要获得以下计数和信息.

I need to get the following count and information.

• 按省份分组{
  • $ ca = count(状态'1')
  • $ cb = count(状态'2')
  • $ cc = count(状态'3')}
  • Group by province {
    • $ca = count(status '1')
    • $cb = count(status '2')
    • $cc = count(status '3') }

    预期结果:美国"搜索条件:-

    Expected result:: 'US' search criteria:-

    Province TX
        Status '1': 1
        Status '2': 2
        Status '3': 0
    
    Province WA
        Status '1': 0
        Status '2': 2
        Status '3': 1
    

    以下是我尝试执行的操作但失败了

    The following is what i trying to do but failed

    $users = DB::table('users')
    ->where('country', $b)
    ->select('province', DB::raw('count(*) as total'))
    ->groupBy('province')
    ->whereBetween('updated_at', [
            \Carbon\Carbon::createFromDate($d, $e)->startOfMonth(),
            \Carbon\Carbon::createFromDate($f, $g)->endOfMonth()
        ])->orderBy('created_at','desc')->get();
    

    推荐答案

    注意:我的原始MySQL有点生锈，对于任何错误都深表歉意.

    问题1:您按错误的列分组.

    如果您想知道每个国家每个省的每个州的总数，则需要在GROUP BY子句中指定所有这些项目.实际上，您的COUNT将确定唯一省份的数量.

    If you want to know the totals of each individual status for each individual province for each individual country, you need to specify all of those items in your GROUP BY clause. As it is, your COUNT will determine the number of unique provinces.

    GROUP BY country, province, status
    

    问题2:没有条目的状态将不会显示.

    除非您要为每种状态加入外部表，否则将不会获得不存在状态的任何计数.根据您提供的数据，原始结果将更像这样:

    Unless you are joining to an outside table for each status, you will not get any counts for non-existent statuses. Given your provided data, the raw results would look more like this:

    Country  Province  Status  Total
    US       TX        1       1
    US       TX        2       2
    US       WA        2       2
    US       WA        3       1
    

    ​​这是一个如何完成零计数的示例用于不存在的条目.使用它完全取决于您的数据和数据库结构.

    Here's an example of how to accomplish zero counts for non-existent entries. Using this will totally depend on your data and DB structure.

    可能的警告:ORDER BY

    Potential caveats: ORDER BY

    通过created_at列进行排序似乎毫无意义.如果您希望获得预期的结果，则需要根据该信息进行具体订购:

    Ordering by the created_at column seems pointless. If you'd like your expected results, you'll want to order specifically by that information:

    ORDER BY country, province, status
    

    解决方案

    我不会包含用于检索任何零结果状态的联接.但是，这是一个未经测试的如何转换为查询生成器的示例:

    I won't include the join to retrieve any zero-result statuses. But here's an untested example of how to translate to Query Builder:

    $results = DB::table('users')
        ->select(['country', 'province', 'status', DB::raw('COUNT(*) AS total')])
        ->where('country', '=', $b)
        ->whereBetween('updated_at', [
            \Carbon\Carbon::createFromDate($d, $e)->startOfMonth(),
            \Carbon\Carbon::createFromDate($f, $g)->endOfMonth()
        ])
        ->groupBy('country', 'province', 'status')
        ->orderBy('country') // defaults to ASC
        ->orderBy('province')
        ->orderBy('status')
        ->get();
    

    替代解决方案

    半复杂查询的一种替代方法可能是简单地查询所需的信息集(属于$b国家的条目，并且在提供的updated_at次之间)，然后自己使用PHP处理.

    An alternative to a semi-complicated query might be to simply query the set of information you need (entries belonging to $b country, and in between the provided updated_at times), and then process it yourself in PHP.

    Laravel的 Collection 类具有内置的便捷方法超过数据.这些甚至是原始的foreach()都可以为您提供所需的信息.无论哪种情况最适合您的情况.

    Laravel's Collection class has handy methods built-in for going over data. These, or even a raw foreach() could get you the information you need. Whatever works best for your situation.

    这篇关于Laravel根据groupBy获取结果计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！