Unable to Get Eloquent to Automatically Create Joins

Question

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.

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 attempts 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');
    }
}

Just dropping this one: area51.stackexchange.com/proposals/46607/laravel — Kriem
– Kriem, Commented Mar 9, 2013 at 12:18

crynobone · Accepted Answer · 2012-06-20 15:59:53Z

10

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');
}

answered Jun 20, 2012 at 15:59

crynobone

1,81412 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Graham Kennedy Over a year ago

Hi @crynobone, thank you for the response. Apologies for the extremely late reply. I switched focus and forgot about this question and specific issue. Implementing your solution didn't quite get the desired result. It created two separate queries, one on the "country" table and another on the "tournament" table. My preferred result would have been a single joined query that matches the one I provided in my original question under the "Query that I Would Like to Replicate in Eloquent" heading. I will come back to this issue again and update this thread if I make headway.

crynobone Over a year ago

Laravel eager loading doesn't use JOIN query, which is different from other ORM approach, being said that it still possible to use Eloquent with join using Fluent Query Builder structure. Tournament::query()->join('countries' ...);

Graham Kennedy Over a year ago

Thanks for the response, crynobone! It's too bad, since I really wanted to use Eloquent to generate queries as much as possible. That said, Fluent Query Builder is more than sufficient.

tharumax Over a year ago

@GrahamKennedy This may be a late reply. But the reason why you are not getting desired result is that you use ->all() instead of ->get() method. Use the ->get() method and you will be amazed

DarkWingDuck Over a year ago

@GrahamKennedy this answer is correct and deserves to be picked as the answer.

sturrockad · Accepted Answer · 2013-12-13 12:39:25Z

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

edited Dec 13, 2013 at 12:39

answered Dec 13, 2013 at 12:30

sturrockad

4,6302 gold badges21 silver badges20 bronze badges

Comments

James Healey · Accepted Answer · 2012-06-19 11:48:56Z

1

Your 'with' clause asks for 'Country', but your code declares it as 'country'.

So, should:

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

Be:

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

Because in your Tournaments Model, you've defined this as:

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

Does making this change solve it?

answered Jun 19, 2012 at 11:48

James Healey

4544 silver badges4 bronze badges

1 Comment

Graham Kennedy Over a year ago

Hi @James Healey, thank you for your input. Unfortunately the casing didn't resolve the issue.

Karl · Accepted Answer · 2013-05-01 08:47:13Z

0

You have to make sure your relationship is declared in your Tournament model:

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

Also, you will have to declare the opposite in your Country model:

public function tournament() {
    return $this->belongs_to('Tournament');
}

At this point, you can access the tournament object associated with the country object like so:

$tournaments = Tournament::with('country')->all();
foreach ($tournaments as $tournament) {
    echo $tournament->country;
}

Let me know if this display each corresponding country to the tournament.

answered May 1, 2013 at 8:47

Karl

5951 gold badge11 silver badges31 bronze badges

Collectives™ on Stack Overflow

Unable to Get Eloquent to Automatically Create Joins

Query that I Would Like to Replicate in Eloquent

Expected Result in PHP

Failed attempts that I've made so far

Failed Attempt #1:

Failed Attempt #2

Other Failed Attempts

My Environment

Relationship Between Tables

Countries Table

Tournaments Table

Countries Model (countries.php)

Tournaments Model (tournaments.php)

4 Answers 4

5 Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Query that I Would Like to Replicate in Eloquent

Expected Result in PHP

Failed attempts that I've made so far

Failed Attempt #1:

Failed Attempt #2

Other Failed Attempts

My Environment

Relationship Between Tables

Countries Table

Tournaments Table

Countries Model (countries.php)

Tournaments Model (tournaments.php)

4 Answers 4

5 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related