Hello!
Need some help to construct hard SQL query.
Now I've this old query:
$select = $this->getAdapter()->select()-> from('order_meta', array(new
Zend_Db_Expr('IF( `order_meta`.`price_type` = 1, `book_data`.`price1`,
`book_data`.`price2` ) `priceswitch` , IF( `order_meta`.`price_type` =1,
`book_data`.`price1` , `book_data`.`price2` ) * order_data.qty as `viso`
'),'orderid','price_type'))->where('order_meta.orderid =?',
$orderId)->joinInner('order_data','order_data.orderid =
order_meta.orderid', array('code','qty'))
->joinInner('book_data','book_data.id = order_data.code',array('id',
'title', 'code', 'pvmcat'))
->joinInner('cats_pvm','book_data.pvmcat = cats_pvm.id', array('coef'))
->joinInner('user','order_meta.userid = user.id', array('name'));
The SQL looks like:
SELECT IF( `order_meta`.`price_type` = 1, `book_data`.`price1`,
`book_data`.`price2` ) `priceswitch` , IF( `order_meta`.`price_type` =1,
`book_data`.`price1` , `book_data`.`price2` ) * order_data.qty as `viso`
, `order_meta`.`orderid`, `order_meta`.`price_type`, `order_data`.`code`,
`order_data`.`qty`, `book_data`.`id`, `book_data`.`title`,
`book_data`.`code`, `book_data`.`pvmcat`, `cats_pvm`.`coef`, `user`.`name`
FROM `order_meta` INNER JOIN `order_data` ON order_data.orderid =
order_meta.orderid INNER JOIN `book_data` ON book_data.id = order_data.code
INNER JOIN `cats_pvm` ON book_data.pvmcat = cats_pvm.id INNER JOIN `user`
ON order_meta.userid = user.id WHERE (order_meta.orderid ='4')
And now I should modify Zend_Db query in that way, that second SELECT
statement appear in SQL query.
In other word the final result should be like this:
SELECT priceswitch * qty, a.* from (SELECT IF( `order_meta`.`price_type`
= 1, `book_data`.`price1`, `book_data`.`price2` ) priceswitch`,
`order_meta`.`orderid`, `order_meta`.`price_type`, `order_data`.`code` as
order_code,`order_data`.`qty`, `book_data`.`id`, `book_data`.`title`,
`book_data`.`code` as book_code, `book_data`.`pvmcat`,
`cats_pvm`.`coef`, `user`.`name` FROM `order_meta` INNER JOIN
`order_data` ON order_data.orderid = order_meta.orderid INNER
JOIN `book_data` ON book_data.id = order_data.code INNER JOIN `cats_pvm`
ON book_data.pvmcat = cats_pvm.id INNER JOIN `user` ON order_meta.userid
= user.id WHERE (order_meta.orderid ='4') ) a
I can't understand from where to start..
Really need some help!
Thank you!
--
View this message in context:
http://zend-framework-community.634137.n4.nabble.com/Need-help-to-construct-SQL-with-Zend-DB-tp3225500p3225500.html
Sent from the Zend Framework mailing list archive at Nabble.com.
