Laravel 4 Eloquent returns wrong ID

Question

I have 3 tables in my database:

1. Campaigns
2. Users
3. Companies

One company may have some users. One user may have some campaigns. A user (with admin rights) can do some actions with any campaign that belongs to his company. So, I want to check whether he's doing these actions with his campaign or not (in the last case I return something like "access denied").

My condition

Campaign::join('users', 'users.id', '=', 'campaigns.user_id')
        ->where('users.company_id', '=', Auth::user()->company->id)
        ->where('campaigns.id', '=', Input::get('id'))
        ->first();

So if I got unique campaign - it's ok, if I got null - something's wrong and I send "access denied" to user as he's dealing with other company campaign.

This code produces next query:

array(3) {
  ["query"]=>
  string(148) "select * from `campaigns` inner join `users` on `users`.`id` = `campaigns`.`user_id` where `users`.`company_id` = ? and `campaigns`.`id` = ? limit 1"
  ["bindings"]=>
  array(2) {
    [0]=>
    string(1) "2"
    [1]=>
    string(2) "13"
  }
  ["time"]=>
  float(0.42)
}

Using phpmyadmin I tried the same query and got a campaign with ID = 13. But when I was debugging my application I found out that

dd($campaign->id);

returns 8 instead. 8 also equals campaigns.user_id (The record has both campaigns.id and campaigns.user_id = 8).

I can't figure out why it's happening. Even if something wrong with my SQL query (what I doubt as phpmyadmin returned right results), I got where condition campaigns.id = Input::get('id'), where Input::get('id') = 13. Why id is being changed?

Of course I can do this security check in two steps, like first get the campaign, then check $campaign->user->company->id = Auth::user()->company->id but just wondering ...

Answer 1

If you run this query in phpMyAdmin you should probably be able to see that the result contains multiple columns by the name "id". When PHP parses the query result to an associative array or object, keys must be unique! If keys are colliding, the last column will be used!

Example:

SQL result:

id    user_id    name    id    name    company_id
1     2          Camp1   2     Pelle   1

PHP result:

array (size=1)
  0 => 
    object(stdClass)[131]
      public 'id' => string '2' (length=1)
      public 'user_id' => string '2' (length=1)
      public 'name' => string 'Pelle' (length=5)
      public 'company_id' => string '1' (length=1)

To solve this you could add a select clause to only select the campaign columns:

Campaign::select('campaigns.*')
    ->join('users', 'users.id', '=', 'campaigns.user_id')
    ->where('users.company_id', '=', Auth::user()->company->id)
    ->where('campaigns.id', '=', Input::get('id'))
    ->first();