У меня есть 2 таблицы: "Автор" и "Книга" со следующими прямыми отношениями:

class Author extends Model
{
    public function books() {
        return $this->hasMany('App\Book');
    }
}

class Book extends Model 
{
    public function author() {
        return $this->belongsTo('App\Author');
    }
}

Модель Book имеет атрибут price (число с плавающей запятой) и category (строка).

Я пытаюсь найти эффективный способ сделать эти 3 вещи в 1 запросе:

1. получить список всех authors, у которых есть books из category комедии, created_at между date1 и date2.
2. авторы в списке должны иметь дополнительный атрибут highest_price, который представляет собой самую высокую цену из всех книг автора, соответствующих критериям из шага 1.
3. список книг должен быть отсортирован по убыванию по price, а конечный список авторов также должен быть отсортирован по убыванию по этому новому атрибуту highest_price.

Результат должен быть таким:

[
    {
        id: 1,
        name: 'Author ABC'
        highest_price: 15
        books: [
            {
                id: 1,
                name: 'book1',
                category: 'comedy',
                price: 15
            },
            {
                id: 2,
                name: 'book2',
                category: 'comedy',
                price: 10
            }
        ]
    },
    {
        id: 10,
        name: 'Author XYZ'
        highest_price: 12
        books: [
            {
                id: 3,
                name: 'book3',
                category: 'comedy',
                price: 12
            }
        ]
    }
]

Что я получил до сих пор:

Author::with(['books' => function($query) {
    $query->where('category', 'comedy')
        ->where('created_at', '>=', $someFromDate)
        ->where('created_at', '<=', $someToDate);
}])
->has('books', '>', 0)
->get()
->dd();

Но это просто дает мне всех авторов, у которых есть книги, это не учитывает условие при нетерпеливой загрузке...

Возможно ли это даже в 1 запросе? Любая помощь будет оценена по достоинству!

РЕШЕНИЕ

Благодаря ответам я узнал, что мне нужна комбинация with() и whereHas() (ответ нашел здесь: https://stackoverflow.com/a/29594039/5297218). Поскольку условия «где» должны быть в обоих методах, я использовал область запроса на модели Author:

public function scopeWithAndWhereHas($query, $relation, $constraint){
  return $query->whereHas($relation, $constraint)
               ->with([$relation => $constraint]);
}

Для дополнительного атрибута highest_price я использовал метод transform()в Коллекции в сочетании с методом max() чтобы получить максимальную цену. Это окончательный результат:

Author::withAndWhereHas('books', function($query) use ($category, $date){
    $query->where('category', $category)
        ->where('created_at', '>=', $date->get('start'))
        ->where('created_at', '<=', $date->get('end'))
        ->orderBy('price', 'desc');
})
->get()
->transform(function ($author){
    $author['highest_price'] = $author->books->max('price');
    return $author;
})
->sortByDesc('highest_price')
->values();

PS: это решение в 42 раза эффективнее того, что я делал раньше!