CODEWITHSUNDEEP
phpmysqllaraveleloquent
Manuel Bosi
Manuel Bosi

Reputation: 123

Laravel 7 pass through relationship and access final table

Hi i have these 4 tables: users, dishes, dish_order, orders.

users: some fields no foreign key

dishes

dish_order

orders: some fields no foreign key

These are the models: User, Dish, Order.

User:

class User extends Authenticatable
{
use Notifiable;

/**
 * The attributes that are mass assignable.
 *
 * @var array
 */
protected $fillable = [
    'email',
    'password',
    'company_name',
    'address',
    'vat',
    'phone_number',
    'opening_info',
    'website',
    'vote_average'
];

/**
 * The attributes that should be hidden for arrays.
 *
 * @var array
 */
protected $hidden = [
    'password', 'remember_token',
];

/**
 * The attributes that should be cast to native types.
 *
 * @var array
 */
protected $casts = [
    'email_verified_at' => 'datetime',
];

public function dishes() {
    return $this -> hasMany(Dish::class);
}

public function typologies() {
    return $this -> belongsToMany(Typology::class);
}
}

Dish:

class Dish extends Model
{
protected $fillable = [
    'name',
    'desc',
    'price',
    'visible',
    'type',
];

public function user() {
    return $this -> belongsTo(User::class);
}

public function orders() {
    return $this -> belongsToMany(Order::class);
}
}

Order:

class Order extends Model
{

protected $fillable = [
    'code',
    'customer_name',
    'customer_address',
    'customer_phone',
    'date',
    'status',
    'total_price',
];


public function dishes() {
    return $this -> belongsToMany(Dish::class);
}
}

Now in the Controller i need to find all orders related to the user (for example with ID 18). I tried this and it's work but in this case i lose all dishes related to the order.

$loggedUserId = Auth::user() -> id;
$orders = DB::table('users')
          -> join('dishes', 'users.id', '=', 'dishes.user_id')
          -> join('dish_order', 'dishes.id', '=', 'dish_order.dish_id')
          -> join('orders', 'orders.id', '=', 'dish_order.order_id')
          -> select('orders.*', DB::raw('COUNT(dishes.id) as dishes'))
          -> groupBy('orders.id')
          -> where('user_id', $loggedUserId)
          -> get();

Result: Array/Collection of orders and each order should have an array inside with list of dishes related to the order. If it's possibile, how can i do this? Thanks.

Upvotes: 1

Views: 279

Answers (1)

Anurat Chapanond
Anurat Chapanond

Reputation: 2987

You already have eloquent relationship in place in your models so you can utilize those relationships.

$orders = Order::whereHas('dishes', function($query) {
        $query->where('user_id', auth()->id());
    })
    ->with(['dishes' => function($query) {
        $query->where('user_id', auth()->id());
    }])
    ->get();

The first part whereHas(...) will get only the orders that the logged in user has dishes.
The second part with(...) will get only the dishes belonging to the logged in user.

Then you can iterate orders and dishes

foreach($orders as $order) {
    $order->dishes;
}

Upvotes: 0

Related Questions