CODEWITHSUNDEEP
phplaraveljoineloquent
user0129e021939232
user0129e021939232

Reputation: 6355

laravel join tables column not found

Hi I am building an application and I have a table for example projects and then settings. However they do not have a foreign key with each other and i have other tables such as tasks, clients etc. And these have settings which I am planning to save in the settings table.

The settings table has a column of type, which i will fill with the related model name e.g. project. Anyway when i am fetching a project I also want to fetch a the settings where the type = project. So I've tried to do a table join instead however this has thrown the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'project' in 'on clause' (SQL: select * from `projects` inner join `settings` on `type` = `project`)

The code I've used is as follows:

return \Project::()->join('settings', 'type', '=', 'project')->get();

I see what the problem is, its looking for a column called project however there isn't one. I suppose what i want to do is use eloquent and a query function to join the table and then query the settings table where type = project. does anyone know how i can do this?

update from burak answer

I've tried to put this in my model as so so

settings.php

public function sprint() { 
return \Setting::where('type', '=', 'sprint')->get(); 
}

and sprint.php 

public function settings() {
 return \Setting::where('type', '=', 'sprint')->get(); 
}

however I get this error Call to undefined method

Illuminate\Database\Eloquent\Collection::addEagerConstraints() and I've called it using this method: 

return Sprint::with(['settings'])->get();

what have i done wrong here?

Upvotes: 0

Views: 4420

Answers (2)

Burak Ozdemir
Burak Ozdemir

Reputation: 5332

Because you are not referencing the table names and you are comparing columns. Instead, what you need is a where statement within your join condition to compare with a value.

return DB::table('projects')
    ->join('settings', function ($join) {
        $join->where('settings.type', '=', 'project');
    })->get();

But you should better get settings and projects one by one as all identical settings columns will be added to your projects rows which is not good. 

$data = [];
$data['projects'] = Project::all();
$data['settings'] = Settings::where('type', '=', 'project')->first();
return $data;

Update:

Within your Sprint model 

public function scopeWithType()
{
    return static::join('settings', function ($join) {
        $join->where('settings.type', '=', 'sprint');
    });
}

Then within the controller 

Sprint::withType()->get();

Upvotes: 4

Asim
Asim

Reputation: 452

I have not tried but I think this is what you want. This is how we do subqueries or query joins with where clause.

return \Project::join('settings', function($q) {
    $q->where('type', '=', 'project');
})->get();

your code looks for records matching project field in the table settings and type field in the project table, which is not the case.

Upvotes: 1

Related Questions