如何在 ZF 表界面中进行联接查询?

2021-12-29 00:00:00 mysql zend-framework zend-db-table

我有数据库，我的表是这样的:

I have the db and my tables look like this:

替代文字 http://img15.imageshack.us/img15/2568/stackdijag.png

我想要做的是获取制造商名称列以 A 开头的所有型号.这意味着查询的简单部分应该像 $manufacturers->fetchAll("name LIKE '$letter%'");

What I want to do is to get all models where manufacturers name column starts with A. Which means that that simple part of query should be like $manufacturers->fetchAll("name LIKE '$letter%'");

我正试图通过 ZF 关系来实现这一点，但它不会成功，因此欢迎任何形式的帮助......

I am trying to accomplish this with ZF relations but it ain't going, so any kind of help is welcome...

推荐答案

$models = new Models();
$select = $models->select(Zend_Db_Table::SELECT_WITH_FROM_PART);
$select->setIntegrityCheck(false)
       ->join(array("a"=>"manufacturers"), 'models.manufacturer_id = a.id',
         array("man_name"=>"name", "man_description"=>"description"))
       ->where("a.name LIKE 'A%'");
$rowset = $models->fetchAll($select);

不幸的是，Zend_Db_Table 关系接口在从其声明的参考映射创建连接查询方面没有太多智能.复杂查询的社区贡献解决方案是 Zend_Db_Table_Select 查询工厂.

Unfortunately the Zend_Db_Table relationships interface doesn't have much intelligence in it related to creating joined queries from its declared reference map. The community-contributed solution for complex queries is the Zend_Db_Table_Select query factory.

请注意，您必须为制造商的名称和描述提供列别名，否则这些列将抑制行数据关联数组中的模型名称和描述.您应该清楚地命名列以避免这种情况.

Note you have to give column aliases for manufacturer's name and description, or else these columns will suppress the model's name and description in the associative array for the row data. You should name columns distinctly to avoid this.

但在你的情况下，我会跳过表接口和选择接口，直接使用数据库适配器直接执行 SQL 查询:

But in your case, I'd skip the table interface and the select interface, and simply execute an SQL query directly using the Db adapter:

$data = $db->fetchAll("
  SELECT m.*, a.name AS man_name, a.description AS man_description
  FROM Models m JOIN Manufacturers a ON m.manufacturer_id = a.id
  WHERE a.name LIKE 'A%'");

您将把数据作为一个简单的关联数组数组返回，而不是作为 Zend_Db_Table_Rowset.但由于联接的行集无论如何都不可写，因此您并没有做出太多牺牲.

You'll get the data back as a simple array of associative arrays, not as a Zend_Db_Table_Rowset. But since a joined rowset isn't writeable anyway, you haven't sacrificed much.

相关文章