如何在ZF表界面中进行联接查询? [英] How to do a joined query in the ZF tables interface?

问题描述

我有数据库，表如下:

替代文字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 Relationships接口在与从其声明的引用映射中创建联接查询有关的方面并不多.社区为复杂查询提供的解决方案是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.

但是，在您的情况下，我将跳过表接口和select接口，而直接使用Db适配器直接执行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.

这篇关于如何在ZF表界面中进行联接查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！