Laravel 4 Query Builder: LEFT JOIN … AND … query

Question

I'm trying to select all results from one table and merge with another table when user ID's match.

I have three tables: runs, users and a run_user pivot table. I want to select all results from 'runs' and additional columns in the pivot table (which are 'completed', 'sticky', 'last_tested' and 'difficulty'), but only pull data from run_user for the current user.

In raw SQL I've managed to do this via a LEFT JOIN with an AND statement:

SELECT
runs.startpoint,
runs.startpostcode,
runs.endpoint,
runs.endpostcode,
run_user.completed,
run_user.sticky,
run_user.last_tested,
run_user.difficulty
FROM runs
LEFT JOIN run_user ON run_user.run_id=runs.id AND run_user.user_id = '2'

Any suggestions how to do this via the Query Builder? I can do the LEFT JOIN in Laravel 4 but can't figure out how to combine this with an AND statement as well.

Any help is appreciated.

Thanks!

Answer 1

As you requested, this is how you would do your query with the query builder:

DB::table('runs')
        ->leftJoin('run_user', function($join)
        {
            $join->on('run_user.run_id', '=', 'runs.id')
            ->on('run_user.user_id', '=', '2');
        })
        ->get();

But that JOIN statement looks a bit weird, you probably want to turn that into a normal WHERE (unless you have a very specific reason to filter directly in the JOIN clause).

DB::table('runs')->join('run_user', 'run_user.run_id', '=', 'runs.id')->where('run_user.user_id', '=', '2')->get();

Docs: http://laravel.com/docs/queries#joins

Answer 2

In case anyone is wondering I got this working using DB::raw on one of the leftJoin parameters:

DB::table('runs')
    ->leftjoin('run_user', 'runs.id','=', 
      DB::raw('run_user.run_id AND run_user.user_id = ' . $user->id))
    ->get();

It's not as 'eloquent' as I'd like it to be but without a working 'andOn' condition this seems to be the only way if you want to add an AND to the LEFT JOIN.

If anyone can suggest a neater way I'd love to know!

Thanks

Answer 3

use this

DB::table('runs')->select('runs.startpoint','runs.startpostcode','runs.endpoint','runs.endpostcode','run_user.completed','run_user.sticky','run_user.last_tested','run_user.difficulty')
    ->leftJoin('run_user', function($join)
    {
        $join->on('run_user.run_id', '=', 'runs.id')
        ->where('run_user.user_id', '2');
    })
    ->get();

Answer 4

In Laravel 4 you can do this using Eloquent query. I assume that you are aware of Eloquent relationships and know to to create models. For your query it should be

RunUser::where('user_id',$id)->leftjoin('runs','runs.id','=','run_user.run_id')->first();

Note RunUser is the model for run_user table.

Answer 5

Provident and user Table Join and get id to all details.

   $return = DB::table(config::get('databaseconstants.TBL_USER'))
            ->leftjoin('inex_pf_details', 'inex_users.id', '=', 'inex_pf_details.pf_user_id')
            ->select('inex_pf_details.*','inex_users.*')
            ->where('inex_pf_details.id', $id)
            ->first();
    return $return;

Answer 6

Here is must work:

DB::table('runs')
        ->leftJoin('run_user', function($join) use ($user_id)
        {
            $join->on('run_user.run_id', '=', 'runs.id')
            ->where('run_user.user_id', '=', $user_id);
        })
        ->get();