So I’m working on a project, and I want to find all of the rows that don’t have a row in it’s related table using Zend_Db_Table_Select… Simple right? Not really :( . In theory this would be the query that I would write…

SELECT author.id FROM author LEFT JOIN project ON project.author_id = author.id WHERE project.author_id IS NULL ORDER BY RAND() LIMIT 1

After toying with Zend_Table for a while I’ve managed to figure out how to do it…

    /**
     * Fetches a random author who has not had a project assigned to them
     * @return Ambigous <Zend_Db_Table_Row_Abstract, NULL>
     */
    public function findRandomNull(){
        $authorModel = $this->getDbTable();
        $select = $authorModel->select()
                                ->setIntegrityCheck(false)
                                ->from('author')
                                ->joinLeft('project', 'project.author_id = author.id', array())
                                ->where('project.author_id IS NULL')
                                ->order('RAND()');

        return $authorModel->fetchRow($select);
    }

Which will produce the following SQL

SELECT `author`.* FROM `author` LEFT JOIN `project` ON project.author_id = author.id WHERE (project.author_id IS NULL) ORDER BY RAND() ASC