Zend subquery arguments

Question

The query below represents what I am trying to do, I need to pull in a list of blog_posts and also join with a users table.

What it is also doing is pulling in a random 'picture_filename' from blog_updates_pictures. It needs blog_updates as a join to reference the blog_update_id.

What I'd like to do now is also COUNT the number of blog_updates for each blog_post. I think this is a subquery but every implementation fails. It would also be good to have the count accept arguments (ie. blog_updates where date = ?). Also, there may be no updates or pictures to a blog_post.

$select = $db->select ();
$select->from ( array ('b' => 'blog_posts' ), array('headline', 'date_created'));
$select->join ( array ('u' => 'users' ), 'u.user_id = b.user_id', array ( 'email' ) );
$select->joinLeft ( array ('bu' => 'blog_updates' ), 'bu.blog_id = b.blog_id', array () );
$select->joinLeft ( array ('bup' => 'blog_updates_pictures' ), 'bu.blog_update_id = bup.blog_update_id', array ('picture_filename' ) );

Can someone show me the way?

Thanks

Check this answer for how to create subquery using Zend_Db_Select http://stackoverflow.com/questions/5604872/nested-select-using-zend-db/5609246#5609246 — satrun77, Nov 07 '11 at 20:28

score 0 · Answer 1 · answered Nov 04 '11 at 15:20

0

What I'd like to do now is also COUNT the number of blog_updates for each blog_post.

You can achieve that using aggregation - use GROUP BY bu.blog_id, and as additional column COUNT(bu.blog_id) AS blog_updates_count. It should work.

answered Nov 04 '11 at 15:20

Radek Benkel

8,278
3
32
41

That does work, but how can I then specify the date param? It will then only find blog_posts that do have updates ... – bluedaniel Nov 04 '11 at 16:15
Code that I wrote will find also posts without updates. It depends on using LEFT or INNER JOIN, not on GROUP BY. Specyfing date? You can specify date param as normal WHERE date_created < [date]. – Radek Benkel Nov 04 '11 at 19:48
Adding a where clause returns ONLY blog_posts with updates that are date_created <. I want it to return all blog_posts and updates to be null if they dont meet the search criteria. – bluedaniel Nov 04 '11 at 20:43
Replace `GROUP BY bu.blog_id` with `GROUP BY b.blog_id`. – Radek Benkel Nov 06 '11 at 11:31
Yeah that is a correction but still where can I put the specification for updates over a certain date? – bluedaniel Nov 07 '11 at 15:01
You're doing left join, so in join clause => `SELECT FROM table LEFT JOIN another_table ON table.at_id = another_table.id AND another_table.updated_at BETWEEN '2011-12-12 AND '2011-12-23'` – Radek Benkel Nov 08 '11 at 20:24

score 0 · Answer 2 · answered Nov 14 '11 at 13:12

Create subselects as:

$subselect = $db->select()
  ->from(
    array ('bu' => 'blog_updates' ),
    array(
      'blog_id', 
      'updates' => 'count(*)' 
    )
  )
  ->group("bu.blog_id");

And then join the $subselect with your main $select as:

$select->join(
  array( 't' => $subselect),
  "t.blog_id = b.blog_id",
  array( 'updates' )
);

If we had the table structure you might get a more complete answer

Zend subquery arguments

2 Answers2