Translation request to use Zend_Db_Select

Question

I am having trouble translating this request to use ZF Zend_Db_Select :

 SELECT b.id, b.title, b.description FROM memberships AS m JOIN blogs AS b ON b.id = m.blog_id WHERE m.user_id = ? ORDER BY m.created LIMIT 0, 30 

(this query works and returns the results)

Memberships - a table of links between blogs and users . This is a simple matter | id | blog_id | user_id | | id | blog_id | user_id | .

Here is what I still have:

 // $table = Zend_Db_Table instance, $id = a user id $select = $table->select() ->from(array('m' => 'memberships'), array('b.id', 'b.title', 'b.description')) ->join(array('b' => 'blogs'), 'b.id = m.blog_id') ->where('m.user_id = ?', (int) $id) ->order('m.created DESC') ->limit(0, 30); 

This is (weird (to me)) the error I get:

#0: Select query cannot join with another table

It happened on line 211 D:\...\library\Zend\Db\Table\Select.php .

Thank you for your help.

Answer 1

When retrieving a table from your object, the statement will be limited to this table, I think. Zend_Db_Table::select() methods return a Zend_Db_Table_Select object, which is a subclass of Zend_Db_Select and imposes this restriction. Try instead:

 $db = Zend_Db::factory( ...options... ); $select = new Zend_Db_Select($adapter); $select->from( 'my_table_name' )->join( ... 

If you prefer, the following should be equivalent:

 $db = Zend_Db::factory( ...options... ); $db->select()->from( 'my_table_name' )->join( ... 

Answer 2

You can also use the traditional $ model-> select () object by adding setIntegrityCheck (false), for example.

 $select = $table->select() ->setIntegrityCheck(false) ->from(array('m' => 'memberships'), array('b.id', 'b.title', 'b.description')) ->join(array('b' => 'blogs'), 'b.id = m.blog_id') ->where('m.user_id = ?', (int) $id) ->order('m.created DESC') ->limit(0, 30); 

This disables the check, which throws an exception:

 #0: Select query cannot join with another table