0
Lines:
1 - Data from filter form
2 - That with i enter a Model relationship function. I do this because I need to sort my list by column order MEDIA.
3 - The strange thing is that I need to do an INNER JOIN for the same table because if not some columns I want to pick up in QUERY does not work. What I don’t understand about Laravel. Yes, I’m the one who’s wrong, but I don’t know where.
4 - Within this JOIN I seek dealerships that have ratings and that do not have, as I choose in the filter form, so I do this IF.
5 - Then I do the AVG(RATING) to bring the average ranking that the dealership has.
6 - The function ORDER_AVG and the CLOSURE are in the MODEL.
7 - In the filter form there is the option for the user to choose the records that have a certain average (1 to 5). That’s why I’m using the HAVING. But it doesn’t work, it doesn’t filter. I don’t understand why either.
$arrData = Input::all();
$consulta = Dealer::with(['order_avg'])
->join('dealer_ratings', function($q) use ($arrData){
if(array_key_exists('filterByAvaliacao', $arrData)){
if($arrData['filterByAvaliacao'] == 1)
$q->on('dealer_ratings.id_concessionaria', '=', 'dealers.id');
else
$q->on('dealer_ratings.id_concessionaria', '<>', 'dealers.id');
}
else
$q->on('dealer_ratings.id_concessionaria', '<>', 'dealers.id');
$q->whereNotIn('id_status', [1, 4]);
})
->selectRaw('*, dealers.id, count(dealer_ratings.id) as qtd_avaliacoes, AVG(rating) as media')
->groupBy('dealers.id')
->orderBy('media', 'desc')
->whereIdCidade($arrData['filterByCidade'])
->whereIdTipo($arrData['filterByTipo'])
->closure(function($query) use ($arrData){
if($arrData['filterByMarca'] && $arrData['filterByMarca'] != 0){
$query->whereIdMarca($arrData['filterByMarca']);
}
if($arrData['palavras-chaves'] != ''){
$query->where('concessionaria', 'REGEXP', $arrData['palavras-chaves']);
}
})
->paginate(10);
MODEL
public function scopeClosure($query, \Closure $callback) {
$callback($query);
return $query;
}
# Order By Media de Stars
public function order_avg(){
return $this->hasMany('App\DealerRating', 'id_concessionaria')
->selectRaw('AVG(rating) as media')
->having('media', '=', Input::get('filterByStars'));
}
Summary:
I want to pick up a list of dealership conditioned by City and Brand. And in the same query bring the average ranking and ordered by it.
What’s the mistake you’re making?
– Wallace Maxters
The error is that HAVING does not work. No error, it is simply ignored... Rsrs
– Diego Souza
I also believe that I didn’t need those two JOINS in the same table.
– Diego Souza
There is an error in your function
order_avg
. The correct approach is to do this within a queryScope, as I taught in the other question. Then you would doModel::where(...)->orderAvg()->where()->where()->get()
– Wallace Maxters