Translating a query to use Zend_Db_Select

Question

I'm having some problems translating this query to use ZF's 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 results)

Memberships is a link table between blogs and users. It's a simple | id | blog_id | user_id | affair.

Here's what I have so far:

// $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 the (strange (to me)) error I'm getting:

#0: Select query cannot join with another table

Occurred on line 211 of D:\...\library\Zend\Db\Table\Select.php.

Thanks for your help.

Aaron · Accepted Answer · 2009-02-06 23:28:35Z

15

You could also still use the traditional $model->select() object by adding setIntegrityCheck(false), like so.

$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 that is throwing the exception:

#0: Select query cannot join with another table

answered Feb 6, 2009 at 23:28

Aaron

3822 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

cg. · Accepted Answer · 2009-02-06 13:01:36Z

9

When retrieved from your table object, the statement will be limited to that table I think. The Zend_Db_Table::select() methods returns a Zend_Db_Table_Select object which is a subclass of Zend_Db_Select and imposes this restriction. Try this 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( ...

edited Feb 6, 2009 at 13:01

answered Feb 6, 2009 at 12:47

cg.

3,6882 gold badges28 silver badges31 bronze badges

2 Comments

Ross Over a year ago

This worked as I wanted it to. However it seems to return the results as an array, rather than as an object? Not a problem, just wondering why.

cg. Over a year ago

It shouldn't, actually. How do you execute the select statement? $select->query() should return either a PDO_Statement or a Zend_Db_Statement.

Collectives™ on Stack Overflow

Translating a query to use Zend_Db_Select

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related