Skip to content
Advertisement

Order by field in many to many relation (Laravel)

I have a model School wich has a belongsToMany relation:

class School extends Model
{
    protected $fillable = [
        "url",
        "title",
        "is_own",
    ];

    public function events()
    {
        return $this->belongsToMany(Event::class, "event_school");
    }
}

How I can order school by event start_at field? For example, can I do like this?

School::whereHas("events")
    ->with([
        "events" => function ($query) {
            $query->latest("start_at")->limit(4);
        },
        "events.address"
    ])->orderBy("events.start_at")->paginate(4);

Is it posible? Please tell me how to implement this. thanks.

UPD: I try with join, yes it works but not properly:

Schools::whereHas("events")
        ->select("schools.*")
        ->join("event_school", "schools.id", "=", "event_school.school_id")
        ->join("events", function ($join) {
            $join->on("events.id", "=", "event_school.event_id")
                ->whereDate("events.start_at", ">=", Carbon::now()->format("Y-m-d"))
                ->limit(4);
        })
        ->join("addresses", "events.address_id", "=", "addresses.id")
        ->groupBy("schools.id")
        ->orderBy("events.start_at")

I’l geting all events. ->whereDate("events.start_at", ">=", Carbon::now()->format("Y-m-d")) not work, and not load addresses

Advertisement

Answer

Solution:

    School::whereHas("events")
        ->select("schools.*")
        ->join("event_school", "schools.id", "=", "event_school.school_id")
        ->join("events", function ($join) {
            $join->on("events.id", "=", "event_school.event_id");
        })
        ->with([
            "events" => function ($query) {
                $query->whereDate("start_at", ">=", Carbon::now()->format("Y-m-d"))
                    ->orderBy("start_at", "asc")
                    ->limit(4);
            },
            "events.address"
        ])
        ->groupBy("schools.id")
        ->orderBy("events.start_at");
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement