Home / Questions / 456547
Accepted
jmrufo
Asked: 2022-06-01 22:32:42 +0800 CST 2022-06-01 22:32:42 +0800 CST

selectRaw query with multiple fields

  • 772

Good morning companions.

Currently, I perform the following queries to calculate the total number of potential clients ($clientsusersp) and loyal clients (clientsusersf). My query is the following: Can you unify these two queries into one to optimize the code?

$clientsusersp = DB::table('users')
                ->selectRaw('users.*, clients.user_id, count(*) as cuentap')
                ->join('clients', 'users.id', '=', 'clients.user_id')
                ->where('loyalcustomer', '=', 0)
                ->groupBy('users.id')
                ->get();

$clientsusersf = DB::table('users')
            ->selectRaw('users.*, clients.user_id, count(*) as cuentaf')
            ->join('clients', 'users.id', '=', 'clients.user_id')
            ->where('loyalcustomer', '=', 1)
            ->groupBy('users.id')
            ->get();

Thanks to everyone beforehand.

All the best.

php

1 Answers

  1. Best Answer

    You can use a case statement to count the potential customers and the loyal customers in two separate fields in a single query, which in this case will not have a clause where, since we want them all and we are going to count them separately:

    $clientsuserspf = DB::table('users')
                ->selectRaw('users.*, clients.user_id, count(case when loyalcustomer = 0 then 1 end) as cuentap, count(case when loyalcustomer = 1 then 1 end) as cuentaf')
                ->join('clients', 'users.id', '=', 'clients.user_id')
                ->groupBy('users.id')
                ->get();

    I'm not sure how laravel translates the fields you include to mysql, but I feel like there are too many fields in the query, or too few fields in the group by. If you don't need all the users information, I suggest you change it to:

    $clientsuserspf = DB::table('users')
                ->selectRaw('users.id, count(case when loyalcustomer = 0 then 1 end) as cuentap, count(case when loyalcustomer = 1 then 1 end) as cuentaf')
                ->join('clients', 'users.id', '=', 'clients.user_id')
                ->groupBy('users.id')
                ->get();

    Which has a direct translation to valid and clear SQL.

    • 0