Unable to Get Eloquent to Automatically Create Joins

Question

Apologies in advance if the answer to my question is obvious. I have done my due diligence in researching this topic before I posted it here.

Most of my framework experience comes from using CodeIgniter, so I've never had hands-on experience using ORM. (CI does have some off-the-shelf ORM solutions, but I've never used them.)

I would like to use built-in ORM functionality in Laravel's Eloquent ORM to automatically join the tournaments and countries tables together when running a query, and return the data set that includes tournament data as well as its associated country data.

That is, I want Eloquent to recognize the foreign key relationship automatically so that I can just run a query (e.g. Tournament:: with('Country')->all()) that will return the entire set of tournament and country data.

Please stop me right now if I'm using Eloquent in a way that it was never intended to be used! My confusion may be more about me trying to mash together an untenable solution rather than syntax or coding error.

Query that I Would Like to Replicate in Eloquent

SELECT * FROM tournaments LEFT JOIN countries ON tournaments.country_id = countries.id 

Expected Result in PHP

I expect to receive an array of Tournament objects (in PHP), where a single Tournament object would look like:

• tournaments.id
• tournaments.year
• tournaments.country_id
• tournaments.created_at
• tournaments.updated_at
• countries.id
• countries.code
• countries.name
• countries.url
• countries.created_at
• countries.updated_at

Failed Attemps that I've Made So Far

I ran all of these attempts in a dummy controller method and output the result as a formatted string to the profiler.

Failed Attempt #1:

PHP code in the dummy controller:

$tournaments = Tournament::with('Country')->all(); 

Generates the following query:

SELECT * FROM `tournaments` 

Attempt #1 returns:

An array containing Tournament objects that only include the columns in the tournaments table.

Failed Attempt #2

PHP code in the dummy controller:

$tournaments = Tournament::with('Country')->first(); 

Generates the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'tournament_id' in 'where clause'  SQL: SELECT * FROM `countries` WHERE `tournament_id` IN (?)  Bindings: array ( 0 => '1', ) 

Other Failed Attempts

I've tried various combinations of naming conventions (e.g. columns, tables, etc.) to no avail. I've also tried creating the query in Fluent, which worked fine, but required me to specify the joins which is what I'm trying to avoid.

My Environment

• PHP: 5.3.13
• MySQL: 5.1.53
• Laravel: 3.2.3

Relationship Between Tables

• one-to-one relationship
• A tournament must have a country (there's a foreign key constraint to enforce it)
• A country can belong to many other relations (e.g. a participant, not shown here, has a country of birth)

Countries Table

CREATE TABLE `countries` (                                                                                                                                                                                                                  `id` int(11) NOT NULL AUTO_INCREMENT,                                                                                                                                                                                                        `code` varchar(4) NOT NULL,                                                                                                                                                                                                                  `name` varchar(25) NOT NULL,                                                                                                                                                                                                                 `url` varchar(25) NOT NULL,                                                                                                                                                                                                                  `created_at` datetime NOT NULL,                                                                                                                                                                                                              `updated_at` datetime NOT NULL,                                                                                                                                                                                                             PRIMARY KEY (`id`),                                                                                                                                                                                                                         UNIQUE KEY `countries_code_unique` (`code`),                                                                                                                                                                                                KEY `countries_url_index` (`url`)                                                                                                                                                                                                           ) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=latin1 

Tournaments Table

CREATE TABLE `tournaments` (                                                                                                                                                                                                              `id` int(11) NOT NULL AUTO_INCREMENT,                                                                                                                                                                                                        `year` int(11) NOT NULL,                                                                                                                                                                                                                     `country_id` int(11) NOT NULL,                                                                                                                                                                                                               `created_at` datetime NOT NULL,                                                                                                                                                                                                              `updated_at` datetime NOT NULL,                                                                                                                                                                                                              PRIMARY KEY (`id`),                                                                                                                                                                                                                          UNIQUE KEY `tournaments_year_unique` (`year`),                                                                                                                                                                                              KEY `tournaments_country_id_foreign` (`country_id`),                                                                                                                                                                                       CONSTRAINT `tournaments_country_id_foreign` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON UPDATE CASCADE                                                                                                                   ) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=latin1 

Countries Model (countries.php)

class Country extends Eloquent {     public static $timestamps = true;     public static $table = 'countries'; } 

Tournaments Model (tournaments.php)

class Tournament extends Eloquent {     public static $timestamps = true;      public function country()     {         return $this->has_one('Country');     } } 

Answer 1

Clearly with('Country') or with('country') doesn't do any different due to the fact that he managed to get following error:

Column not found: 1054 Unknown column 'tournament_id' in 'where clause'  SQL: SELECT * FROM `countries` WHERE `tournament_id` IN (?) 

What wrong is how the relationship is defined: A tournament must have a country would be a tournament need to belong to a country, and not has one country. So to solve this change the relationship to

public function country() {     return $this->belongs_to('Country'); } 

Answer 2

According to the Eloquent docs:

Note: All methods available on the query builder are also available when querying Eloquent models.

So with that in mind something like:

DB::table("tournament")->join("countries","tournaments.country_id","=","countries.id")->get(); 

Should be replicable in Eloquent. I personally use the query builder version just now which you may want to use but I will try and test with Eloquent when I get the chance and update this.

UPDATE:

Yep, using Eloquent's query builder methods you can have:

Tournament::join("countries","tournaments.country_id","=","countries.id")->get(); 

This will return a Tournament model that includes the fields of both tables.

HTH