php 如何使用 HABTM 关系在 CakePHP 中查询数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/327052/
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
How do I query data in CakePHP using HABTM relationships?
提问by
I'm working on a CakePHP 1.2 application. I have a model "User" defined with a few HABTM relationships with other tables through a join table.
我正在开发一个 CakePHP 1.2 应用程序。我有一个模型“用户”，通过连接表定义了与其他表的一些 HABTM 关系。
I'm now tasked with finding User information based on the data stored in one of these HABTM tables. Unfortunately, when the query executes, my condition is rejected with an error about a missing table. Upon inspection it seems that CakePHP is not including any of the HABTM tables in the select statement.
我现在的任务是根据存储在这些 HABTM 表之一中的数据查找用户信息。不幸的是，当查询执行时，我的条件被拒绝，并显示有关缺少表的错误。经过检查，CakePHP 似乎没有在 select 语句中包含任何 HABTM 表。
My User HABTM relationship is as follows:
我的用户HABTM关系如下：
var $hasAndBelongsToMany = array(
'Course' => array(
'className' => 'Course',
'joinTable' => 'course_members',
'foreignKey' => 'user_id',
'associationForeignKey' => 'course_id',
'conditions' => '',
'order' => '',
'limit' => '',
'uniq' => false,
'finderQuery' => '',
'deleteQuery' => '',
'insertQuery' => ''
),
'School' => array(
'className' => 'School',
'joinTable' => 'school_members',
'foreignKey' => 'user_id',
'associationForeignKey' => 'school_id',
'conditions' => '',
'order' => '',
'limit' => '',
'uniq' => false,
'finderQuery' => '',
'deleteQuery' => '',
'insertQuery' => ''
),
'Team' => array(
'className' => 'Team',
'joinTable' => 'team_members',
'foreignKey' => 'user_id',
'associationForeignKey' => 'team_id',
'conditions' => '',
'order' => '',
'limit' => '',
'uniq' => false,
'finderQuery' => '',
'deleteQuery' => '',
'insertQuery' => ''
)
);
The error is:
错误是：
SQL Error: 1054: Unknown column 'School.name' in 'where clause'
SQL 错误：1054：“where 子句”中的未知列“School.name”
And finally, the query it is trying to execute
最后，它试图执行的查询
SELECT
`User`.`id`, `User`.`username`, `User`.`password`, `User`.`firstName`, `User`.`lastName`, `User`.`email
`, `User`.`phone`, `User`.`streetAddress`, `User`.`city`, `User`.`province`, `User`.`country`, `User
`.`postal`, `User`.`userlevel`, `User`.`modified`, `User`.`created`, `User`.`deleted`, `User`.`deleted_date
` FROM `users` AS `User` WHERE `User`.`id` = 6 AND `School`.`name` LIKE '%Test%' LIMIT 1
回答by neilcrookes
Turn your debug level up to 2 and look at the SQL output. Find the query that your code is generating and you'll notice there are several. The ORM layer in CakePHP doesn't join HABTM related tables in the first query. It gets the results from the first select, then separately fetches the HABTM data for each item. Because the join table is not in the first query, your condition, which is intended for use on a joined table, results in the error you are seeing.
将调试级别调高至 2 并查看 SQL 输出。找到您的代码正在生成的查询，您会注意到有几个。CakePHP 中的 ORM 层不会在第一个查询中加入 HABTM 相关表。它从第一次选择中获取结果，然后分别获取每个项目的 HABTM 数据。因为连接表不在第一个查询中，所以您的条件（旨在用于连接表）会导致您看到的错误。
The cook book has a section on HABTM associationsand fetching data based on conditions in the HABTM table that will fit your requirements.
回答by Asif Zardari
From the Cookbook: http://book.cakephp.org/view/83/hasAndBelongsToMany-HABTM
来自食谱：http: //book.cakephp.org/view/83/hasAndBelongsToMany-HABTM
One option is to search the Tag model (instead of Recipe), which will also give us all of the associated Recipes.
一种选择是搜索标签模型（而不是食谱），这也将为我们提供所有相关的食谱。
$this->Recipe->Tag->find('all', array(
'conditions' => array('Tag.name' => 'Dessert')));
We could also use the join table model (which CakePHP provides for us), to search for a given ID.
我们还可以使用连接表模型（CakePHP 为我们提供的模型）来搜索给定的 ID。
$this->Recipe->bindModel(array('hasOne' => array('RecipesTag')));
$this->Recipe->find('all', array(
'fields' => array('Recipe.*'),
'conditions' => array('RecipesTag.tag_id' => 124) // id of Dessert
));
It's also possible to create an exotic association for the purpose of creating as many joins as necessary to allow filtering, for example:
也可以创建一个奇特的关联，以便根据需要创建尽可能多的连接以允许过滤，例如：
$this->Recipe->bindModel(array('hasOne' => array('RecipesTag',
'FilterTag' => array(
'className' => 'Tag',
'foreignKey' => false,
'conditions' => array('FilterTag.id = RecipesTag.tag_id')
))));
$this->Recipe->find('all', array(
'fields' => array('Recipe.*'),
'conditions' => array('FilterTag.name' => 'Dessert')
));
回答by m3nt0r
Your table should be called "schools_users" and not "school_members" because it's many-to-many, thus using the plural form in the table name on both sides is appropiate.
你的表应该被称为“schools_users”而不是“school_members”，因为它是多对多的，因此在表名两边使用复数形式是合适的。
You also set the Model ClassName "School" as Alias for the HABTM. You should change that to something more generic like "Schools" as in "User is in and has many SchoolS" to avoid conflicts.
您还将模型类名称“学校”设置为 HABTM 的别名。您应该将其更改为更通用的内容，例如“用户所在且拥有许多学校”中的“学校”以避免冲突。
And another hint: Try to find the user "via" the School Model rather than the User Model.
另一个提示：尝试“通过”学校模型而不是用户模型找到用户。
$this->User->Schools->find()
$this->User->Schools->find()
Hope this helps.
希望这可以帮助。
回答by J Cooper
FWIW, your join tables do appear to be "oddly named" insofar as they don't follow the convention described here:
FWIW，您的连接表似乎确实是“奇怪的命名”，因为它们不遵循此处描述的约定：
http://book.cakephp.org/view/83/hasAndBelongsToMany-HABTM
http://book.cakephp.org/view/83/hasAndBelongsToMany-HABTM
In any case, good luck, I remember HABTM being a butt-pain in CakePHP.
无论如何，祝你好运，我记得 HABTM 是 CakePHP 中的一个痛点。
