Laravel Ordination with Relationship

Question

I’m looking to make a related consultation, but the ordering should be done by a field from another table that is in the relationship.

$consulta       = Dealer::whereIdMarca($codMarca)
                    ->whereIdCidade($codCidade)
                    ->get();

Relationship

public function order_avg_rating(){
    return $this->hasMany("App\DealerRating", "id_concessionaria")
                ->selectRaw('AVG(rating) as media')
                ->groupBy('id_concessionaria');
}

The order would be for establishments that had more stars and more reviews. Do not order! Also, if I do a direct JOIN on Dealer:: in the Controller, loses all relationships I did at Model for other things...

"/

I’m trying other things:

Consultation

$consulta       = Dealer::whereIdMarca($codMarca)
                    ->whereIdCidade($codCidade)
                    ->where('concessionaria', 'like', '%'.$consultaCon.'%')
                    ->with('order_avg')
                    ->get()
                    ->sortByDesc('order_avg.media, order_avg.qtd_avaliacoes')
                    ->reverse();

I’m trying to sort by the average and amount of ratings. But it doesn’t apply what I’m doing. I don’t know what happens that it doesn’t order. It automatically sorts by register order, possibly.

Model

public function order_avg(){
    return $this->belongsToMany('App\Dealer', 'dealer_ratings', 'id_concessionaria', 'id_concessionaria')
                ->withPivot('id_concessionaria')
                ->selectRaw('count(dealer_ratings.id) as qtd_avaliacoes, AVG(rating) as media')
                ->groupBy('dealers.id');
}

Answer 1

You can make a Join in your model Dealer

Dealer::join('dealer', 'tabela_2.id', '=', 'dealer.relationship_id')->orderBy('table_2.id', 'DESC');

Answer 2

Solved. I did with Eager Loading, was the only way.

# Pesquisar na Base de Dados a Consulta do Usuário
$consulta       = Dealer::with(['order_avg'])
                  ->leftJoin('dealer_ratings', function($q){
                     $q->on('dealer_ratings.id_concessionaria', '=', 'dealers.id')
                       ->whereNotIn('id_status', [1, 4]);
                        })
                        ->selectRaw('*, dealers.id, coalesce(count(dealer_ratings.id), 0) as qtd_avaliacoes, coalesce(AVG(rating), 0) as media')
                        ->groupBy('dealers.id')
                        ->orderBy('media', 'desc')
                        ->orderBy('qtd_avaliacoes', 'desc')
                        ->whereIdMarca($codMarca)
                        ->whereIdCidade($codCidade)
                        ->where('concessionaria', 'like', '%'.$consultaCon.'%')
                  ->get();

And in the Model Dealer.PHP I just made the call:

# Order By Media de Stars
public function order_avg(){
    return $this->hasMany('App\DealerRating', 'id_concessionaria');
}

Doing so does not lose the other relationships that exist.