PHP postgres Zend Framework Select query with WHER

Question

Ok Im still new to Zend, and trying to find how the ORM works in general for it, seems to be posing a difficult task (anyone know where they have a specific document for it). That aside what I am trying to do is take an existing query and add a "AND" clause to it. Just not sure how to go about doing that, as the examples I have found else where dont look like this one, and I'd rather avoid breakage on this

$select = $this->select()
            ->from('offer_term')
            ->setIntegrityCheck(false)
            ->joinUsing('term_mapping', 'term_id')
            ->where('promo_id = ?', $promo_id);
        return $this->fetchAll($select);

Answer 1

Actually this is really easy once you start understand how Zend_db works.

your query:

$select = $this->select()
            ->from('offer_term')
            ->setIntegrityCheck(false)
            ->joinUsing('term_mapping', 'term_id')
            ->where('promo_id = ?', $promo_id);
        return $this->fetchAll($select);

you are already using the select() object for performing the query, so try refactoring to something like this(I'll include how do it with a conditional as well):

$select = $this->select()->setIntegrityCheck(false);
$select->from('offer_term');//if query is inside of a DbTable model the from() can be omitted
$select->joinUsing('term_mapping', 'term_id');
$select->where('promo_id = ?', $promo_id);//Every time you add a where(), select() adds the param using the AND operator
$select->where('something_new = ?', $new_param);//if you need to add a param using OR you can use the orWhere() method.
if (TRUE) {
    $select->orWhere('something = ?',$parameter);//This will add an OR WHERE to the query if the condition is true.
}
return $this->fetchAll($select);

Adding an AND in a select() query is done just by adding another where() to the chain. This can be done by chaining as your original query does or by using separate statements as I have done. If you need to use the OR operator in select() query you can use the orWhere() method.

You can mix chaining and separate statements as needed, which makes adding a conditional pretty easy.

NOTE: Zend_Db is not an ORM, it is an implementation of The table gateway and table row patterns (I hope I got the names correct). So please do not expect full ORM functionality.

Hope this helps.

Answer 2

Try:

$select = $this->select()
    ->from('offer_term')
    ->setIntegrityCheck(false)
    ->joinUsing('term_mapping', 'term_id')
    ->where('promo_id = ?', $promo_id);
    ->where('name = ?', $name); // WHERE promo_id = $promo_id AND name = $name
return $this->fetchAll($select);

And remember to using manual.