Counting Current HABTM Relationships. CakePHP

Question

I need to assign hospitals to a group. I've decided to do this via habtm.

class FavoriteHospital extends AppModel {
    public $actsAs = array('Containable');
    ...

    var $hasAndBelongsToMany = array(
        'Hospital' => array(
            'className' => 'Hospital',
            'joinTable' => 'favorite_hospitals_hospital',
            'foreignKey' => 'favorite_hospital_id',
            'associationForeignKey' => 'hospital_id',
            'unique' => false,
        )
    );
}

There is a requirement to limit the number of hospitals in a group to 10. So I set my association to false because the default behaviour just deletes associations. I decided to start by testing the ability to add a hospital using a method....

public function addHospital($gid, $hid) {                           
    $this->data['Hospital']['id'] = $hid;
    $this->data['FavoriteHospital']['id'] = $gid;
    if($this->save($this->data)){return true;}
    return false;
}

Now, the above adds associations correctly, which is great, but every time I try to find a way to count the number of existing associations, I hit a brick wall.

There is a secondary issue with the above, and that is that it also allows duplicates.

Christos Hrousis Christos Hrousis · Accepted Answer · 2014-01-05T01:30:49

I figured out a very non-cake-php way to do it (also allows potential sql injection):

public function addHospital($gid, $hid) { 
    $db = $this->getDataSource();
    //$fh_count = $this->query("SELECT count(favorite_hospital_id) FROM favorite_hospitals_hospital WHERE favorite_hospital_id = ".$gid.";");
    $fh_count = $db->fetchAll(
            'SELECT count(favorite_hospital_id) from favorite_hospitals_hospital where favorite_hospital_id = ?',
            array($gid)
    );
    if($fh_count[0][0]['count(favorite_hospital_id)'] < 10){    
        //$existing_association = $this->query("SELECT count(*) FROM favorite_hospitals_hospital WHERE favorite_hospital_id = ".$gid." AND hospital_id = ".$hid.";");
        $existing_association = $db->fetchAll(
                        'SELECT count(*) from favorite_hospitals_hospital where favorite_hospital_id = ? and hospital_id = ?',
                        array($gid,$hid)
        );
        if($existing_association[0][0]['count(*)'] < 1){                    
            $this->data['Hospital']['id'] = $hid;
            $this->data['FavoriteHospital']['id'] = $gid;
            if($this->save($this->data)){return true;}
        } else {
            throw new Exception(__('Association already exists in that group'));
        }
    } else {
        throw new Exception(__('Already 10 Hospitals in that group'));
    }
    return false;
}

Can someone improve on this answer? either make it more cakephp, or at least make it more secure?

EDIT made it sanitary by using datasource object, although I still feel this answer isn't the best answer...

Counting Current HABTM Relationships. CakePHP

1 Answers