How to join 4 tables with the Eloquent ORM

I have 4 tables...

+-------+
| User  |
+-------+
| id    |
| email |
| ...   |
+-------+

+---------+
| Order   |
+---------+
| id      |
| user_id |
| ...     |
+---------+

+-------------------+
| Order_Promo_Codes |
+-------------------+
| order_id          |
| promo_code_id     |
| ...               |
+-------------------+

+-------------+
| Promo_Codes |
+-------------+
| id          |
| code        |
| ...         |
+-------------+

Users have zero or more Orders.

Orders have zero or more Order_Promo_Codes.

Promo_Codes have zero or more Order_Promo_Codes.

Order_Promo_Codes is basically all of the instances of Promo_Codes applied to an Order.

I have the various hasMany(...) and belongsTo(...) relationships defined in the models.

I'm trying to find Orders based on a specific Promo_Code.code and I'm not sure how to do this with Laravel's Eloquent ORM.

Roughly the query I'd like to generate is:

select count(o.*), sum(o.total), pc.code
from orders as o
inner join order_promo_code as opc on opc.order_id = o.id
inner join promo_codes as pc on pc.id = opc.promo_code_id
where pc.code in ('ABC', 'XYZ')
group by pc.code

I've tried various things including hasManyThrough(...) but can't get Eloquent to generate a correct query.

How can I retrieve the order data based on the promo codes using Eloquent?

from Newest questions tagged laravel-5 - Stack Overflow http://ift.tt/1LpanHu
via IFTTT