I have a products and products_images AR classes (and tables). When user delete or create product photo, the system generate the default (first) photo again.
I don’t know it is an ellegant or not, so if you have nicer pattern please share it.
Thanks!
class ProductsPhotos extends CActiveRecord
/**
* first photo id of the product
* @param int $id_product product id
* @return int
*/
public function getFirstPhotoId($id_product="")
{
if (empty($id_product)) {
$id_product = $this->id_product;
}
$sql = "SELECT id
FROM products_photos
WHERE id_product=".$this->id_product."
ORDER BY POSITION
LIMIT 1";
$first = Yii::app()->db->createCommand($sql)->queryScalar();
// convert false to null (it's require to AR)
return $first===false?NULL:$first;
}
/**
* Change product's main photo after deleting
*/
public function afterDelete()
{
$product = Products::model()->findByPk($this->id_product);
$product->photo_main = $this->getFirstPhotoId();
$product->save(false);
return parent::afterDelete();
}
/**
* change product's main photo after creating or modifying
* @return boolean
*/
public function afterSave()
{
$product = Products::model()->findByPk($this->id_product);
$product->photo_main = $this->getFirstPhotoId();
$product->save(false);
return parent::afterSave();
}
}
I wouldn’t feed params directly in to an SQL statement like you have.
Should be done like,
$sql = "SELECT id
FROM products_photos
WHERE id_product=:id
ORDER BY POSITION
LIMIT 1";
$first = Yii::app()->db->createCommand($sql)->queryScalar(array(':id' => $this->id_product));
I’d be tempted to do a join with the products and product images tables instead when you need it so you don’t need to update every photo change.
Thanks. I try to use AR and relations, but this example doesn’t work because the null and the false is not equal in PHP (or something different, but I don’t understand). Could you have any idea? See code below:
public function getFirstPhotoId($id_product="")
{
if (empty($id_product)) {
$id_product = $this->id_product;
}
return Products::model()->findPk($id_product)->productImages->find()->id
}
Thanks, but this code is wrong, because $productPhoto is an empty AR. When you read the id_product attributes make an exception (I made a similar code first time, but I realised it doesn’t work unfortunately) . See:
[font=“Courier New”][color="#696969"]CDbCommand hibába ütközött az SQL parancs végrehajtása közben: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘:id ORDER BY position, id DESC LIMIT 1’ at line 1. The SQL statement executed was: SELECT * FROM products_photost WHERE id_product:id ORDER BY position, id DESC LIMIT 1[/color][/font]