Easy way to convert raw mysql query to Eloquent

Posted 1 month ago by johnef_sh

How can I convert a row MySql query to Eloquent

I have a query like this

SELECT * FROM
( SELECT products.id, products.image, products.price, products.featured_item as featured, product_translations.name as name, product_translations.details as details FROM products LEFT JOIN product_translations on products.id=product_translations.product_id where products.quantity > 0 AND products.product_category_id IS NOT NULL AND products.soft_delete=0 AND product_translations.locale='ar' ) a LEFT JOIN ( SELECT *, COUNT(order_details.product_id)as count FROM order_details where order_details.created_at BETWEEN CURRENT_TIMESTAMP - INTERVAL 10 MONTH AND CURRENT_TIMESTAMP GROUP BY order_details.product_id ORDER BY count DESC limit 12 ) b ON b.product_id = a.id ORDER BY count DESC, featured DESC LIMIT 24

I try something like this with Eloquent but still not the same sorting

$query    = ( new Product() )
            ->where( 'quantity', '>', 0 )
            ->where( 'soft_delete', 0 )
            ->where( 'product_category_id', '!=', null )
            ->with( [
                'orderDetail' => function ( $query ) {
                    $query->whereBetween( 'created_at', [ Carbon::now()->subMonth( 10 ), Carbon::now() ] )
                          ->groupBy( 'product_id' )
                          ->take( 12 )->count();
                }
            ] )
            ->where( 'featured_item', 1 )
            ->orderByDesc( 'id' )
            ->where( 'product_category_id', '!=', null )
            ->where( 'soft_delete', 0 )
            ->with( [
                'campaign' => function ( $q ) {
                    $q->select( [ 'id', 'product_id', 'price', 'purchasesLimits', 'stock' ] );
                    $q->where( 'stock', '>', 0 );
                    $q->where( 'expire', '>', Carbon::today()->toDateString() );
                }
            ] );
        $products = $query
            ->select( 'products.*' )
            ->with( 'translations' )
            ->paginate( 16 );

        return response()->json( [
            $products
        ] );

Of course a have all the tables relations. in the models

Please sign in or create an account to participate in this conversation.

Reply to

Use Markdown with GitHub-flavored code blocks.