4

I have the following raw query which moves items from a shopping cart to an order table:

insert into webshop_order_item (
    order_id,
    product_id,
    count
)
select
    1,
    product_id,
    count
from 
    webshop_cart

I am using the Zend DB for all my modeling. I was wondering if there is a way of achieving the goal of the above query without having to use a raw query?

rodrigo-silveira
  • 12,607
  • 11
  • 69
  • 123
sroes
  • 14,663
  • 1
  • 53
  • 72

2 Answers2

6

There is no way of inserting from select in zend db yet. However, if you need this feature only for one adapter, then you could use approach similar to given below:

public function insertSelect($tableName, Zend_Db_Select $select, array
$fields = array()) {
    $fieldString = '';
    if (count($fields))
    {
        foreach($fields as $fieldKey => $field)
        {
            $fields[$fieldKey] =  $this->quoteIdentifier($field);
        }

        $fieldString = ' (' . implode(',', $fields) . ')';
    }

    $query  = "INSERT INTO ".$this->quoteIdentifier($tableName) .
$fieldString . " ";
    $query .= $select;

    $this->_db->query($query);
}
altern
  • 5,829
  • 5
  • 44
  • 72
2

This works fine for me: I use a Zend_Db_Expr. Sanitize as needed.

$this->db->insert("sa_article_attributes", array(
    "article_id" => $sid,
    "article_attribute_id" => new Zend_Db_Expr("(
        SELECT Attribute.id FROM sa_attributes Attribute
        WHERE Attribute.title = '{$category}'
        LIMIT 1
        )")
    )
);
rodrigo-silveira
  • 12,607
  • 11
  • 69
  • 123
  • This is not the same. The query from my question will insert multiple rows. Yours will only insert one. – sroes Mar 07 '13 at 20:14