MySQL Query where column is specific value or any value

Question

Not specifically a Laravel question, but I am using Laravel Query Builder. I have a MySQL database column that contains any one of a long list of statuses. When filtering the list I simply pass the status or statuses as an array to the query...

$statusArray = ['active','inProgress'];

DB::table('mytable')->whereIn('status', $statusArray)->get();

However when not filtering I want all statuses returned so I would wish for something like...

 $statusArray = [*];

Is there a simple way to handle this or do I need to dynamically build the whereIn clause for the query?

Answer 1

When you want to see perticular status add them in $statusArray

$statusArray = ['active','inProgress'];

And when you want to get all statuses declare $statusArray as empty array

$statusArray = array();

Then add condition in eloquent like below

DB::table('mytable')->when(!empty($statusArray), function($q, $statusArray) {
   return $q->whereIn('status', $statusArray);
})->get();

Answer 2

Yo can use the 'when' conditional clause. This will help you add additional conditions to your query when a condition is satisfied and skip the conditions when it is not. (in your case, when the filter is to be applied

See here : https://laravel.com/docs/5.7/queries#conditional-clauses