这是我用于左JOIN的查询,它可以正常工作,但是当我将其转换为Laravel语法时缺少某些东西。
查询要转换的是
$result = DB::select("select amenities.name as
name,amenities.type_id,amenities.id as id, amenities.icon, rooms.id as status
from amenities left join rooms on find_in_set(amenities.id, rooms.amenities)
and rooms.id = $room_id and type_id !=4");
我正在做这个
$result = DB::table('amenities')
->select('amenities.name as name', 'amenities.type_id' , 'amenities.id as id'
, 'amenities.icon', 'rooms.id as status' )
->leftJoin('rooms', function ($join) {
$join->on('FIND_IN_SET(amenities.id, rooms.amenities)')
->where('rooms.id' , '=', '$room_id')
->where('type_id','!=', 4);
})->get();
错误是
无效 f: xampp htdocs arheb arheb arheb vendor laravel framework src src iLluminate database query query joinclause.php 第79行:关于on子句的论点不足。
您的查询是错误的。我认为amenities.id
和rooms.amenities
分别是amenities
和rooms
表的属性。
mysql find_in_set((如果字符串存在(作为子字符串(在字符串列表中,则返回字符串的位置。
您需要在on()
函数的第一个和第二个参数中传递列名。
$result = DB::table('amenities')
->select('amenities.name as name', 'amenities.type_id' , 'amenities.id as id'
, 'amenities.icon', 'rooms.id as status' )
->leftJoin('rooms', function ($join) {
$join->on('amenities.id', '=', 'rooms.amenities')
->where('rooms.id' , '=', '$room_id')
->where('type_id','!=', 4);
})->get();
我认为您可以尝试以下操作:
$result = DB::table('amenities')
->select('amenities.name as name', 'amenities.type_id' , 'amenities.id as id'
, 'amenities.icon', 'rooms.id as status' )
->leftJoin('rooms', function ($join) {
$join->on(DB::raw("find_in_set(amenities.id, rooms.amenities)"))
->where('rooms.id' , '=', '$room_id')
->where('type_id','!=', 4);
})->get();
希望这项工作适合您!