Laravel 5.3 inner join not working properly

Question

I'm having two tables as 'jobs' and 'desired_skills'. Table structure is as follows.

jobs table jobs Table

desired_skills table desired_skils table

where desired_skills.job_id refers to jobs.job id

In controller I have (I am getting $id as an argument from the url, and I can confirm the argument grabs the desired value)

$jobs = DB::table('jobs')->where(function ($query) use ($id) {
    $query->Join('desired_skills', 'desired_skills.job_id', '=', 'jobs.job_id')
    ->where('jobs.employer_id', '=', $id);
    ->select('*')
})->get();

when I dump and die $jobs it only returns values from jobs table. but when I run the query

SELECT * FROM jobs INNER JOIN desired_skills ON desired_skills.job_id = jobs.job_id it returns the desired value set.

What am I doing wrong? Any help will be greatly appreciated.

Answer 1

I think it has to do with wrapping your join inside of a where clause. I don't think it's giving you your desired query with that there.

$jobs = DB::table('jobs')
    ->join('desired_skills', 'desired_skills.job_id', '=', 'jobs.job_id')
    ->where('jobs.employer_id', '=', $id)
    ->get();

Answer 2

The query SELECT * FROM jobs INNER JOIN desired_skills ON desired_skills.job_id = jobs.job_id is not the same has what you are trying to do in the function. In this query there is not mention of 'employer_id' in the table 'jobs'.

An alternative would be to use eloquent relationships, as refered in a comment.

You need 3 classes in models:

Employer Job DesiredSkill

Between Employer and Job -> one-to-many relation (an employer can have multiple jobs).

Between DesiredSkill and Job -> one-to-one relation.

I'm not sure what you are trying to get from the join, but i think that if you implement the methods that allow the relationships i believe you solve whatever.

class Job extends Model
{

    public function employer()
    {
        return $this->hasOne('App\Job');
    }
}

class Employer extends Model
{

    public function jobs()
    {
        return $this->hasMany('App\Employer');
    }

    public function desiredSkill()
    {
        return $this->hasOne('App\DesiredSkill');
    }
}

class DesiredSkill extends Model
{

    public function job()
    {
        return $this->hasOne('App\DesiredSkill');
    }
}

Answer 3

Try this:

$jobs = DB::table('jobs')
                ->join('desired_skills', 'desired_skills.job_id', '=', 'jobs.job_id')
                ->select('jobs.*', 'desired_skills.*')
                ->get();