Eloquent ORM not fetching data using relationship laravel

Question

I am new to laravel and using Eloquent ORM. Now the issue is I am trying to get records using relationships with with() function. Now this issue is that eloquent generates and applies the right query but does not returns any result. But if I test the same generated query on mysql than it works fine.

Following are the two tables that are involved in this:

Properties: id , name , locality_id

Localities: id, name , type , adjoining

Now the relationship between above mentioned tables is one to many relationship.

Property Model:

protected $table = 'properties';
protected $guarded = array('id');

public function localityAreaAndCity() {

    return $this->belongsTo('Locality','locality_id')
                ->leftjoin('localities as ls', function($join)
                {
                $join->on('localities.id', '=', 'ls.adjoining')
                    ->where('localities.type', '=','area');
                });

                ->select(array('localities.name as localityPrimaryName',
                          'localities.type as localityPrimaryType',
                           'ls.name as localitySecondaryName',
                          'ls.type as localitySecondaryType'));
}

Locality Model:

public $timestamps = false;
protected $table = 'localities';
protected $guarded = array('id');
public function properties()
{
    return $this->hasMany('Property');
}

Eloquent Query:

$properties = Property::with('localityAreaAndCity')->get();

DB::getQueryLog() Result:

select `localities`.`name` as `localityPrimaryName`, `localities`.`type` as `localityPrimaryType`, `ls`.`name` as `localitySecondaryName`, `ls`.`type` as `localitySecondaryType` from `localities` left join `localities` as `ls` on `localities`.`id` = `ls`.`adjoining` and `localities`.`type` = ? where `localities`.`id` in (?, ?, ?, ?, ?)

Know if I use the above mentioned query in mysql then it returns data but using with Eloquent ORM it returns NULL. Please Help..

please put this into your code and show me the result echo Property::with('localityAreaAndCity')->count(); — mininoz

Muhammad Raza Muhammad Raza · Accepted Answer · 2015-04-02T14:30:48

Just added locality_id to the select and it worked. Actually eloquent works like you have 2 arrays with related items and you want to match them - if there's no foreign key in one of them, that points to the primary key in the other one, then you can't do that. Eloquent does exactly the same.

You must always select both keys of the relation (most likely PK on one model and FK on the other one).

Eloquent ORM not fetching data using relationship laravel

1 Answers