CODEWITHSUNDEEP
phpmysqllaravellaravel-4eloquent
Ian Gustafson
Ian Gustafson

Reputation: 172

Id Column Overwritten by Join

Using Laravel 4.2 & MySQL.

I have an applications table with an id and a fit_score_id column, and a fit_scores table with an id column. It's a basic "belongs to" relationship.

The following code:

$query = Application::join('fit_scores', 'applications.fit_score_id', '=', 'fit_scores.id');
$collection = $query->get();

...produces a collection of Application models with the id property set to the value of the fit_score_id. What am I doing to cause this?

I should note that it is necessary to do this join rather than simply using eloquent relations, because I'm going to want to order the results by a column on the fit_scores table. I don't believe this is possible using Eloquent without an explicit join.

Upvotes: 3

Views: 2971

Answers (3)

mytuny
mytuny

Reputation: 875

The best way to solve this is by chaining the join method to a select method as following:

Application::select('*', \DB::raw("applications.id as appid"))
             ->join('fit_scores', 'applications.fit_score_id', '=', 'fit_scores.id')
             ->get();

Explained: The solution simply suggest that instead of thinking to prevent the behavior of overwriting the first id with the joined id, we can hook into the primary selection query (before joining) and change the label of the id column into something else (in this case 'appid'). By doing so, we end up with both the id of the parent table being labeled 'appid' and the id of the joined table being labeled 'id' again while they lives together on the final result.

Upvotes: 3

jhmilan
jhmilan

Reputation: 508

It depends on what you need but probably you can achieve it using eager loading. In case you need to mix joins and eager loading check this out. http://www.jmilan.net/posts/eager-loading-joins-in-laravel

Upvotes: 0

Ian Gustafson
Ian Gustafson

Reputation: 172

I was able to find a possible solution using this answer:

Laravel 4 - JOIN - Same column name

Basically, since Laravel does not automatically prefix column names with table_name. for joined tables, we need to manually work around it by aliasing any conflicting column names in joins. Adding this select statement to my query did it:

->select(DB::raw("applications.*, fit_scores.*, applications.id as id"))

Upvotes: 0

Related Questions