MySQL选择（Zend Framework DB Select）

Question

I have the following select:

public function fetchFooSum($id)
    {
        $db = Zend_Registry::get('dbAdapter');
        $select = $db->query("SELECT SUM(prod) FROM `output` WHERE loc_id IN (SELECT loc_id FROM `locations` WHERE location_id = ".$id.");");

        return $select->fetchAll();
    }

I have 2 questions about this:

1. Is this considered a reasonably acceptable way to form such a query?

       SELECT SUM(prod) FROM output WHERE loc_id IN (SELECT loc_id FROM locations WHERE location_id = ".$id.");
   

   or is that not ideal (should I be using some other way of doing this... a join or union or somesuch thing that I've heard of but am not clear on when to use)...

2. Within the Zend Framework, how would you formulate such a query?

The method I am using works but I doubt it is the ideal way to do it (both as a regular SQL query and in the way I do it within Zend Framework).

Any advice about the query or how to better implement it in ZF is appreciated.

Answer 1

I would prefer an inner join to using the IN operator for this query as I believe it will outperform your query in its current form (but do not yet have a reference as to why :)

// example using Zend_Db_Select
$select = $db->select()
             ->from('output', array('sum(prod)'))
             ->join('locations', 'output.loc_id = locations.loc_id', array())
             ->where('locations.location_id = ?', $id);

// to dump query
print_r($select->__toString());

// to execute
$result = $db->fetchOne($select);

I would strongly recommend reading the Zend_Db_Select documentation which has plenty of practical examples - and shouldn't take more than an hour.