Convert DB Query to eloquent

I am trying get data from 3 tables

My tables:

table 1: bookings

Id,
dog_id
service_id
user_id

table 2: services

id
service_name

table 3: dogs

id
dog_name

This is my current DB query which is work but I want laravel Eloquent in mysql Dog Controller:

$pendingBookingInfo = DB::table('bookings')
                        ->select('*')
                        ->leftJoin('dogs','dogs.id','=','bookings.dog_id')
                        ->leftJoin('services','services.id','=','bookings.service_id')
                        ->where('bookings.user_id','=',auth()->user()->id)
                        ->where('bookings.booking_status','=','Pending')
                        ->orderBy('bookings.id', 'DESC')
                        ->get();

Please help! Thanks

azizulislam

2 years ago

Level 1

Expected Output:

DogName, ServiceName, BookingStatus

Thunderson

2 years ago

Level 2

first wrap your code in 3 backticks,that will format your code that way " code"

1 like

Thunderson

2 years ago

Level 2

here we have services belongs to many dogs and dogs belongs to many services by bookings table as a pivot, i think Booking is also a model in your app.

// in Service model 
public function dogs():BelongsToMany {
   return $this->belongsToMany(Dog::class, 'bookings', 'service_id','dog_id')
   ->using(Booking::class)->withPivot('booking_status')->withTimestamps();
}
// in Dog model
public function services():BelongsToMany {
   return $this->belongsToMany(Service::class, 'bookings', 'dog_id','service_id')
   ->using(Booking::class)->withPivot('booking_status')->withTimestamps();
}
// Booking model
class Booking extends Pivot
{
    // ...
    public function user():BelongsTo {
       return $this->belongsTo(User::class);
    }
    public function dog():BelongsTo {
       return $this->belongsTo(Dog::class);
    }
    public function service():BelongsTo {
       return $this->belongsTo(Service::class);
    }
}

then in your controller you can use this

Booking::with('dog','service')->where('user_id', auth()->user()->id)
->where('booking_status','Pending')->get()

1 like

azizulislam

2 years ago

Level 1

@thunderson Thank you so much! It is worked!

Question: If I want to pending booking under the dog.

$dog = Dog::with('booking','service')->where('user_id', auth()->user()->id)->get();

piljac1

2 years ago

Level 28

@azizulislam If I understand correctly, you want your models from your bookings dog relation to also include a relation to the booking and the service? If that's the case, just modify the query provided by @thunderson this way:

Booking::with('dog.booking', 'dog.service', 'service')
    ->where('user_id', auth()->user()->id)
    ->where('booking_status', 'Pending')
    ->get()

or with an array syntax:

Booking::query()
    ->with([
        'dog' => [
            'booking',
            'service',
        ],
        'service',
    ])
    ->where('user_id', auth()->user()->id)
    ->where('booking_status', 'Pending')
    ->get()

Please or to participate in this conversation.