laravel 无法 Eloquent 自动创建连接

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/11099570/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-14 07:22:42  来源:igfitidea点击:

Unable to Get Eloquent to Automatically Create Joins

phplaravellaravel-3

提问by Graham Kennedy

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.)

我的大部分框架经验来自使用 CodeIgniter,所以我从未有过使用 ORM 的实践经验。(CI 确实有一些现成的 ORM 解决方案,但我从未使用过它们。)

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.

我想使用 Laravel 的 Eloquent ORM 中的内置 ORM 功能在运行查询时自动将锦标赛和国家表连接在一起,并返回包含锦标赛数据及其相关国家数据的数据集。

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.

也就是说,我希望 Eloquent 能够自动识别外键关系,以便我可以运行一个查询(例如 Tournament:: with('Country')->all()),它将返回整个锦标赛和国家数据集。

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.

如果我以一种从未打算使用的方式使用 Eloquent,请立即阻止我!我的困惑可能更多是因为我试图将一个站不住脚的解决方案混在一起,而不是语法或编码错误。

Query that I Would Like to Replicate in Eloquent

我想在 Eloquent 中复制的查询

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

Expected Result in PHP

PHP 中的预期结果

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

我希望收到一组锦标赛对象(在 PHP 中),其中单个锦标赛对象如下所示:

  • 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
  • 锦标赛.id
  • 比赛年
  • 锦标赛.country_id
  • 锦标赛.created_at
  • 锦标赛.updated_at
  • 国家/地区编号
  • 国家代码
  • 国家名称
  • 国家/地区网址
  • country.created_at
  • country.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:

失败的尝试 #1:

PHP code in the dummy controller:

虚拟控制器中的 PHP 代码:

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

Generates the following query:

生成以下查询:

SELECT * FROM `tournaments`

Attempt #1 returns:

尝试 #1 返回:

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

一个包含 Tournament 对象的数组,该对象仅包含锦标赛表中的列。

Failed Attempt #2

失败的尝试 #2

PHP code in the dummy controller:

虚拟控制器中的 PHP 代码:

$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.

我尝试了各种命名约定的组合(例如列、表等),但都无济于事。我还尝试在 Fluent 中创建查询,效果很好,但要求我指定我试图避免的连接。

My Environment

我的环境

  • PHP: 5.3.13
  • MySQL: 5.1.53
  • Laravel: 3.2.3
  • 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)

国家模型 (countries.php)

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

Tournaments Model (tournaments.php)

锦标赛模型 (tournaments.php)

class Tournament extends Eloquent {
    public static $timestamps = true;

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

采纳答案by crynobone

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

由于他设法得到以下错误,显然with('Country')with('country')没有做任何不同的事情:

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

回答by sturrockad

According to the Eloquent docs:

根据雄辩的文档:

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

注意:在查询 Eloquent 模型时,查询构建器上可用的所有方法也可用。

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.

应该可以在 Eloquent 中复制。我个人现在使用查询构建器版本,您可能想使用它,但当我有机会并更新它时,我会尝试使用 Eloquent 进行测试。

UPDATE:

更新:

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

是的,使用 Eloquent 的查询构建器方法,您可以:

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

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

这将返回一个包含两个表字段的锦标赛模型。

HTH

HTH

回答by James Healey

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

您的“with”子句要求“Country”,但您的代码将其声明为“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?

进行此更改是否可以解决问题?

回答by Karl

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:

此外,您必须在 Country 模型中声明相反的内容:

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.

让我知道这是否显示了锦标赛的每个相应国家/地区。