beforeDelete() function help

Yii 1.1 General Discussion
1

The models in question are Supplier and Vehicle

In Supplier model I have a field ‘supplier_vehicles’ - because I was lazy I made this a multi-value field - that is it stores the IDs of Vehicle delimited by pipe, for example:

1|2|3|4|5|6|7|8|9|10

Now I want to set up a constraint in my Vehicle model, so that if the Vehicle ID exists in ‘supplier_vehicles’ field in any row of the Supplier table, then it should throw a CDbException.

I believe this needs to be done in a ‘beforeDelete()’ function, but how?

2 



class Vehicle extends CActiveRecord{


// code

......


public function beforeDelete(){


$suppliers = SupplierVehices::model()->findAll();


foreach($suppliers as $id=>$item){

$vehicles = explode('|', $item->supplier_vehicles);

if (in_array($this->id, array_values($vehicles)))

        // throw CDbException here

}

return parent::beforeDelete();

}


}

Is this you want?

3

Don’t use a single column as a multi-value storage. It is called a “Jaywalking” antipattern (following “SQL Antipatterns” book by Bill Karwin). It is the first antipattern (means pattern which brings more problems than benefits) of the book, so make a conclusion… :)

If you use this approach, it means you don’t need Relational Database. But since you ask us to help you with constraints, you want to build a relational database on the top of relational database (but without any relations). You want to do things already realized in RDBM but with PHP and Yii.

And since you are so lazy then you should prepare for a huge amount of work if you’ll continue with this approach ;)

4

Enclose the whole string with delimiters, e.g.


|1|2|3|4|5|6|7|8|9|10|

In Vehicle::beforeDelete() you then simply call


public function beforeDelete($event)

{

  if(Supplier::model()->count('supplier_vehicles LIKE :id', array(':id' => '%|' . $this->primaryKey . '|%')))

    throw new CDbException('Constraint violation');

  return parent::beforeDelete($event);

}
5

Thanks diggy and ciss - I’ll give those suggestions a try.

andy_s - lol I totally agree and I know it’s bad practice! I got lazy because there is actually another multi-value field in this table - so I couldn’t be bothered creating 2 link tables!

But it’s the last time I’ll be doing it this way - I promise! :)

6

ciss - Your solution works well! However just one thing - I also have a constraint on this table at database level - how can I ensure that the DB constraint is checked first? At the moment it seems the beforeDelete() constraint is being checked first.

7

And this pattern-for-lazy-guys brings another problem! :lol: You have two constraints, one is handled by a database, and one by a program. Unfortunately a program always works first and after it sends a delete query, a corresponding row will be really deleted without further program checks. Maybe you need to keep this row in memory and insert it again into a database if program constraint fails? Or maybe you should handle both constraints in your program now? Both ways are weird.

Sorry me, if I look sarcastic, I really want to help, but our help will be useless if you’ll continue using this approach (because next your question can be "how to GROUP BY vehicle_id in Supplier table).

8

function beforeDelete($event)
{
   if ($this->getOwner()) {
    if (null !== $this->deletedAttribute ) {
// set deleted timestamp here
    $this->getOwner()->{$this->deletedAttribute} = $this->getTimestampByAttribute($this->deletedAttribute);
}
if (null !== $this->deletedStateAttribute ) {
$this->getOwner()->{$this->deletedStateAttribute} = $this->deletedStateValue;
}
$this->getOwner()->update([$this->deletedStateAttribute, $this->deletedAttribute]);

$event->isValid = false; // important here, return false that will be softdelete, true as real
return false;
}
}