Laravel where clause with addSelect()

Hi,

I have three tables named "product", "warehouse" and "warehouse_product". I want to get total quantity for each product with selected warehouse. Tables have columns like;

product

id , name

warehouse

id , name

product_warehouse

id , product_id, warehouse_id, quantity

My code like below;

$products = Product::query() ->addSelect(['total_qty' => WarehouseProduct::query()->selectRaw('SUM(qty)')->whereColumn('product_id', '=', 'products.id')]) ->get();

When i dump my results i get the correct values. But i need record only if the "total_qty" column greater than 0. When i try to add where clause like ->where('total_qty", ">", 0) i get error;

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'total_qty' in 'where clause'

Any solution ideas for my problem? Thank you guys.

Regards.

MohamedTammam

4 years ago

Level 51

I suppose you have a relationship between your models.

$product = Product::has('warehouses', '>', 0)->get();

https://laravel.com/docs/9.x/eloquent-relationships#querying-relationship-existence

1 like

Snapey

4 years ago

Level 122

how about just using the pivot table?

DB::table('product_warehouse')
    ->select('product_id','quantity')
    ->where('warehouse_id',1)
    ->where('quantity','>',0)
	->get();

1 like

aosdev

4 years ago

Level 1

@Snapey That would be nice but i need other relations from Product model like "product_variants" and "product_images". That's why i start query with Product model.

Snapey

4 years ago

Level 122

@aosdev

$products = DB::table('product_warehouse')
    ->select('product_id','quantity','products.*')
    ->where('warehouse_id',1)
    ->where('quantity','>',0)
    ->join('products','product_id','=','products.id')
	->get();

$products->load('product_variants');

1 like

aosdev

4 years ago

Level 1

@Snapey That means i have to refactor all my codes, thank you i'll give a try. <3

Snapey

4 years ago

Level 122

@aosdev probably won't work thinking about it since these are not eloquent models

if you want models you probably need a whereHas or use the original query just to get the ids and then load all models whereIn

1 like

aosdev