Laravel 3 Many to Many relationship not binding query

Question

I'm working through the documentation of many-to-many relationships and have run into a brick wall of sorts. I'm trying to use an existing database schema and wrap Laravel/Eloquent around it, which requires teaching Eloquent about the different foreign key fields. Below are my two models as well as the result of DB::profile() which displays the output of the query. Notice that it successfully builds the query however the UserID is not bound to it.

In my code, I call the model as User::find(1)->roles. I'd expect to get an array of roles for that particular user, however it is empty.

User Model

class User extends Eloquent
{
   public static $table = 'GDN_User';
   public static $key = 'UserID';
   public function roles()
   {
      return $this->has_many_and_belongs_to('role', 'GDN_UserRole', 'UserID', 'RoleID');
   }
}

Role Model

class Role extends Eloquent
{
   public static $table = 'GDN_Role';
   public static $key = 'RoleID';
   public function users()
   {
      return $this->has_many_and_belongs_to('user', 'GDN_UserRole', 'RoleID', 'UserID');
   }
}

DB::profile() output

array(2) {
  [0]=>
  array(3) {
    ["sql"]=>
    string(51) "SELECT * FROM `GDN_User` WHERE `UserID` = ? LIMIT 1"
    ["bindings"]=>
    array(1) {
      [0]=>
      int(1)
    }
    ["time"]=>
    string(4) "4.04"
  }
  [1]=>
  array(3) {
    ["sql"]=>
    string(367) "SELECT `GDN_Role`.*, `GDN_UserRole`.`id` AS `pivot_id`, `GDN_UserRole`.`created_at` AS `pivot_created_at`, `GDN_UserRole`.`updated_at` AS `pivot_updated_at`, `GDN_UserRole`.`UserID` AS `pivot_UserID`, `GDN_UserRole`.`RoleID` AS `pivot_RoleID` FROM `GDN_Role` INNER JOIN `GDN_UserRole` ON `GDN_Role`.`RoleID` = `GDN_UserRole`.`RoleID` WHERE `GDN_UserRole`.`UserID` = ?"
    ["bindings"]=>
    array(1) {
      [0]=>
      NULL
    }
    ["time"]=>
    string(4) "0.10"
  }
}

The resulting query is good. If I run that directly on my server, it returns two groups for UserID = 1. The binding is not happening and I'm not certain if this is a bug or if I'm misusing Eloquent.

Thanks!

Answer 1

Laravel often lowercases keys. I would suggest lowercasing all instances of UserID and RoleID in your models and working from there.

If you're designing databases for use with Laravel aim to use lowercase table and column names (I appreciate if you're using an existing database you may have no option).

-- PhillSparks