CODEWITHSUNDEEP
phplaravel
Furya
Furya

Reputation: 463

Laravel eloquent orderby relation

I use eloquent to get data from many tables but I'm don't achieve to order my last table but "ordre" rather than "id".

My setup :

$etages_lot = EtageLot::where('lot_id', $lot_id)->with('variantes', 'variantes.piece')->get();

EtageLot model :

public function lot(){
    return $this->belongsTo('App\Models\Copro\Lot');
}

public function etage(){
    return $this->belongsTo('App\Models\Copro\Etage');
}

public function fractions()
{
    return $this->hasMany('App\Models\Copro\Fraction','etage_lot_id');
}

public function variantes()
{
    return $this->belongsToMany('App\Models\Copro\Variante', 'lot_variante')->withPivot('nombre');
}

Variante model :

    public function piece(){
    return $this->belongsTo('App\Models\Copro\Piece')->orderBy('ordre', 'asc');
}

public function EtageLot(){
    return $this->belongsToMany('App\Models\Copro\EtageLot','lot_variante')->withPivot('nombre');
}

Piece model :

public function variantes(){
    return $this->hasMany('App\Models\Copro\Variante');
}

I get all data but last part, piece, isn't order by ordre asc.

I tried :

        $etages_lot = EtageLot::where('lot_id', $lot_id)->with('etage', 'variantes')->with(['variantes.piece' => function ($query)
    {
        $query->orderBy('ordre','asc');
    }])->get();

But it doesn't work neither.

An idea why I can't get the last part of my data with another order ?

Thank for your help.

Edit : add some migrations :

etage_lot table :

        Schema::create('etage_lot', function(Blueprint $table){
        $table->increments('id');
        $table->integer('lot_id')->unsigned()->index();
        $table->integer('etage_id')->unsigned()->index();
        $table->foreign('lot_id')->references('id')->on('lots')->onDelete('cascade');
        $table->foreign('etage_id')->references('id')->on('etages')->onDelete('cascade');
    });

lot_variante table :

        Schema::create('lot_variante', function (Blueprint $table) {
        $table->increments('id');
        $table->integer('etage_lot_id')->unsigned()->index();
        $table->foreign('etage_lot_id')->references('id')->on('etage_lot')->onDelete('cascade');
        $table->integer('variante_id')->unsigned()->index();
        $table->foreign('variante_id')->references('id')->on('variantes')->onDelete('cascade');
        $table->integer('nombre')->unsigned();
        $table->unique(['etage_lot_id', 'variante_id']);
    });

variantes table :

        Schema::create('variantes', function (Blueprint $table) {
        $table->increments('id');
        $table->string('nom')->default('sans');
        $table->integer('piece_id')->unsigned()->index();
        $table->foreign('piece_id')->references('id')->on('pieces')->onDelete('cascade'); 
        $table->unique(['nom', 'piece_id']);
    });

pieces table :

        Schema::create('pieces', function (Blueprint $table) {
        $table->increments('id');
        $table->string('nom')->unique();
        $table->string('nom_pluriel')->unique();
        $table->integer('ordre')->unsigned()->unique();
        $table->string('article');
        $table->tinyInteger('show_article')->default(0);
        $table->tinyInteger('compte_pluriel')->default(0);
        $table->tinyInteger('jouissance')->default(0);

    });

Upvotes: 0

Views: 392

Answers (3)

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25936

You can use a modified withCount():

$etages_lot = EtageLot::where('lot_id', $lot_id)
    ->with(['etage', 'variantes' => function ($query) {
        $query->withCount(['piece as piece_ordre' => function ($query) {
            $query->select('ordre');
        }])->orderBy('piece_ordre')->with('piece');
    }])->get();

Upvotes: 1

Razib Al Mamun
Razib Al Mamun

Reputation: 2711

You may use the whereHas and orWhereHas methods to put "where" conditions on your has queries. These methods allow you to add customized constraints to a relationship constraint, such as checking the content of a comment:

$etages_lot = EtageLot::where('lot_id', $lot_id)
 ->with('etage', 'variantes')
 ->whereHas('variantes.piece', function ($query) {
    $query->orderBy('ordre', 'asc');
 })->get();

Upvotes: 0

Jay Kaneriya
Jay Kaneriya

Reputation: 1

    $etages_lot = EtageLot::with(['etage', 'variantes.piece'])->where('lot_id', $lot_id);

    $etages_lot->where(function ($query) {
         $query->whereHas('variantes.piece', function ($newquery) {
              $newquery->orderBy('pieces.ordre',"asc")->get()->toArray();
          });          
    });

try using this

Upvotes: 0

Related Questions