Sorting Laravel with Relationship

Question

Sorting Laravel with Relationship

Navigation

#1 by (2 votes)
#2 by (1 votes)

1

I want to do a related query, but the ordering must 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');
}

Sorting would be for establishments that have more stars and more ratings . Do not order! Also, if I make a direct JOIN on Dealer :: on the Controller, lose all relationships I did in Model for other things ...

"/

I'm trying other things:

Inquiry

$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 average and number of ratings. But it does not apply what I'm doing. I do not know what happens that does not order. It automatically sorts through the registration 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');
}

laravel

asked by anonymous 31.03.2016 / 21:24

2 answers

2

You can join in your model Dealer

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

31.03.2016 / 22:23

Zoom to the selected image thumbnail in the Input file atom text-editor - Clicking on the go to code function

score 1 · Accepted Answer

Solved. I did with Eager Loading , it 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 Model Dealer.PHP I just dialed:

# 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.