How to sort Schools by eloquent courses promo ASC , price ASC and also all courses as promo_price ASC , price ASC

Here's a clean solution for the requirements you've described — order schools (parent) by their lowest course promo_price (or price if promo_price is null), and also sort courses inside each school by promo_price or price ascending.
This is a common scenario (sorting on a relationship field), but note:
Eloquent does NOT support relationship-based sorting on parents in pure SQL.
You have to either join or do sorting in PHP after retrieval (using collection methods).

Ideal Solution:
Let's cover both — the right way for eager loading, and the performance-efficient approach if you have a large dataset.

1. Getting schools with their courses, tagged & filtered

We'll use withWhereHas for the query, and eager load the attributes, calendar, and tags.
We’ll also do sorting of courses using a custom relationship.

In your School model:

public function sortedCourses()
{
    return $this->hasMany(SchoolCourse::class)
        ->where('active', '>=', 1)
        ->join('course_attributes', 'school_courses.id', '=', 'course_attributes.school_course_id')
        ->orderByRaw('
            CASE 
                WHEN course_attributes.promo_price IS NOT NULL THEN course_attributes.promo_price 
                ELSE course_attributes.price 
            END ASC
        ')
        ->with(['calendar', 'tags.category:id,name', 'attributes']);
}

(Be careful: .with() doesn't work after a join, so we only eager load directly related data.)

2. Query & filter all schools with courses matching tag(s)

$attr = request()->validate([
    // your validation rules here; assuming structure...
    'attributes.category' => 'required|int',
    'attributes.course_type' => 'required|int',
]);

$schools = School::whereHas('courses', function ($course_query) use ($attr) {
    $course_query->where('active', '>=', 1)
        ->whereHas('tags', function ($query) use ($attr) {
            $query->whereIn('id', [
                $attr['attributes']['category'], 
                $attr['attributes']['course_type']
            ]);
        }, '=', 2);
})
// Eager-load the sortedCourses relationship we defined
->with(['sortedCourses' => function($q) use ($attr) {
    // you could add more filters if needed here, for tags
    $q->with(['calendar', 'tags.category:id,name', 'attributes']);
}])
->get();

3. Sort schools by the lowest promo_price (or price) among their courses

You cannot sort schools by related table fields in SQL unless you’re willing to build a complex join/subquery. So, do it in PHP after retrieval:

$schools = $schools->sortBy(function($school) {
    // Extracts all the courses' promo/price and finds the min for each school
    $prices = $school->sortedCourses
        ->map(function($course) {
            return $course->attributes 
                ? ($course->attributes->promo_price ?? $course->attributes->price) 
                : null;
        })
        ->filter(fn($v) => $v !== null);

    // Get the minimum price or a large number if no valid prices
    return $prices->isNotEmpty() ? $prices->min() : PHP_INT_MAX;
})->values();

Full Example

$attr = request()->validate([
    'attributes.category' => 'required|int',
    'attributes.course_type' => 'required|int',
]);

$schools = School::whereHas('courses', function ($course_query) use ($attr) {
    $course_query->where('active', '>=', 1)
        ->whereHas('tags', function ($query) use ($attr) {
            $query->whereIn('id', [
                $attr['attributes']['category'], 
                $attr['attributes']['course_type']
            ]);
        }, '=', 2);
})
->with(['sortedCourses' => function($q) {
    $q->with(['calendar', 'tags.category:id,name', 'attributes']);
}])
->get();

$schools = $schools->sortBy(function($school) {
    $prices = $school->sortedCourses
        ->map(function($course) {
            return $course->attributes 
                ? ($course->attributes->promo_price ?? $course->attributes->price) 
                : null;
        })
        ->filter(fn($v) => $v !== null);

    return $prices->isNotEmpty() ? $prices->min() : PHP_INT_MAX;
})->values();

Key Points

• You cannot eager load with join and use with() on the same relationship because joins return flat rows.
• To sort schools by a computed value on a related table, fetch all, then sort in PHP with collections.
• To sort courses within each school, define a custom relationship using a join and orderByRaw, or sort them in the collection after fetching.

Performance Note

If the number of Schools is LARGE, you need a native SQL approach for efficient sorting (using join, groupBy, and min() subquery).
But for most apps, eager-loading + PHP sort as above is clean, maintainable, and works well.

Let me know if you need the pure SQL version for the schools as a single paginatable query!