Left join not working properly

Question

I'm trying to search all the users registered for a particular course using the following query using the laravel.

$users = DB::table('users')
            ->leftJoin('registrationrequests', 'users.id', '=', 'registrationrequests.user_id')
            ->where('firstname', 'LIKE', "%$search%")
            ->orWhere('lastname', 'LIKE', "%$search%")
            ->orWhere('username', 'LIKE', "%$search%")
            ->where('registrationrequests.course_id', $course_id)
            ->where('registrationrequests.registered', 1)
            ->get();

In the table registrationrequests, I have columns for course_id, user_id and registered (binary value). course_id and user_id are foreign keys for respective tables.

I'm getting an array of output. But it's not checking the condition where('registrationrequests.course_id', $course_id)

What might be the reason?

have you looked at http://stackoverflow.com/a/18660266/689579? — Sean, Dec 31 '14 at 04:53
So you have tried `->where('registrationrequests.course_id', $course_id) ->where('registrationrequests.registered', 1) ->where(function($users){ $users->where('firstname', 'LIKE', "%$search%") ->orWhere('lastname', 'LIKE', "%$search%") ->orWhere('username', 'LIKE', "%$search%")}) ->get();`? — Sean, Dec 31 '14 at 05:03
`$users = DB::table('users') ->where('registrationrequests.course_id', $course_id) ->where('registrationrequests.registered', 1) ->where(function($users) use($search){ $users ->where('firstname', 'LIKE', "%$search%") ->orWhere('lastname', 'LIKE', "%$search%") ->orWhere('username', 'LIKE', "%$search%");}) ->get();` This is what I tried. — user1012181, Dec 31 '14 at 05:10
This is the error that Im getting `Column not found: 1054 Champ 'registrationrequests.course_id unknown in where clause (SQL: select * from `` users` Where registrationrequests`.`course_id` and `registrationrequests`.`registered` = 7 = 1 and (` firstname` LIKE Melwin%%% LIKE or `lastname` Melwin% username` LIKE or`% Melwin%))` — user1012181, Dec 31 '14 at 05:11
Did you include `->leftJoin('registrationrequests', 'users.id', '=', 'registrationrequests.user_id')`? I only was showing an idea of the where, and looking at your query, it does not show the `leftJoin` — Sean, Dec 31 '14 at 05:19
Oops! :) Thanks it worked. You can post it as an answer and I could mark it as completed. you might also wanna take a look at this: http://stackoverflow.com/questions/27716340/how-to-get-the-select-values-from-multiple-rows-in-php — user1012181, Dec 31 '14 at 05:22

score 1 · Accepted Answer · edited May 23 '17 at 10:33

Using http://laravel.com/docs/4.2/queries#advanced-wheres and https://stackoverflow.com/a/18660266/689579 your query would look something like -

$users = DB::table('users')
        ->leftJoin('registrationrequests', 'users.id', '=', 'registrationrequests.user_id')
        ->where('registrationrequests.course_id', $course_id)
        ->where('registrationrequests.registered', 1)
        ->where(function($users) use($search){ 
                $users->where('firstname', 'LIKE', "%$search%")
                      ->orWhere('lastname', 'LIKE', "%$search%")
                      ->orWhere('username', 'LIKE', "%$search%")
        })
        ->get();

Left join not working properly

1 Answers1