php nested query in laravel using query builder

Question

I am making ecommerce app api using laravel and have 1 table for product data and another table for product images

This query works fine. I want nested query in which each product will have its list of images.I am making android app and need to show multiple product images in carousel slider and need to get nested JSON

$products = DB::table('products')->where ('stock_quantity',  '<>' , 0 )->inRandomOrder()->get();

in products table I have unique 'id' and and same id is used in product_images table to map data.

you should add table/model relations between product and product images, eg hasMany and belongsTo then fetch the products with(images). In general outline — Nikos M.
– Nikos M., Commented Jul 4, 2020 at 15:57
i have read in few articles that eloquent is slow compared query builder. Also, I need to filter product where stock <> 0, which i am not sure in eloquent model. — chetan suri
– chetan suri, Commented Jul 4, 2020 at 16:06

OMR · Accepted Answer · 2020-07-04 16:36:18Z

1

i think simple join will do the purpose:

 $products = DB::table('products')->where ('stock_quantity',  '<>' , 0 )->join('product_images',
            'product_images.product_id','products.id')->select('product.*','product_images*')
            ->get();

however, i must say that i don't recommend this kind of storing images, you should store a product_image for each row, not 7 product image for each row ....

answered Jul 4, 2020 at 16:36

OMR

12.3k5 gold badges26 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

chetan suri Over a year ago

I am able to use joinSub and get all 7 images. what i need is those 7 images to nested . joinSub query

$products = DB::table('products')->where ('stock_quantity',  '<>' , 0 )->inRandomOrder()  //          ->joinSub($product_images = DB::table('product_images'), 'product_images', function ($join) {$join->on('products.id', '=', 'product_images.product_id');})->get();

OMR Over a year ago

if you want those images to be nested you should rebuild tables schema and build eloquent models

Collectives™ on Stack Overflow

php nested query in laravel using query builder

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related