MySQL Query to a Zend DB Select

Question

I have a MySQL standard query that I need to convert into a Zend_Db_Select but I can't get it to work.

I get this error:

Select query cannot join with another table

Here's the query:

// THE COUNTER
$subselect = $this->table->select()->from(
        array('x' => 'blog_comments'),
        array('x.post_id', new Zend_Db_Expr('count(*) as comments')))
    ->group('post_id');
// THE TOTAL SELECT
$select->from(array('p' => 'blog_posts'), array('p.*'))
       ->setIntegrityCheck(false)
       ->joinLeft(array(
           'x' => $subselect,
           'x.post_id = p.id', 
           array()
           )
       );

If someone can convert this, it would be great because I need that in select() mode because I use Zend_Pagination.

For those who want the full PHP function: Pastebin and the stack traces: Pastebin.

score 1 · Accepted Answer · answered Oct 31 '12 at 09:19

1

You may need: setIntegrityCheck(false) - review: http://framework.zend.com/manual/1.12/en/zend.db.select.html for more information

$select = $this->select()
->from(params) 
->setIntegrityCheck(false) 
->joinLeft(params)
->where(params);

answered Oct 31 '12 at 09:19

Michael

10,124
1
34
49

I get a `Error 1064`. `SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS `x` ON x.post_id = p.id ORDER BY `created_date` DESC LIMIT 10' at line 2` – Frederick Marcoux Oct 31 '12 at 09:31

score 1 · Answer 2 · answered Oct 31 '12 at 10:03

As Michael already mentioned, you need setIntegrityCheck(false) in order to do joins with other tables using Zend_Db_Select.

Error 1064 is kind of ambiguous and encompasses all kinds of query syntax issues. So I suggest you wrap the sub query in parenthesis:

$select->setIntegrityCheck(false)
    ->from(array('p' => 'blog_posts'), array('p.*'))
    ->joinLeft(array(
        'x' => new Zend_Db_Expr("({$subselect})"), 
        'x.post_id = p.id', 
        array()
    ));

If that doesn't work well then there must be something wrong with your sub-select. Try echo $subselect; which will invoke the __toString() magic method and show you your query.

score 1 · Answer 3 · answered Oct 31 '12 at 11:36

1

The Expression is the Count(*) statement, not the whole subquery.

$subselect = $this->table->select()->from(
    array('x' => 'blog_comments'),
    array('x.post_id', new Zend_Db_Expr('count(*) as comments'))
)->group('post_id');

$select->from(array('p' => 'blog_posts'), array('p.*'))
    ->joinLeft(array(
        'x' => $subselect,
        'x.post_id = p.id', 
        array()
    ));

I'm not really sure you actually need to have a subquery. Regardless start out with a simple join and build on it.

//getGateway = whatever method used to access the database adapter.
//SetIntegrityCheck(false) locks the tables from writes allowing tables to be joined
$select = $this->getGateway()->select()->setIntegrityCheck(false);
$select->from('blog_posts');//default $cols = *
$select->join('blog_comments', 'blog_comments.post_id' = 'blog_posts.post_id');//does it really matter what kind of join you use? They all work the same so use whichever you like.
$select->group('blog_comments.post_id');

once you get the query working with the defaults you can refine it until it work how you want.

If you are doing a query to use paginator a count() expression is kind of useless as paginator will apply a limit and offset to every query.

Also you might consider executing this query in reverse. You might be better off joining in the other direction or just executing a query only on the comments table. Without seeing the rest of your structure it's kinda hard to tell.

Good Luck.

answered Oct 31 '12 at 11:36

RockyFord

8,529
1
15
21

Hmm, I forgot to specify that the paginator is only used to paginate the blog posts. The whole thing is more about counting comments for each posts using a join or something else... – Frederick Marcoux Oct 31 '12 at 11:41
Then why join at all just query as required using a where() against the post_id which is likely a foreign key or at least an index. Personally i'd just use a getComments() method from an entity model, but it doesn't look like you're structured to support that. – RockyFord Oct 31 '12 at 12:05
Nop, I'm not supporting that kind of model. But my link between the post and the comment is not a foreign key, it's just a reference. – Frederick Marcoux Oct 31 '12 at 12:26
I'm still getting this error even using `setIntegrityCheck(false)` : `Message: Select query cannot join with another table` – Frederick Marcoux Oct 31 '12 at 12:35
update your question and put your query in context. Where are you doing the query? In the controller, in a DbTable Model or in a different kind of model? This info might help troubleshoot – RockyFord Oct 31 '12 at 12:38
See the edit. And the query is made in a model who include a DbTable. – Frederick Marcoux Oct 31 '12 at 12:46

score 0 · Answer 4 · answered Oct 31 '12 at 12:54

I finaly made it in an other way.

I use my CMW_Model_Comments within the view to get the number of comment by the post ID.

// IN MY VIEW
echo $this->commentor->getCommentCount($post->id);

// IN MY CONTROLLER
$cmt = new CMW_Model_Comments();
$this->view->commentor = $cmt;

It works perfectly!

MySQL Query to a Zend DB Select

4 Answers4