Get data & Ignore null value when using group by in laravel

user_15383969 0
Viewed 564

I want to group by the data based on content_related_group. But there's a condition where content_related_group is null, and I don't want it to group the data with null value.

I've tried like this:

$products = DB::table('product')
        ->select('*', DB::raw('count(*) as total'))
        ->orderBy('created_at', 'desc')
        ->groupBy('content_related_group')  
        ->paginate(9);

It's working, but it grouping the null data into one. I don't want it, what I want is to get all data from database and only group the the data with same content_related_groupinto one.

Example data:

id   |   content_related_group
1    |   content_1
2    |   content_1
3    |   null
4    |   null

Result (the null data keep separated from each other & the same content_related_group is grouped:

id   |  content_related_group
1    | content_1
2    | null
3    | null

It is possible? Thanks

eloquent laravel
1 Answers

try this if you want to remove the null rows:

$products = DB::table('product')
        ->select('*', DB::raw('count(*) as total'))
        ->whereNotNull('content_related_group')  
        ->orderBy('created_at', 'desc')
        ->groupBy('content_related_group')  
        ->paginate(9);

try this if you want to keep null value grouped to one row

$x_products   =   DB::table('product')->whereNull('content_related_group');

$products = DB::table('product')
        ->whereNotNull('content_related_group')
        ->union($x_products)
        ->select('*', DB::raw('count(*) as total'))
        ->orderBy('created_at', 'desc')
        ->groupBy('content_related_group')  
        ->paginate(9);
user_118827290
Related