CODEWITHSUNDEEP
laraveleloquent
EnikiBeniki
EnikiBeniki

Reputation: 981

Eloquent: How to get data from three related tables?

I am trying to get all orders associated with a Product and an Event.

Must params:

Here are my code:

Product: [id, name].

class Product    

// get Product related events
public function events()
{
   $this->hasMany(Order::class)
}

// get Product related orders
public function orders()
{
   $this->hasMany(Event::class)
}

Event: [id, product_id, date]

class Event

// get related Product
public function product()
{
   $this->belognsTo(Product::class)
}

Order: [id, date, order_product_id]

public function product()
{
   $this->belognsTo(Product::class,'order_product_id')
}

// I Want to get all Events, where present Product with 'order_product_id'
public function scheduled()
{
   $this->hasMany(Product::class,'order_product_id', 'product_id')
}

In summary:

If I have an Event with the id=9, how do get the number of orders of the same product for associated to this event.

Upvotes: 1

Views: 112

Answers (2)

EnikiBeniki
EnikiBeniki

Reputation: 981

I solved this problem.

Add to Event model class two methods:

public function scheduledOrders()
{
    return $this->orders()->whereHas('product', function ($q) {
        $q->whereHas('scheduled', function ($q) {
            $q->whereRaw("events_table.date = orders_table.date")->where('id',$this->id);
        });
    })->get();
}

public function orders()
{
    return $this->hasMany(Order::class, 'order_product_id', 'product_id');
}

And when we get concrete Event, we can look all related orders with same date and product_id.

$scheduledOrders = Event::find(14)->scheduledOrders();

scheduledOrders() method will return related Orders [Collection] with the same date and product_id which has this Event.

Upvotes: 0

Alexey Mezenin
Alexey Mezenin

Reputation: 163748

To count how many orders with the same product present in orders table you can use withCount():

$eventId = 9;
$product = Product::whereHas('events', function($q) use ($eventId ){
               $q->where('id', $eventId);
           })
           ->withCount('orders')
           ->get();

This code will return Product with additional attribute orders_count.

Another way is to use simple queries:

$productId = Event::where('id', 9)->first()->id;
$ordersCount = Order::where('order_product_id', $productId)->count();

Also, you have an error in relations. orders() pointed to Event class and events() pointed to Order.

Upvotes: 2

Related Questions