2

I have a simple Left query on mysql

SELECT sp. * , p.name, p.date_created
FROM subpages sp
LEFT JOIN pages p ON p.id = sp.page_id

I dont know how to execuite this query from zend framework.

I have a Mapper Page, which access a DbTable page which is extending Zend_Db_Table_Abstract. I read some articles so i suppose statement should look something like this from the mapper

$select = $this -> DbTable() -> select();
$select -> joinleft(..... This is what I dont know how to write....);
$results = $this -> DbTable() -> fetchAll($select);
if($results) { return $result; }

In the article, $select -> from() were used, that where my mind is stuck , why do we need to write $select -> from("subpages") when It will already be defined in the DbTable page.

How to write the join statement properly?

mrN
  • 3,734
  • 15
  • 58
  • 82

2 Answers2

1

From the query you posted , you can use the following

 (You have not mentioned table name for ailas 'mi' so I have taken it as 'your_table_name' )

$select = $this -> DbTable() -> select()->from(array("your_table_name" => 'mi');
$select -> joinleft(array("pages" => 'p'),"p.id = mi.page_id");
$results = $this -> DbTable() -> fetchAll($select);
if($results) { return $result; }

Hope it will work for you mrN.
kbbagal
  • 49
  • 1
  • mi was a mistake, it is sp. How to define indiviudal fields p.name and p.date_created – mrN Sep 27 '11 at 06:40
  • and an errors occurs `Zend_Db_Table_Select_Exception: Select query cannot join with another table` – mrN Sep 27 '11 at 06:44
1

If you want to use the select with multiple tables, you need to get it from the table adapter, which is a Zend_Db object (and you'll need to specify your table in the from() method).

So, you would need something like the following:

$select = $this->DbTable()->getAdapter()->select();
$select->from(array("mi" => "tableName"));
$select->joinLeft(array("p" => "pages"), "p.id = mi.page_id");
(etc)

Hope that helps,

dinopmi
  • 2,683
  • 19
  • 24