How to order Eloquent query by `belongsToMany` relationship column?

I have a model Event with the following relationship:

public function labels() {
        return $this->belongsToMany(EventLabel::class, 'event_event_labels');
    }

I would like to retrieve all events ordered by a property priority on the EventLabel model. The thing is that some events do not have corresponding labels. Therefore, I want these events to be placed at the end of the ordered result set. Further, I want to apply other query clauses, too.

I tried the following:

Event::selectRaw("events.*, event_labels.*, event_event_labels.*")
                     ->join('event_event_labels', 'event_event_labels.event_id', '=', 'events.id')
                     ->join('event_labels', 'event_labels.id', '=', 'event_event_labels.event_label_id')
                     ->whereDate('start_date', '>', $now)
                     ->whereDate('register_date', '<', $now)
                     ->whereDate('unregister_date', '>', $now)
                     ->orderBy('event_labels.priority', 'desc');

But this only returns three events with the ids 1, 2 and 3, which don't even exist on the database.

pardeepkumar

7 years ago

Level 2

public function abc() {
  return $this
    ->belongsToMany('Song')
    ->withPivot('play_count')
    ->orderBy('pivot_play_count', 'desc');
}

1 like

FerdinandFrank

7 years ago

Level 9

Thanks for the respond, but the property priority is not on the pivot table, but on the event_labels table. So the schema of the three tables are the following:

Schema::create('events', function (Blueprint $table) {
    $table->increments('id');
    ...
});

Schema::create('event_labels', function (Blueprint $table) {
    $table->increments('id');
    $table->unsignedTinyInteger('priority');
            ...
});

 Schema::create('event_event_labels', function (Blueprint $table) {
    $table->unsignedInteger('event_label_id');
    $table->unsignedInteger('event_id');

    $table->primary(['event_label_id', 'event_id']);    
});

FerdinandFrank

7 years ago

Best Answer

Level 9

Ok, I solved it! The problem was the content of the selectRaw. So this works:

Event::selectRaw("events.*")
                     ->join('event_event_labels', 'event_event_labels.event_id', '=', 'events.id')
                     ->join('event_labels', 'event_labels.id', '=', 'event_event_labels.event_label_id')
                     ->whereDate('start_date', '>', $now)
                     ->whereDate('register_date', '<', $now)
                     ->whereDate('unregister_date', '>', $now)
                     ->orderBy('event_labels.priority', 'desc');

Please or to participate in this conversation.