Eloquent Query Limit by field value

Can you show your migrations ?

Your second code snippet, has nothing wrong, if needing to flatten it bothers you, use ->concat() instead of ->push():

$lesson_ids = [156,157,158,159,160,161,162,163,164,165,166];

$questions = Question::query()
    ->where('quiz_id', $quizId)
    ->whereIn('related_lesson', $lesson_ids)
    ->get();

$questionsLimited = collect();

foreach ($lesson_ids as $id) {
    $questionsLimited->concat(
        $questions->where('related_lesson', $id)->shuffle()->take(4)
    );
}

return $questionsLimited->shuffle()->values();

• I added the ->whereIn('related_lesson', $lesson_ids) to filter down the lessons and retrieve only related rows from the database.
• I added a ->shuffle() call to get random elements for each related lesson.
• I also added a final ->shuffle() call to randomize the questions orders regardless of its related lesson.
• The ->values() call just reindex the results so the final collection is keyed in a sequence.

Overall, retrieving around 100 records, and filtering it down to 40, is not a big deal, performance-wise. If you are happy with the code, and understand it well, I would leave it like this.

But, if you still think the code is not "elegant" enough, you could try:

1. Avoiding foreach and using collection methods

$quizId = 1;
$lessonIds = [156,157,158,159,160,161,162,163,164,165,166];

return Question::query()
    ->whereIn('related_lesson', $lessonIds)
    ->where('quiz_id', $lessonIds)
    ->get()
    ->groupBy('related_lesson')
    ->flatMap(fn ($questions) => $questions->shuffle()->take(4))
    ->shuffle()
    ->values();

This does the same thing than the code above, but chains collection methods to avoid calling foreach

2. Using a LATERAL JOIN

MySQL since version 8.0.14 added support to LATERAL JOIN. PostgreSQL has support for it since 9.3.

In raw SQL it would be something like this:

SELECT
    `questions`.*
FROM
    `lessons`
    CROSS JOIN LATERAL (
        SELECT *
        FROM `questions`
        WHERE
            `related_lesson` = `lessons`.`id`
            AND `quiz_id` = 1
        ORDER BY RAND()
        LIMIT 4
    ) AS `questions`
WHERE `lessons`.`id` IN (156,157,158,159,160,161,162,163,164,165,166)
ORDER BY RAND();

A LATERAL JOIN can reference previous table and would execute like a related subquery.

Unfortunately Laravel does not support LATERAL JOINS out of the box.

To use it you can try a couple of options:

• Use fromRaw and have the raw SQL query in your code
• add a macro to the query builder to add support to Lateral Join
• use ->tap() to manually manipulate the query object

I will illustrate the third option, and you can search about the other options if you will:

$quizId = 1;
$lessonIds = [156, 157, 158, 159, 160, 161, 162, 163, 164, 165, 166];

return Question::query()
    ->from('lessons')
    ->tap(function (Builder $builder) use ($quizId) {
        $subQuery = Question::query()
            ->whereColumn('related_lesson', 'lessons.id')
            ->where('quiz_id', $quizId)
            ->inRandomOrder()
            ->take(4)
            ->toBase();

        $join = $subQuery->toSql();
        $bindings = $subQuery->getBindings();

        $query = $builder->getQuery();

        $query->addBinding($bindings, 'join');
        $query->joins[] = new JoinClause($query, 'CROSS', new Expression("LATERAL ($join) AS questions"));
    })
    ->select(['questions.*'])
    ->whereIn('lessons.id', $lessonIds)
    ->inRandomOrder()
    ->get()
    ->values();

Note that here, although querying from the Question model, I change the from table to lessons, as we need it to filter out the LATERAL JOIN.

The reason I didn't use the Lesson model instead, is because at the end you want a collection of Question instances and not Lesson instances. This is the reason I added a ->select(['questions.*']) call, so only the fields from the questions table are populated into the resulting models.