User Auth with multiple tables

Yii 1.1 General Discussion
1

Hi everyone!

I am new to Yii and have a problem with my user authentication where username, password (and other details) are spread across multiple tables. Unfortunately the database design is dictated by SugarCRM.

Please take a look at following EER Diagram:

Username and password are highlighted in red, the table relations are highlighted green and blue.

If there is anything unclear, please let me know.

I know how I change my UserIdentity component to query the User model, but I have no idea how I can join/query the tables from my model.

Any help is appreciated :)

Regards,

ycast

2

You should create three Model, one for each table, and then create two relations as I can see from your relations diagram. But you told User model, but on the diagram there is no user table, seems one of these tables is User model. Could you provide UserIdentity and User files.

3

Thank you for your answer frantic.

The User model does not relate to a specific user table, because I thought I can have only this one User model for all the database handling related to user authentication and authorization.

Otherwise I will end up with a email_addr_bean_rel model although I only need that table as an "intermediate".

In plain MySQL, I would only write something like "SELECT […] FROM […] INNER JOIN […] ON […] WHERE […]".

Do you know any other way where I don’t have to create a model for every table associated?

My UserIdentity and User model are based on the code from http://www.yiiframework.com/doc/blog/1.1/en/prototype.auth and don’t include any other code yet.

4

Unfortunately, you cannot create relations without making the new classes using Active Records:

http://www.yiiframework.com/doc/api/1.1/CActiveRecord#relations-detail

As you can see there className as the second parameter. On the other hand, you may not use AR, but DAO. In this case you can just write plain SQL query:


public function authenticate()

{

	$sql = '

		SELECT [...] 

		FROM [...] 

		INNER JOIN [...] ON [...] 

		WHERE LOWER(email) = :email

	';


	$command = Yii::app()->db->createCommand($sql);


	$model = $command->bindParam(':email', $_POST['email'], PDO::PARAM_STR);

	$model = $command->query();


	if ( ( $row = $model->read() ) === false ) {

		$this->errorCode = self::ERROR_USERNAME_INVALID;

	} else if ( $row['password'] !== $_POST['password'] ) {

		$this->errorCode = self::ERROR_PASSWORD_INVALID;

	} else {

		$this->_id = $row['id'];

		$this->username = $row['username'];

		$this->errorCode = self::ERROR_NONE;

	}

	

	return $this->errorCode==self::ERROR_NONE;

}
5

Wow that’s fantastic, I haven’t noticed the DAO API so far. Thanks for your help :)