CODEWITHSUNDEEP
phpmysqllaravellaravel-4laravel-5
Hashibul hasan
Hashibul hasan

Reputation: 187

Laravel Join query Not work properly

I am trying to get a list of users with a join query, but its not working properly. I would like to retrieve all the users except the current user_id. I pass in a user_id to exclude but the results return all users and it should have a list of all users except the current user_id.

It also returns a row id value from the preferences table and not the user table.

Here is my query.

$query = DB::table('users')
           ->join('preferences', function($join) use($results) {
              $join->on('users.id', '=', 'preferences.UserId') 
                ->where('preferences.ClassIdFrom', '>=', $results->ClassIdFrom)
                ->where('preferences.ClassIdTo', '<=', $results->ClassIdTo);
         })
           ->where('users.id', '!=',$UserId)
           ->whereNotIn('users.id', $mid_query)
           ->whereNotIn('users.id', $last_query);

$result = $query->get();

If I run the query with a user_id of 22 the result should not include this user so this does not work.

->where('users.id', '!=',$UserId)

Again, the result returns all of the columns from the user table correctly except the that the id column value is coming from the preferences table.

i think after join 

                    ->where('users.id', '!=',$UserId)
                    ->whereNotIn('users.id', $mid_query)
                    ->whereNotIn('users.id', $last_query);

both three not work

Upvotes: 2

Views: 2048

Answers (1)

patricus
patricus

Reputation: 62228

If you don't specify the fields to select, you will perform a select *. When you do this, if multiple tables have the same field name (e.g. id), the ultimate value retrieved will be from the last table with the field. In this case, the preferences table comes after the users table, so any fields with the same name in both tables will end up with the data from the preferences table.

The quick fix would be to specify your select statement:

$query = DB::table('users')
    ->join('preferences', function($join) use($results) {
        $join->on('users.id', '=', 'preferences.UserId') 
            ->where('preferences.ClassIdFrom', '>=', $results->ClassIdFrom)
            ->where('preferences.ClassIdTo', '<=', $results->ClassIdTo);
    })
    ->where('users.id', '!=',$UserId)
    ->whereNotIn('users.id', $mid_query)
    ->whereNotIn('users.id', $last_query)
    ->select('users.*');

Extra

If you were to setup your Eloquent models and relationships, you could also perform this query with the Eloquent ORM.

$users = User::whereHas('preferences', function($query) use ($results) {
    $query->where('ClassIdFrom', '>=', $results->ClassIdFrom)
        ->where('ClassIdTo', '<=', $results->ClassIdTo);
})
    ->where('id', '!=',$UserId)
    ->whereNotIn('id', $mid_query)
    ->whereNotIn('id', $last_query)
    ->get();

Additionally, you could clean up your where's a little bit:

$ids = array_merge(array($UserId), $mid_query, $last_query);
$users = User::whereHas('preferences', function($query) use ($results) {
    $query->where('ClassIdFrom', '>=', $results->ClassIdFrom)
        ->where('ClassIdTo', '<=', $results->ClassIdTo);
})
    ->whereNotIn('id', $ids)
    ->get();

Upvotes: 3

Related Questions