Yii find models through many_many relationship

Question

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.

Answer 1

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

Answer 2

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.