$result=Yii::app()->db->createCommand()
->select('product.product_name,image.image_path,price.selling_price,(SELECT special.special FROM clz_product_special special WHERE special.product_id=product.id AND special.start_date<=NOW() AND special.end_date>=NOW() ) special')
->from('{{product}} product')
->leftJoin('{{product_price}} price', 'product.id=price.product_id')
->leftJoin('{{product_image}} image', 'product.`id`=image.product_id')
->where(array('and','price.current=1',array('in','product.id',"SELECT product_id FROM {{active_category_product}} WHERE active_category_id=1")))
->queryAll();
上面
array('in','product.id',"SELECT product_id FROM {{active_category_product}} WHERE active_category_id=1")//这段代码不能用子查询,Yii解释出来是字符串,不能变成查询条件
SQL语句:
SELECT product.product_name,image.image_path,price.selling_price,
(SELECT special.special FROM clz_product_special special WHERE special.product_id=product.id AND special.start_date<=NOW() AND special.end_date>=NOW() ) special
FROM clz_product product
LEFT JOIN clz_product_price price ON product.id=price.product_id
LEFT JOIN clz_product_image image ON product.id=image.product_id
WHERE (price.current=1) AND (`product`.`id` IN (SELECT product_id FROM
clz_active_category_product WHERE active_category_id=1))