[Sander]

I would like to delete records from my Field_Values table based on the value of a 2nd-level foreign key, much like the following:

Field_Value::model()->with('field')->deleteAllByAttributes(array(
	'field.profile_id' => $this->profile_id,
));

Since the with() method in the chain returns a CActiveFinder instead of CActiveRecord, this obviously won't work. Assuming I don't want to use direct SQL, what would be the best way to handle this situation? All I can think of is SELECTing all Field_Value records, put their IDs in an array and use that array in a subsequent DELETE query. I hope there is a better way.

[Onman]

Sander, on 31 December 2009 - 07:31 AM, said:

...

Field_Value::model()->with('field')->deleteAllByAttributes(array(
	'field.profile_id' => $this->profile_id,
));

Since the with() method in the chain returns a CActiveFinder instead of CActiveRecord, this obviously won't work.
...

try this:

Field_Value::model()->with('field')->findAll()->deleteAllByAttributes(array(
	'field.profile_id' => $this->profile_id,
));

instead of findAll() you can use find(), findByPk(), etc.
You can also add a CDbCriteria as a parameter.

[Mike]

Onman, on 02 January 2010 - 06:00 PM, said:

try this:

Field_Value::model()->with('field')->findAll()->deleteAllByAttributes(array(
	'field.profile_id' => $this->profile_id,
));

instead of findAll() you can use find(), findByPk(), etc.
You can also add a CDbCriteria as a parameter.

This will not work, as findAll() always returns an array, so the deleteAllByAttributes() call will fail. I wondered, why the delete*() methods are not implemented in CActiveFinder. Might be useless for the standard LEFT JOIN but useful for INNER JOIN. But maybe i'm missing something here .

@Sander:
In your situation i think i'd add a method like deleteFieldValues() to your AR and perform deletion in a seperate call.

[Sander]

Sorry for the late response, I've been on vacation last week

Right now I'm doing it like this:

$fields = Field::model()->findAllByAttributes(array(
	'profile_id' => $this->profile_id,
));
$field_ids = array();
foreach($fields as $field) { // Collect all Fields attached to this Profile
	$field_ids[] = $field->field_id;
}
Field_Value::model()->deleteAllByAttributes(array(
	'field_id' => $field_ids,
));

I don't really like the fact that I need an additional query as wel as a custom loop for this though (though I know I could ditch AR for this but that's not really an option).

[Mike]

Why don't you want to use this solution? It's not always necessary to fetch full AR objects if a simple SQL statement does the job:

public function deleteByProfileId($id)
{
    $table=$this->tableName();
    $this->getDbConnection()->createCommand('DELETE FROM '.$table.' WHERE '. /* your condition here */)->execute();
}

MyRecord::model()->deleteByProfileId($someId);

[Sander]

Our app has an explicit requirement to be as DB independent as can be, so I want to use as little direct SQL as possible. A delete statement will probably remain pretty universal across DBs, but still I prefer to start off as abstract as possible. If it proves to be a serious bottleneck, I could always change it to hard SQL. Fortunately, this operation won't be executed a lot since it's mostly used for maintenance by a single admin, but still a nice AR way to handle similar situations would always be appreciated

[Mike]

I see. Not sure, if that's possible, but maybe you could use CDbCommandBuilder in that method to build your query. That's exactly what AR does internally. Using CDbCommandBuilder makes sure, that your query will be compatible to your DBMS. You could take a look into db/ar/CActiveRecord.php to see how it's used by AR. E.g. see the implementation of deleteByPk() or insert().

[Sander]

Yes, that might be a good alternative. I'll look into it when I have the chance, thanks