The query below signifies things i am attempting to do, I have to pull in a listing of blog_posts as well as join having a customers table.
What it's also doing is tugging inside a random 'picture_filename' from blog_updates_pictures. It requires blog_updates like a join to reference your blog_update_id.
What Let me do now's also COUNT the amount of blog_updates for every blog_publish. I believe this can be a subquery but every implementation fails. It might be also good to achieve the count accept arguments (ie. blog_updates where date = ?). Also, there might be no updates or pictures to some blog_publish.
$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 in which?
What Let me do now's also COUNT the amount of blog_updates for every blog_publish.
You are able to make that happen using aggregation - use
GROUP BY bu.blog_id, so that as additional column
COUNT(bu.blog_id) AS blog_updates_count. It will work.
Create subselects as:
$subselect = $db->select() ->from( array ('bu' => 'blog_updates' ), array( 'blog_id', 'updates' => 'count(*)' ) ) ->group("bu.blog_id");
After which join the $subselect together with your primary $choose as:
$select->join( array( 't' => $subselect), "t.blog_id = b.blog_id", array( 'updates' ) );
When we had the table structure you can find a far more complete answer