CODEWITHSUNDEEP
mysqlsqllaravellaravel-5
Hassaan
Hassaan

Reputation: 328

Sql leftJoin query using laravel syntax showing error

This is my query that i used for left join, it is working fine but something is missing when I convert it to laravel syntax.

Query to convert is 

$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");

and the I am doing this

$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();

The error is

InvalidArgumentException in F:\xampp\htdocs\arheb\Arheb\vendor\laravel\framework\src\Illuminate\Database\Query\JoinClause.php line 79: Not enough arguments for the on clause.

Upvotes: 0

Views: 193

Answers (2)

Pankit Gami
Pankit Gami

Reputation: 2553

Your query is wrong. I assume that amenities.id and rooms.amenities are attributes of amenities and rooms table respectively.

MySQL FIND_IN_SET() returns the position of a string if it is present (as a substring) within a list of strings.

You need to pass column names in first and second parameter of on() function. 

$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();

Upvotes: 1

AddWeb Solution Pvt Ltd
AddWeb Solution Pvt Ltd

Reputation: 21681

I think you can try this:

$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();

Hope this work for you!

Upvotes: 0

Related Questions