Zend_Db_Table subquery

Question

Hi, I have a some SQL that I want to use with ZendFW, but I can't get it working and it's driving me crazy. I get the correct result with this query:

SELECT DISTINCT e.festival_id FROM entries AS e, mail_log as m 
WHERE e.status = 1 
AND e.festival_id 
NOT IN (SELECT m.entry_id FROM entries AS e, mail_log as m WHERE m.entry_id = e.festival_id)

Help would be appreciated. Cheers :)

Answer 1

I am not sure that Zend_Db_Table is capable of handling subqueries.

Why do you need to use Zend_Db_Table?

You can execute the query directly using Zend_Db::query().

$db = Zend_Db_Table::getDefaultAdapter();
$db->query("SELECT DISTINCT e.festival_id FROM entries AS e, mail_log as m 
    WHERE e.status = 1 
    AND e.festival_id 
    NOT IN (SELECT m.entry_id FROM entries AS e, mail_log as m WHERE m.entry_id = e.festival_id)
");

Answer 2

Goran's answer is the best answer. But if you want a zend_db_table style query, this would be an alternative:

$sql = $table->select()
    ->setIntegrityCheck(false)
    ->from('entries', new Zend_Db_Expr('DISTINCT festival'))
    ->where('status = ?', 1)
    ->where('festival_id NOT IN (?)',new Zend_Db_Expr("SELECT m.entry_id FROM entries AS e, mail_log as m WHERE m.entry_id = e.festival_id"));