Laravel - Filtering a Model by Condition between "a Field and Its Relationship Model Field"

Question

I have these two models with one to one relationship.

"products"

• id
• name
• minimum_required

"product_data"

• id
• product_id
• price
• oh_hand

I want to get the count of product_data where its on_hand is less than its related product's minimum_required.

I've tried subqueries and I still can't figure it out. The query I want may looks something like this.

$low_products_count = ProductDetail::where('on_hand', '<', Product::select('minimum_required')->count();

Answer 1

you can join the tables then use 'whereColumn':

 $low_products_count =Product::join('product_data','product_data.product_id','=',
    'products.id')->whereColumn('product_data.on_hand','<','products.minimum_required')->get();