I've been working with ZF for a while now, but for some odd reason, I have
never really come across too many situations where I couldn't get a query to
way the way I liked right out of the box, probably because most of the
complexity was in the selects.
Anyhow, I have a query along the lines of this:
UPDATE
tbl_unit_size AS size_1, tbl_unit_size AS size_2
SET
size_1.sort_order = size_2.sort_order
WHERE
( size_1.ID IN (21,22) )
AND ( size_2.ID IN (21,22) )
AND ( size_1.ID <> size_2.ID)
;
Basically, it just swaps the sort order of two rows, in this case rows with
the ID 21 and 22.
Anyhow, I cant see a way to make the dbTable->update() do the trick, so that
leaves me with either just making a zend_db_statement, something like this:
$sql = "UPDATE
tbl_vendor_size AS size_1, tbl_vendor_size AS size_2
SET
size_1.sort_order = size_2.sort_order
WHERE
( size_1.VS_PK IN (:orig,:dest) )
AND ( size_2.VS_PK IN (:orig,:dest) )
AND ( size_1.VS_PK <> size_2.VS_PK );";
$data = array(':orig' => 21, ':dest' => 22);
$query = $this->getDbTable()->getAdapter()->query($sql, $data);
$query->execute();
Or would this be better in a stored procedure, in which case I don't know
how that would be accessed, or how you would know it worked afterwards.
How would you go about this, from a best practices perspective?
--
View this message in context:
http://zend-framework-community.634137.n4.nabble.com/Complex-UPDATE-statement-Best-method-tp3347426p3347426.html
Sent from the Zend Framework mailing list archive at Nabble.com.
