Reputation: 6321
I have the following Models and related database tables. Resource Standard Resource_Standard
I've given both the tables a belongsToMany correctly and all the naming conventions are correct. I'm trying to do a join on a query, but I keep getting an error that the field I'm checking against doesn't exist. Since I have several values to check against I'm passing them as an array to the query builder. Here is how I'm building my query:
$resource = Resource::where(function($query) use($values)
{
if($values["grade"] != 0)
$query->where('grade_id', '=', $values["grade"]);
if($values['subject'] != 0)
$query->where('subject_id', '=', $values['subject']);
if($values['types'] != '')
{
if(is_array($values['types']) && count($values['types'])> 0)
$query->whereIn('resourcetype_id', $values['types']);
else
$query->where('resourcetype_id', '=', $values['types']);
}
if($values['standards'] != '')
{
if(is_array($values['standards']) && count($values['standards'])> 0)
{
$query->join('resource_standard', 'resource_standard.resource_id', '=', 'resource.id')
->with('standards')->whereIn('resource_standard.standard_id', $values['standards']);
}
else
{
$query->join('resource_standard', 'resource_standard.resource_id', '=', 'resource.id')
->with('standards')->where('resource_standard.standard_id', '=', $values['standards']);
}
}
})->distinct()->take(30)->get();
When there is a standard_id to check against it gives the following error:
{
"error":{
"type":"Illuminate\\Database\\QueryException",
"message":"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'resource_standard.standard_id' in 'where clause' (SQL: select distinct * from `resources` where (`grade_id` = 2 and `subject_id` = 1 and `resource_standard`.`standard_id` in (4832, 4833)) limit 30)",
"file":"\/Users\/luke\/Dropbox\/DEV\/PHP\/4aplus\/4aplus\/vendor\/laravel\/framework\/src\/Illuminate\/Database\/Connection.php","line":555
}
}
Upvotes: 1
Views: 1009
Reputation: 146269
You can join
using Eloquent
model as well. Just use following code:
$resource = Resource::join('resource_standard', 'resource_standard.resource_id', '=', 'resources.id')
Instead of this:
$resource = DB::table('resources')->join('resource_standard', 'resource_standard.resource_id', '=', 'resources.id')
Don't forget to call ->get()
at last.
Upvotes: 1
Reputation: 6321
I resolved this by using DB::table
rather than the Model. I guess you can't do a join with a model perhaps.
$resource = DB::table('resources')->join('resource_standard', 'resource_standard.resource_id', '=', 'resources.id')
Upvotes: 0