CODEWITHSUNDEEP
mysqljoinlaravellaravel-4eloquent
Jhorra
Jhorra

Reputation: 6321

Join with Laravel 4 using Eloquent

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

Answers (2)

The Alpha
The Alpha

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

Jhorra
Jhorra

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

Related Questions