Yii Framework Forum: DELETE from JOIN - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

DELETE from JOIN Rate Topic: -----

#1 User is offline   Sander 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 169
  • Joined: 03-November 09
  • Location:Amsterdam, Netherlands

Posted 31 December 2009 - 07:31 AM

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.
0

#2 User is offline   Onman 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 175
  • Joined: 26-December 09
  • Location:The Netherlands

Posted 02 January 2010 - 06:00 PM

View PostSander, 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.
0

#3 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,016
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 04 January 2010 - 03:56 AM

View PostOnman, 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.
1

#4 User is offline   Sander 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 169
  • Joined: 03-November 09
  • Location:Amsterdam, Netherlands

Posted 13 January 2010 - 10:02 AM

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).
0

#5 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,016
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 13 January 2010 - 10:10 AM

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);

0

#6 User is offline   Sander 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 169
  • Joined: 03-November 09
  • Location:Amsterdam, Netherlands

Posted 13 January 2010 - 10:17 AM

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 :)
0

#7 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,016
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 13 January 2010 - 11:20 AM

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().
0

#8 User is offline   Sander 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 169
  • Joined: 03-November 09
  • Location:Amsterdam, Netherlands

Posted 14 January 2010 - 06:36 AM

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

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users