Using Active Record

Hi!

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.

Try :



$productPhoto = ProductsPhotos::model()->find('id_product:id',


array(


  ':id'=>$this->id_product,


));





$productPhoto->id;

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_photos t WHERE id_product:id ORDER BY position, id DESC LIMIT 1[/color][/font]

You’re missing the comparison, need the equals =


$productPhoto = ProductsPhotos::model()->find('id_product=:id',

array(

  ':id'=>$this->id_product,

));


$productPhoto->id;

Good work, I had not noticed