How do I execute a joined query in the ZF table interface?
I have a db and my tables look like this:
alt text http://img15.imageshack.us/img15/2568/stackdijag.png
What I want to do is get all models where the manufacturer column name starts with A. This means that this simple part of the query should look like $ manufacturer-> fetchAll ("name LIKE '$ letter%'");
I am trying to accomplish this using ZF relationships, but it doesn't work, so any help is appreciated ...
source to share
$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);
Unfortunately, the relationship interface Zend_Db_Table
doesn't have a lot of intelligence in it related to making related queries from its declared reference map. The solution to complex queries for the community is a Zend_Db_Table_Select
factory query .
Note that you must provide column aliases for the manufacturer name and description, otherwise these columns will suppress the model name and description in the associative array for the row data. You must be clear about the columns to avoid this.
But in your case, I would skip the table interface and select interface and just execute the 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%'");
You will get the data back as a simple array of associative arrays, not as Zend_Db_Table_Rowset
. But since the concatenated rowset cannot be written anyway, you haven't sacrificed much.
source to share