Yii find models through many_many relationship

6
votes

I'm using Yii and have 3 tables: users, devices with a users_devices table (user_id, device_id) to define the MANY_MANY relationship between them.

What I'm looking for is the simplest method of finding a device from its id (devices.id) that belongs to a specific user (users.id) via ActiveRecord.

The scenario is a REST API is querying for a device, but I want to verify the device is owned by the user for security reasons.

Something like this is the idea:

$device = Devices::model()->findByPk($deviceId)->having(
    array('user_id' => $userId));

Thanks in advance for any help, I've been researching this for a while and can't find an elegant solution.

3
phpmysqlactiverecordmany-to-manyyii
Does users_devices have user_id and device_id as primary (composite) keys? Is so, don't forget to override prmaryKey() like they do here. And you could just do a find on that table using your custom-built model to identify the user-device relationship (or lack of one). - hobs
You meant MANY_MANY not HAS_MANY. - hobs

3 Answers

7
votes

Got some help on the Yii forums, which led me to figure it out myself:

$device = Device::model()->with('users')->find(array(
    'condition' => 'user_id = :userId AND device_id=:deviceId',
    'params' => array(':userId' => Yii::app()->user->id, ':deviceId' => $_GET['id'])));
2
votes

Take two.

in Device.php:

// creates a users property within a Device, a container of associated Users
public function relations()
    {
        return array(
            'users'=>array(self::MANY_MANY, 'User',  // don't use HAS_MANY
                'user_devices(user_id, device_id)'), // composite key assumed
        );
    }

then to find if the requested device is owned by the requesting user:

$device = Device::model()->findByPk($deviceId);
if ( $device->users->findByPk($userId) == Null )
    $device = Null;

It seems like this would work but inefficiently retrieve a lot of unneeded User records, since you already know who the user is and likely already have their activeRecord. To avoid this innefficiency, the Yii Agile Development book uses raw SQL for M2M relationship queries within the parent model (Device.php):

// "Agile" uses a $user AR argument, you can use $userId instead
public function doesUserOwnDevice($userId) 
{
    $sql = "SELECT user_id FROM user_devices WHERE
    device_id=:deviceId AND user_id=:userId";
    $command = Yii::app()->db->createCommand($sql);
    $command->bindValue(":deviceId", $this->id, PDO::PARAM_INT);
    $command->bindValue(":userId", $userId, PDO::PARAM_INT);
    return $command->execute()==1 ? true : false;
}

I used Device rather than Devices for the name of the model (likewise device for the name of the table). Refactor if you cut and paste. Likewise for User. Likewise for missing "tbl_" prefix.

1
votes

Do you have to use AR?

I always prefer to use the query builder when dealing with complex statements...

i.e.

$user = Yii::app()->db->createCommand()
    ->select('id, username, profile')
    ->from('tbl_user u')
    ->join('tbl_profile p', 'u.id=p.user_id')
    ->where('id=:id', array(':id'=>$id))
    ->queryRow();