CODEWITHSUNDEEP
laravellaravel-5
RK4002
RK4002

Reputation: 97

Laravel 5.2 orderBy relation withCount results in SQL error because of failed attempt to get column instead of count

The goal is to sort my campaigns by views in my filtration, which is why i have an analytics table with relations to my campaigns

My campaign model (The DB name is "ads"):

public function views() {
    return $this->hasMany('App\Analytic', 'foreign_id', 'id')->where('foreign_type', '=', 'campaign');
}

The controller of my filtration:

$query = Ad::withCount('views')->with('tags');
$query->where("is_active", "=", 1);
$query->where("status", "=", 1);
$query->orderBy('views_count', 'DESC');
$campaigns = $query->get();

Now the reason for not writing it without the $query-> part, is because the query has lots of if statements depending on filtration settings.

The error im getting:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'views_count' in 'order clause' (SQL: select count(*) as aggregate from `ads` where `is_active` = 1 and `status` = 1 and `from_year` >= 7 and `to_year` <= 88 and `price` >= 1000 and `price` <= 64000 order by `views_count` desc)

The error is it tries to fetch a column, but i can't figuere out why. If i try to access $campaign->views_count in my blade template, it shows the count just fine.

Thank you for your time, i hope someone can tell me what I'm doing wrong here.

Upvotes: 0

Views: 555

Answers (1)

N69S
N69S

Reputation: 17206

The error you're getting is a result of a count() method not a get(). something like this

$query = Ad::withCount('views')->with('tags');
$query->where("is_active", "=", 1);
$query->where("status", "=", 1);
$query->orderBy('views_count', 'DESC');
$campaignCount = $query->count();

wich replaces the complex select part with:

select count(*) as aggregate

if you need the count() and the get(), do it like this:

$query = Ad::withCount('views')->with('tags');
$query->where("is_active", "=", 1);
$query->where("status", "=", 1);
$campaignCount = $query->count();

$query->orderBy('views_count', 'DESC');
$campaigns = $query->get();

Upvotes: 1

Related Questions