Query to return minimum amount of "different" values

Asked

Viewed 49 times

2

I have a table called clientes and this table has a column status which may have +/- 3 types (novo, cancelado, pendente...)

need to display a quantity of information separated by status, and instead of doing it with 3 queries, sendo a Where for each status, I wonder if it is possible in a single query to return the 3 status with a minimum amount of each one of them:

Current example:

Cliente::where('status', 'novo')->take(20)->get();
Cliente::where('status', 'cancelado')->take(20)->get();
Cliente::where('status', 'pendente')->take(20)->get();

it is possible, in a single query, to return 20 results for each different status?

mysql laravel laravel-eloquent

  • There is no way you can build what you want the way you are proposing, understand that status is a one-to-one relationship and resides in another table. However you can create correctly, note that what you want to do is look at from the point of view of STATUS and not from the customer, so you will be required to use the STATUS model and not client to assemble the query.

  • actually, the status is on the same table, it’s just a table

  • Some answer got your problem???

2 answers

2

You can use a "group by", for that would look something like this:

Cliente::groupBy('status')->select('status', DB::raw('count(*) as total'))->get();

Remember to use the DB namespace

use Illuminate\Support\Facades\DB;

  • Thank you for the reply, but there would come more than 20, they would all come

0

Yes, with the command Union, even creating the query Builder without compiling the , example:

$a = Cliente::where('status', 'novo')->take(20);
$b = Cliente::where('status', 'cancelado')->take(20);
$c = Cliente::where('status', 'pendente')->take(20);

and after these 3 lines that are not yet executed at the bank make the link as follows:

$result = $a->union($b)->union($c)->get();

and it is worth remembering that this method of ORM has more or less this :

SELECT * FROM cliente WHERE status = 'novo' limit 20
UNION
SELECT * FROM cliente WHERE status = 'cancelado' limit 20
UNION
SELECT * FROM cliente WHERE status = 'pendente' limit 20

that is, a single is sent and return the data that is required under the conditions.

Browser other questions tagged mysql laravel laravel-eloquent

You are not signed in. Login or sign up in order to post.