CODEWITHSUNDEEP
laravellaravel-5
RK4002
RK4002

Reputation: 97

Laravel - selectRaw count - IF NULL Set to zero

I'm working on filtering my products, but i only seem to get products where there is actually views logged.

How can i set "IF NULL = 0" or even do it smarter?

Here's my code: 

if ($request->sort == 'views') {
   $query->leftJoin('analytics', 'foreign_id', '=', 'ads.id');
   $query->selectRaw('ads.*, IFNULL(count(analytics.id),0) as views');
   $query->orderBy('views', "$sort_order");
}

My DB looks like this: id | ip | foreign_id | foreign_type | updated_at | created_at

Right now i have one row in my DB, and the product with that id i the only one showing.

Thank your so much in advance.

Upvotes: 1

Views: 3290

Answers (3)

RK4002
RK4002

Reputation: 97

The solution was to group, then it worked perfectly. 

if ($request->sort == 'views') {

    $query->leftJoin('analytics', 'foreign_id', '=', 'ads.id');
    $query->selectRaw('ads.*, count(analytics.id) as views');
    $query->orderBy('views', "$sort_order");
    $query->groupBy('ads.id');

}

Upvotes: 3

Cedric
Cedric

Reputation: 5303

if ($request->sort == 'views') {
   $query->leftJoin('analytics', 'foreign_id', '=', 'ads.id');
   $query->whereNotNull('analytics');
   $query->selectRaw('ads.*, analytics.id as views');
   $query->orderBy('views', "$sort_order");
}

I leave this one as a suggestion at the moment - I am pretty sure it will not work as you wish, but it just gives some directions

Upvotes: 0

DevK
DevK

Reputation: 9952

I think COALESCE (assuming MySQL) is what you're looking for. Try this:

if ($request->sort == 'views') {
   $query->leftJoin('analytics', 'foreign_id', '=', 'ads.id');
   $query->selectRaw('ads.*, COALESCE(count(analytics.id),0) as views');
   $query->orderBy('views', "$sort_order");
}

Upvotes: 0

Related Questions