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.

Michael · Accepted Answer · 2012-10-31 09:19:28Z

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, 2012 at 9:19

Michael

10.5k1 gold badge39 silver badges51 bronze badges

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

1 Comment

Frederick Marcoux Over a year ago

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`

Yes Barry · Accepted Answer · 2012-10-31 10:03:01Z

1

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.

answered Oct 31, 2012 at 10:03

Yes Barry

9,9465 gold badges54 silver badges77 bronze badges

Comments

RockyFord · Accepted Answer · 2012-10-31 11:36:57Z

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, 2012 at 11:36

RockyFord

8,5191 gold badge17 silver badges22 bronze badges

6 Comments

Frederick Marcoux Over a year ago

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...

RockyFord Over a year ago

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.

Frederick Marcoux Over a year ago

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 Over a year ago

I'm still getting this error even using setIntegrityCheck(false) : Message: Select query cannot join with another table

RockyFord Over a year ago

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

|

Frederick Marcoux · Accepted Answer · 2012-10-31 12:54:34Z

0

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!

answered Oct 31, 2012 at 12:54

Frederick Marcoux

2,2091 gold badge30 silver badges58 bronze badges

Collectives™ on Stack Overflow

MySQL Query to a Zend DB Select

4 Answers 4

1 Comment

Comments

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related