CODEWITHSUNDEEP
phpmysqlsqllaravel
lggg3
lggg3

Reputation: 557

Laravel LeftJoin where

I have some problems with my eloquent model, where I try to join two different tables in one

Now I have two tables like.

First tables name "company_saved_cv"

|id|company_id|worker_id |
--------------------------
|1|       2    |  61     |
|3|       3    |  66     |
|4|       2    |  69     |
--------------------------

Second tables name "workers"

|id|location|name_and_surname|
------------------------------
|61|London  | John John      |
|62|London  | John John      |
|66|London  | John John      |
|67|London  | John John      |
|68|London  | John john      |

And I whana get table like this.

|id|location|name_and_surname|worker_id|
---------------------------------------|
|61|London  | John John      |  61     |
|62|London  | John John      | NULL    |
|66|London  | John John      | 66      |
|67|London  | John John      | NULL    |
|68|London  | John john      | NULL    |

where is my model function

public static  function CvSearch($interested_field, $location)
{

    $match = "MATCH( `interested_fields`) AGAINST (?)";

    $query = Worker::select('workers.name_and_surname', 'workers.id', 'workers.location','company_saved_cv.worker_id')
        ->leftJoin('company_saved_cv', function($leftJoin)
        {
            $leftJoin->on('company_saved_cv.worker_id', '=', 'workers.id')
                ->where('company_saved_cv.company_id', '=', Session::get('company_id') );


        })
        ->where('workers.location', '=', $location)
        ->whereRaw($match, array($interested_field))
        ->orderBy('workers.created_at')
        ->paginate(10);
    return $query;
}

If I comment this line:->where('company_saved_cv.company_id', '=', Session::get('company_id') ); It is working, but I need get only rows from workers table where company_id = 2 (in my example);

Session::get('company_id') = 2

I will check it by logging it in laravel.log file.

I fink it is problem with where('company_saved_cv.company_id', '=', Session::get('company_id') ); request, but I can't figure it out, maybe some one can help me?

Upvotes: 8

Views: 58527

Answers (4)

Ilya Degtyarenko
Ilya Degtyarenko

Reputation: 1569

More or less like this:

$directoryTypeId = 1;

Node::query()
    ->leftJoin('directories', 'directories.id', '=', 'nodes.directory_id')
    ->leftJoin('directory_types', function ($leftJoin) use ($directoryTypeId) {
        $leftJoin
            ->on('directory_types.id', '=', DB::raw($directoryTypeId))
            ->on('directory_types.COLUMN_1', '=', 'directories.COLUMN_2');
    })
    ->select([
        'nodes.id as node_id',
        'directories.name',
        'directory_types.id as type_id',
    ]);

Upvotes: 3

Donny van V
Donny van V

Reputation: 981

You can easily use the Model for it. The last table, you can remove the location because you can get it with and from the model. Create easily a many-2-many relationship or something you want. Check the docs: http://laravel.com/docs/4.2/eloquent#relationships

Upvotes: 0

Abhishek Gupta
Abhishek Gupta

Reputation: 4166

Well when you use LEFT JOIN with where it acts as an INNER JOIN

Use LEFT JOIN and WHERE like

SELECT workers.name_and_surname, workers.id, workers.location,company_saved_cv.worker_id
FROM workers LEFT JOIN company_saved_cv
ON company_saved_cv.worker_id= workers.id
WHERE company_saved_cv.company_id = 2

OUTPUT

|id|location|name_and_surname|worker_id|
---------------------------------------|
|61|London  | John John      |  61     |
|66|London  | John John      |  66     |

Use LEFT JOIN and AND like 

SELECT workers.name_and_surname, workers.id, workers.location,company_saved_cv.worker_id
FROM workers LEFT JOIN company_saved_cv
ON company_saved_cv.worker_id= workers.id
AND company_saved_cv.company_id = 2

OUTPUT

|id|location|name_and_surname|worker_id|
---------------------------------------|
|61|London  | John John      |  61     |
|62|London  | John John      | NULL    |
|66|London  | John John      | 66      |
|67|London  | John John      | NULL    |
|68|London  | John john      | NULL    |

New query 

$query = Worker::select('workers.name_and_surname', 'workers.id', 'workers.location','company_saved_cv.worker_id')
        ->leftJoin('company_saved_cv', function($leftJoin)
        {
            $leftJoin->on('company_saved_cv.worker_id', '=', 'workers.id')

                ->on('company_saved_cv.company_id', '=', Session::get('company_id') );


        })

Upvotes: 3

lggg3
lggg3

Reputation: 557

I solve this problem with this code

 $query = Worker::select('workers.name_and_surname', 'workers.id', 'workers.location','company_saved_cv.worker_id')
        ->leftJoin('company_saved_cv', function($leftJoin)use($company_id)
        {
            $leftJoin->on('workers.id', '=', 'company_saved_cv.worker_id');
            $leftJoin->on(DB::raw('company_saved_cv.company_id'), DB::raw('='),DB::raw("'".$company_id."'"));


        })

Upvotes: 13

Related Questions