Question 283386 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel equivalent of a nested Query using Models

Looking for laravel equivalent of a Query.

SELECT *
FROM  (
   SELECT * FROM tbl
   ORDER  BY updated_at
   LIMIT  20
   )
ORDER  BY random()
LIMIT  5;

Please assume we have a model class "TableA" that already exists.
Inner query can be achieved using.

TableA::OrderByRaw('updated_at ASC NULLS FIRST')->limit(20)->get();
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    This is kind of hilarious, but I modified the query to below (for equivalent result).

    SELECT *
FROM  tbl WHERE id IN (
   SELECT id FROM tbl
   ORDER  BY updated_at
   LIMIT  20
   )
ORDER  BY random()
LIMIT  5;

    This version ends up taking much less db memory (for processing) and has very similar execution time.

    Please note : This works in my case, as My "in" clause will never exceed 150 elements.

    This can be achieved in laravel using,

    TableA::whereIn('id', function ($query) {
                    $query->select('id')->from('tbl')
                        ->OrderByRaw('updated_at ASC NULLS FIRST')
                        ->limit(20);
                })->inRandomOrder()->limit(5);

  2. 0

    You can use a subquery to do this. I would prefer this way as it seems more readable.

    <?php

$rows = TableA::whereIn('id', function($query){
        $query->select('id')
              ->from('table_a')// name of the model table
              ->orderByRaw('updated_at ASC NULLS FIRST')
              ->limit(20);
    })->orderByRaw('random()')->limit(5)->get();// use ->toSql() instead of ->get() to see the actual raw query

dd($rows);

    Note For MySQL users, you can use joins to avoid This version of MariaDB doesn't yet support 'LIMIT &amp; IN/ALL/ANY/SOME subquery' error. See here.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search