CODEWITHSUNDEEP
phplaravel
Farshad
Farshad

Reputation: 2000

laravel join on query not working and gives ambiguous error

I am trying to append a join to my query builder and I get this error:

message: "SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id' in where clause is ambiguous (SQL: select * from accommodation_rooms inner join room_pricing_histories on accommodation_rooms.id = room_pricing_histories.accommodation_room_id and room_pricing_histories.from_date = 2019-10-05 15:49:21 where id in (1, 2) order by room_pricing_histories.sales_price desc limit 1)", code: "23000", status_code: 500,

This is my code here 

public function cheapestRoom()
{
    $roomIds = $this->accommodationRooms()->pluck('id')->toArray();
    return AccommodationRoom::whereIn('id', $roomIds)
        ->join('room_pricing_histories', function ($join) {
            $join->on('accommodation_rooms.id', '=', 'room_pricing_histories.accommodation_room_id')
                ->where('room_pricing_histories.from_date', Carbon::now());
        })
        ->orderBy('room_pricing_histories.sales_price', 'DESC')
        ->first();
}
public function getCheapestRoomAttribute()
{
    return $this->cheapestRoom();
}
protected $appends = ['cheapest_room'];

what I am trying to do is to get the cheapest room and append that to hotel model

Upvotes: 0

Views: 608

Answers (1)

Salim Djerbouh
Salim Djerbouh

Reputation: 11034

Update your whereIn query clause to specify which table to query from

Either

return AccommodationRoom::whereIn('accommodation_rooms.id', $roomIds)

Hope this helps

Upvotes: 1

Related Questions