Inner Join-Laravel

Question

I have three tables in my database. User, Employer and Jobs.

Some Users are Employers who have posted some jobs.

I am trying to display jobs by users. My code: User Model

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

Routes:

Route::get('/find_user_jobs',function(){
    $user=User::find(1);
    foreach($user->jobs as $job){
        echo $job->created_at."<br>";
    }
});

But I get this error

 Column not found: 1054 Unknown column 'jobs.user_id' in 'field list' (SQL: select `employers`.*, `jobs`.`user_id` from `employers` inner join `jobs` on `jobs`.`id` = `employers`.`job_id` where `jobs`.`user_id` = 1)

I get that its trying to find user_id in jobs but here is what I want it to do

My desire for the program When I give the user id, go to employers table search for user_id, if it exists ,go to jobs table and search for the employers_id and return all the jobs with the employer_id.

User Migration

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateUsersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->string('password');
            $table->integer('employer_id');
            $table->rememberToken();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('users');
    }
}

Job migration

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateJobsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('jobs', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('employer_id');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('jobs');
    }
}

Employer Migration

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateEmployersTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('employers', function (Blueprint $table) {
            $table->increments('id');
            $table->string('company_name');
            $table->integer('user_id');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('employers');
    }
}

Answer 1

Because Employee are User who have Jobs you can create a model App\Employee which extends the App\User Model

class Job {

    public function employee()
    {
        return $this->belongsTo(App\Employee::class,'user_id');
    }
}

and Create an Employee class like this

Here in the Employee Model I set the $table property to users, when we make some query that query will have the target table set to users instead of the employees table which would be the default behavior of Eloquent.

class Employee extends User
{
    protected $table = "users";

    public function jobs()
    {
        return $this->hasMany(Job::class, 'user_id');
    }
}

You can use directly the Employee Model and get jobs

And here are the corresponding migration

the create_user_table

class CreateUsersTable extends Migration
{

    public function up()
    {
        Schema::create('users', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('email')->unique();
            $table->string('password');
            $table->string('company_name')->nullable();
            $table->rememberToken();
            $table->timestamps();
        });
    }

    public function down()
    {
        Schema::drop('users');
    }
}

The create_job_table

class CreateJobsTable extends Migration
{

    public function up()
    {
        Schema::create('jobs', function (Blueprint $table) {
            $table->increments('id');
            $table->integer("user_id")->unsigned();
            $table->foreign('user_id')->references('id')->on('users');
            $table->timestamps();
        });
    }

    public function down()
    {
        Schema::drop('jobs');
    }
}

Answer 2

The relationship arguments are in the wrong order:

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