[JFReyes]

Hi:

I have a problem validating the existence of a foreign key:

Table countries
* country_code varchar(2) (pk)
* country_name varchar(64)
* country_currency char(3) (fk->currencies.currency_code)

Table currencies
* currency_code char(3) (pk)
* currency_name varchar(32)

Model Countries.php

public function rules()
{
	return array(
// other validation rules
		array('country_currency', 'length', 'is'=>3),
		array('country_currency', 'match', 'pattern'=>'/[A-Z]{3}/', 'message'=>'Uppercase alphabetic only!'),
		array('country_currency', 'exists',
			'attributeName'=>'currency_code',
			'className'=>'application.models.Currencies',
			'skipOnError',
			'message'=>'Currency must be already defined!'),
// other validation rules
}

Upon saving the country with a non existing currency it explodes with the following error:

CDbException

Description

CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation:
1452 Cannot add or update a child row: a foreign key constraint fails (`mpldb/countries`,
CONSTRAINT `fk_countries_currencies` FOREIGN KEY (`country_currency`)
REFERENCES `currencies` (`currency_code`) ON DELETE NO ACTION ON UPDATE NO ACTION)

What am I doing wrong? All I want is my error message displayed. Thanks for the help.

[jsoo]

Does your controller action include a validate() step before saving the new record?

[JFReyes]

jsoo, on 22 March 2010 - 05:33 PM, said:

Does your controller action include a validate() step before saving the new record?

No, I didn't know it needed one. I'm using the default code created by yiic's crud command and thought that it was the model's job to validate this kind of thing. As a matter of fact I'm being extra cautious because I'll actually use a dropDownList to select the currency, not a textField. Any suggestions? Thanks for the help.

[jsoo]

You're right that the CActiveRecord save() does run validation by default. Now I'm wondering about the skipOnError attribute. Is the result any different if you remove that? [Sorry, I'm taking a bit of a guess.]

Edit: On second thought, that can't be right. What does your controller action look like?

[JFReyes]

jsoo, on 22 March 2010 - 08:47 PM, said:

You're right that the CActiveRecord save() does run validation by default. Now I'm wondering about the skipOnError attribute. Is the result any different if you remove that? [Sorry, I'm taking a bit of a guess.]

skipOnError makes no difference, but I tested it (by introducing a different data entry error) and it works.

Quote

Edit: On second thought, that can't be right. What does your controller action look like?

The controller's create and update actions look like this:

/**
 * Creates a new model.
 * If creation is successful, the browser will be redirected to the 'view' page.
 */
public function actionCreate()
{
	$model=new Countries;

	// Uncomment the following line if AJAX validation is needed
	// $this->performAjaxValidation($model);

	if(isset($_POST['Countries']))
	{
		$model->attributes=$_POST['Countries'];
		if($model->save())
			$this->redirect(array('view','id'=>$model->country_code));
	}

	$this->render('create',array(
		'model'=>$model,
		'modelCurrencies'=>Currencies::model(),
		));
}

/**
 * Updates a particular model.
 * If update is successful, the browser will be redirected to the 'view' page.
 */
public function actionUpdate()
{
	$model=$this->loadModel();
		
	// Uncomment the following line if AJAX validation is needed
	// $this->performAjaxValidation($model);

	if(isset($_POST['Countries']))
	{
		$model->attributes=$_POST['Countries'];
		if($model->save())
			$this->redirect(array('view','id'=>$model->country_code));
	}

	$this->render('update',array(
		'model'=>$model,
		'modelCurrencies'=>Currencies::model(),
	));
}

The only variation from yiic's crud default is that I pass modelCurrencies to the view to populate the currencies dropDownList that I'll use instead of textField in the final version. Eliminating this parameter makes no difference either.

As a matter of fact, eliminating the rule altogether from the model produces the same error. Could this be a bug? Thanks for the help.

[jsoo]

JFReyes, on 23 March 2010 - 03:00 AM, said:

As a matter of fact, eliminating the rule altogether from the model produces the same error. Could this be a bug? Thanks for the help.

That's expected, because the error message is triggered by the database itself; it won't let you perform an insert/update that violates the foreign key constraint you've set up in the table definition.

Do your Countries and Currencies models each correctly show their relation in their respective relations() methods?

Are you able to generate a Yii validation error if you violate one of the earlier rules (length or match)?

[JFReyes]

jsoo, on 23 March 2010 - 06:53 AM, said:

That's expected, because the error message is triggered by the database itself; it won't let you perform an insert/update that violates the foreign key constraint you've set up in the table definition.

Yes, I expected MySQL to complain so no surprises here.

Quote

Do your Countries and Currencies models each correctly show their relation in their respective relations() methods?

I believe so:

Model Currencies.php

/**
 * @return array relational rules.
 */
public function relations()
{
	// NOTE: you may need to adjust the relation name and the related
	// class name for the relations automatically generated below.
	return array(
		'relFromCountries' => array(self::HAS_MANY, 'Countries', 'fk_countries_currencies'),
	);
}

Model Countries.php

/**
 * @return array relational rules.
 */
public function relations()
{
	// NOTE: you may need to adjust the relation name and the related
	// class name for the relations automatically generated below.
	return array(
		'relToCurrencies' => array(self::BELONGS_TO, 'Currencies', 'fk_countries_currencies'),
	);
}

I use relFrom/relTo as the relationship prefix because the default (the table name) confuses me in this context. I hope I'm not breaking any laws here
Just to make sure I also tried using the actual fields involved in the relationship instead of the foreign key with the same result.

Quote

Are you able to generate a Yii validation error if you violate one of the earlier rules (length or match)?

Yes, that does work as mentioned before. Any more ideas? Thanks again.

[jsoo]

I think you should have this instead:

Model Currencies.php

/**
 * @return array relational rules.
 */
public function relations()
{
	// NOTE: you may need to adjust the relation name and the related
	// class name for the relations automatically generated below.
	return array(
		'relFromCountries' => array(self::HAS_MANY, 'Countries', 'country_currency'),
	);
}

Model Countries.php

/**
 * @return array relational rules.
 */
public function relations()
{
	// NOTE: you may need to adjust the relation name and the related
	// class name for the relations automatically generated below.
	return array(
		'relToCurrencies' => array(self::BELONGS_TO, 'Currencies', 'country_currency'),
	);
}

That is, use the column name(s) of the foreign key rather than the name of the foreign key itself.

[JFReyes]

Quote

That is, use the column name(s) of the foreign key rather than the name of the foreign key itself.

I already tried it and it didn't work. Neither did different permutations of the columns and the foreign key. I guess I need to see a code sample to figure it out; either that or else it's a bug...

Thanks for your help, and keep the ideas coming...

[jsoo]

JFReyes, on 24 March 2010 - 02:34 PM, said:

Thanks for your help, and keep the ideas coming...

Not very helpful help so far

But I think I've just seen the obvious point we've been overlooking. The shortcut for CExistValidator is "exist", not "exists". Though why that isn't throwing an error I don't know.

[JFReyes]

Quote

The shortcut for CExistValidator is "exist", not "exists". Though why that isn't throwing an error I don't know.

I'm afraid not. When I replace "exists" with "exist" this happens:

CException
Description

Property "CExistValidator.0" is not defined.

There's a discrepancy between the official documentation here and the cookbook article here. I'm going to report this as a bug hoping Master Qiang can help.

[JFReyes]

PROBLEM SOLVED!

JFReyes, on 25 March 2010 - 04:22 AM, said:

I'm afraid not. When I replace "exists" with "exist" this happens:

CException
Description

Property "CExistValidator.0" is not defined.

There's a discrepancy between the official documentation here and the cookbook article here. I'm going to report this as a bug hoping Master Qiang can help.

The tip you gave me made me re-examine the syntax, as I remembered Yii doesn't put out error messages on model errors (I believe this is a bad practice).

You're right, the correct name is "exist" (I will report it in the Cookbook); in addition, "skipOnError" required setting to true. The working version resulted like this:

array('country_currency', 'exist',
	'on'=>'create, update',
	'attributeName'=>'currency_code',
	'className'=>'Currencies',
	'skipOnError'=>true,
	'message'=>'Currency must be already defined!'
),

Thank you for your assistance!