lifesound's avatar

get relation of many to many - of another one to many

I have a shipments table with one to many relation shipment->hasMany Products

also, products have many to many relation -> pivot table product_state

I want to get each shipment states' products count

as an example

shipment number 1000 has 2000 products

some products have these states ( "screen broken", " no processor " ...etc).

I want to know how many "screen broken" products are for shipment 1000. and the same for each state.

tables names ( shipments, products, states, product_state)

and I have made all relations in their Models( Shipment, Product, State )

thx in advance

0 likes
2 replies
tisuchi's avatar
tisuchi
2 years ago
Best Answer
Level 70

@lifesound You can try this:

$shipmentId = 1000;

$stateCounts = DB::table('products')
    ->join('product_state', 'products.id', '=', 'product_state.product_id')
    ->join('states', 'states.id', '=', 'product_state.state_id')
    ->where('products.shipment_id', $shipmentId)
    ->select('states.name', DB::raw('count(states.id) as state_count'))
    ->groupBy('states.name')
    ->get();

⚠️ You may need to tweak the column name based on your table. I jus try with the regular convention. Please keep it in mind.

2 likes

Please or to participate in this conversation.