An article explaining in detail how Laravel uses aggregate functions to calculate totals (with code examples)

This article brings you relevant knowledge about Laravel. It mainly introduces how to use conditional aggregate functions to calculate the total in Laravel. Let’s take a look at it together. I hope it will be helpful to friends who need it. help.

If there is an email subscription service, you want to display the detailed statistics page of the subscribers as shown below

Total number of subscribers	Confirmed	Unconfirmed	Cancelled	Bounced
200	150	50	10	5

For the purposes of this article, assume we have a subscribers database table containing data in the following format:

##小明adam@hotmeteor.comconfirmed小红taylor@laravel.comunconfirmed##小Jun##小花adam.wathan@gmail.combouncedWhat most people do:

name	email	status
jonathan@reinink.ca	cancelled

$total = Subscriber::count();
$confirmed = Subscriber::where('status', 'confirmed')->count();
$unconfirmed = Subscriber::where('status', 'unconfirmed')->count();
$cancelled = Subscriber::where('status', 'cancelled')->count();
$bounced = Subscriber::where('status', 'bounced')->count();

The above will definitely produce five statements, which is definitely not good. So if you try to optimize it, you will use another method to solve the problem of executing multiple statements:

$subscribers = Subscriber::all();
$total = $subscribers->count();
$confirmed = $subscribers->where('status', 'confirmed')->count();
$unconfirmed = $subscribers->where('status', 'unconfirmed')->count();
$cancelled = $subscribers->where('status', 'cancelled')->count();
$bounced = $subscribers->where('status', 'bounced')->count();

The above first obtains all subscriber data, and then performs conditional statistics on this result set, using

Collection

. Model multiple data queries return

Illuminate\Database\Eloquent\Collection. This method is only suitable for use when the amount of data is not large. If our application has thousands or millions of subscribers, the processing time will be very slow and a large amount of memory will be used. Conditional Aggregation

There is actually a very simple way to query and calculate these totals. The trick is to put the condition in an aggregate function. Here is a SQL example:

select
  count(*) as total,
  count(case when status = 'confirmed' then 1 end) as confirmed,
  count(case when status = 'unconfirmed' then 1 end) as unconfirmed,
  count(case when status = 'cancelled' then 1 end) as cancelled,
  count(case when status = 'bounced' then 1 end) as bounced
from subscribers

 total | confirmed | unconfirmed | cancelled | bounced
-------+-----------+-------------+-----------+---------
   200 |       150 |          50 |        30 |      25

————————————————
原文作者：4pmzzzzzzzzzz
转自链接：https://learnku.com/articles/74652
版权声明：著作权归作者所有。商业转载请联系作者获得授权，非商业转载请保留以上作者信息和原文链接。

Here is how to write this query in Laravel using the query builder:

$totals = DB::table('subscribers')
    ->selectRaw('count(*) as total')
    ->selectRaw("count(case when status = 'confirmed' then 1 end) as confirmed")
    ->selectRaw("count(case when status = 'unconfirmed' then 1 end) as unconfirmed")
    ->selectRaw("count(case when status = 'cancelled' then 1 end) as cancelled")
    ->selectRaw("count(case when status = 'bounced' then 1 end) as bounced")
    ->first();

<div>Total: {{ $totals->total }}</div>
<div>Confirmed: {{ $totals->confirmed }}</div>
<div>Unconfirmed: {{ $totals->unconfirmed }}</div>
<div>Cancelled: {{ $totals->cancelled }}</div>
<div>Bounced: {{ $totals->bounced }}</div>

Boolean Column (Field)

Table migration creates boolean

fields,

model definition belongs to transformation The model is not used as a code example here, you can replace it with modelIf you use boolean

When the fields are listed, it will be easier, for example, to query whether the users in the

subscribers table have different role permissions. Assume that the subscribers table has is_admin, is_treasurer, is_editor, is_manager, and fields

$totals = DB::table('subscribers')
    ->selectRaw('count(*) as total')
    ->selectRaw('count(is_admin or null) as admins')
    ->selectRaw('count(is_treasurer or null) as treasurers')
    ->selectRaw('count(is_editor or null) as editors')
    ->selectRaw('count(is_manager or null) as managers')
    ->first();

This is because the aggregate function count

ignores

null columns. Unlike false || null in PHP which returns false, in SQL (and JavaScript) it returns null. Basically, A || B returns the value A if A can be coerced to true; otherwise, B is returned. If you don’t understand this paragraph, please read my explanation below: Using laravel’s

boolean

column, the field in the actual data table is
tinyint, and the value is 0(false) and 1(true), for example, Xiao Ming’s is_admin field is
1(true),count(is_admin or null) can be regarded as expressed as (1 or null), if A is true, it returns A, and the final sql is count(is_admin). On the contrary, if the is_admin field is
0(false), and the final sql is count(null), then this column will be ignored

//PHP  返回 false
var_dump(0 || null) 

//JavaScript 返回 null
console.log(0 || null)

//SQL 返回 null
SELECT (0 or null) as result

Translation of the original text

: This article is just a translation of the general meaning, and is used as a simple record for myself

Recommended study: "laravel video tutorial

"

The above is the detailed content of An article explaining in detail how Laravel uses aggregate functions to calculate totals (with code examples). For more information, please follow other related articles on the PHP Chinese website!