如何使用HABTM关系查询CakePHP中的数据？ [英] How do I query data in CakePHP using HABTM relationships?

问题描述

我正在使用CakePHP 1.2应用程序。我有一个模型用户通过连接表与其他表通过一些HABTM关系定义。

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.

我现在的任务是基于存储的数据查找用户信息在这些HABTM表中的一个中。不幸的是，当查询执行时，我的条件被拒绝并显示有关缺少表的错误。检查后，CakePHP似乎不包括select语句中的任何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关系如下：

My User HABTM relationship is as follows:

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

错误是：

SQL错误：1054：未知列
'where子句'中的'School.name'

SQL Error: 1054: Unknown column 'School.name' in 'where clause'

最后，它试图执行的查询

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

推荐答案

将您的调试级别最多为2，并查看SQL输出。找到您的代码生成的查询，您会注意到有几个。 CakePHP中的ORM层在第一个查询中不连接HABTM相关表。它从第一次选择获得结果，然后单独获取每个项目的HABTM数据。由于连接表不在第一个查询中，因此您的条件（用于连接的表）会导致您看到的错误。

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.

有一节介绍 HABTM协会并根据HABTM表中的条件获取满足您需求的数据。

The cook book has a section on HABTM associations and fetching data based on conditions in the HABTM table that will fit your requirements.

这篇关于如何使用HABTM关系查询CakePHP中的数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！