Compose a query on Laravel Query Builder?

Question

MySQL

Good afternoon dear ones.
I broke my whole head, I can’t compose the following query on Laravel Query Builder.
Help me please.

SELECT products.title, products.price, x.sales  
 
FROM products 
        
      INNER JOIN 
            (
            SELECT product_id, 
            
            MAX(sales) AS sales 
            
            FROM offers 
            
              GROUP BY product_id 
            
            ) x
            
      ON x.product_id = products.id 
            
        
ORDER BY x.sales DESC LIMIT 20

Tried like this:

$populars = DB::table('products')

    /* ->select(DB::raw(' JOIN (SELECT product_id, MAX(sales) AS sales FROM offers GROUP BY product_id) ON offers.product_id = products.id  ')) */

    ->select( DB::table('offers')
                  ->join('offers.product_id', '=', 'products.id')
                  ->select('product_id')
                  ->max('sales')
                  ->groupBy('offers.product_id')
                  ->get()
            )
    ->orderBy('offers.sales', 'desc')
    ->take(20)
    ->get();

but gives an error:

SQLSTATE[42000]: Syntax error or access violation: 1049 Unknown database 'offers' (SQL: select max(`sales`) as aggregate from `offers` inner join `offers`.`product_id` on `=` = `products` .`id`)

Answer 1

Thank you.
I wrote incorrectly about the work of collections in the model.
here is the solution:

$populars = DB::table('products')
      ->join('offers', 'products.id', '=', 'offers.product_id')
      ->orderBy('offers.sales', 'desc')
      ->get()
      /*   */
      ->unique('title')
      ->take(20);

    return $populars;

This is a hybrid solution, query builder and collections, now you only need it on the query builder :)

Answer 2

$results = DB::select( DB::raw("SELECT products.title, products.price, x.sales  
 
FROM products 
        
      INNER JOIN 
            (
            SELECT product_id, 
            
            MAX(sales) AS sales 
            
            FROM offers 
            
              GROUP BY product_id 
            
            ) x
            
      ON x.product_id = products.id 
            
        
ORDER BY x.sales DESC LIMIT 20") );

Why bother much if you don't need a collection of models, but just three fields.

Answer 3

you have an error in the join syntax, try this:
if anything, here is a link to the place in the dock that you need
https://laravel.com/docs/5.8/queries#joins

Answer 4

See what picture.
First, there is Progressive jpeg
. In many cases, a png is much smaller than a jpeg (if there are large monochrome areas, for example).
Or, if the design allows, you can replace the image with a vector one.

Answer 5

Compress, make several resizes and connect the one you need depending on the screen size, shrink icons and small pictures into sprites.