how to select from subquery using query builder in laravel 5

Question

please help me, i have query: SELECT * from (select * from products ORDER BY id DESC LIMIT 20) AS result ORDER BY discount DESC LIMIT 14

so, how to convert into query builder in laravel 5. tks!

Answer 1

Using the query builder and importing (use) DB, allows you to build and fetch the result you want.

use DB;

$result = DB::table(
    DB::raw("(" . 
        DB::table('products')
          ->select('*')
          ->orderBy('id', 'desc')
          ->limit(20)
          ->toSql()
        . ") as result"
    )
)
->select('*')
->orderBy('discount', 'desc')
->limit(14)
->get();

table() selects the table you want to query. In this case we are building up a separate SQL statement to fetch the table.

select() the columns you would like to see.

orderBy($column, $direction) where $column is the name of the column you would like to order and $direction is the order, desc or asc.

limit($limit) only return $limit items to the result set.

toSql() returns the current QueryBuilder SQL statement as a string.

get() returns the data in a Collection.

Also added in the missing discount ordering.

Answer 2

Try something like:

Products::orderBy('id', 'desc')->take(20)->orderBy('discount', 'desc')->take(14)->get();