Can I rewrite this using Zend_Db_Select?

Question

I need to write the following query:

SELECT forum_threads.id AS id_thread,
forum_threads.topic,
forum_threads.date_created,
forum_posts.content,
CONCAT(users.first, ' ', users.last) AS author_name 
  FROM forum_threads,forum_posts,users
     WHERE forum_threads.category_id=1
        AND forum_threads.author_id=users.id
        AND forum_posts.id=
            (SELECT id FROM forum_posts WHERE thread_id=`id_thread` ORDER BY date_posted ASC LIMIT 0,1)

I'm not asking anyone to do the work for me. I just couldn't find anything in the reference that could do a query like this. Point me in the right direction and that should be everything I need.

I can get to the point where I need to the subquery, then I have no idea how to progress. Any ideas?

FYI: I want to use a Zend_Db_Select object because I'm sending it to a Zend_Paginator

Clarification of what the query is doing: Pulling all threads for a given forum category a long with the content of the first post.

Answer 1

I developed a lot of Zend_Db_Select while I worked for Zend, as well I wrote the docs and the unit tests.

My usual advice with Zend_Db_Select is that you don't have to use it. It's meant to be used when you have complex application logic that needs to build up a query piece by piece. If you already know the full SQL query, it's far easier to just execute it as a string and don't use Zend_Db_Select at all.

But to answer your query, I include a solution below.

I changed the query so that it doesn't need a subquery. I'm using a trick with LEFT JOIN to match the post p that has no other earlier post p2 with the same thread_id. This should be more efficient than the subquery idea you had.

$select = $db->select()
 ->from(array('t'=>'forum_threads'), array('id_thread'=>'id', 'topic', 'date_created'))
 ->join(array('p'=>'forum_posts'), 't.id=p.thread_id', array('content'))
 ->joinLeft(array('p2'=>'forum_posts'),
     't.id=p2.thread_id AND p.id > p2.id', array())
 ->join(array('u'=>'users'), 't.author_id = u.id',
     array('author_name'=>new Zend_Db_Expr("CONCAT(u.first, ' ', u.last)")))
 ->where('t.category_id = 1')
 ->where('p2.id IS NULL');

I test this and it has the following output:

SELECT `t`.`id` AS `id_thread`, `t`.`topic`, `t`.`date_created`, `p`.`content`,
  CONCAT(u.first, ' ', u.last) AS `author_name` 
FROM `forum_threads` AS `t`
 INNER JOIN `forum_posts` AS `p` ON t.id=p.thread_id
 LEFT JOIN `forum_posts` AS `p2` ON t.id=p2.thread_id AND p.id > p2.id
 INNER JOIN `users` AS `u` ON t.author_id = u.id 
WHERE (t.category_id = 1) AND (p2.id IS NULL)