Creating a proper ForgottenPassword lookup with 3 tables joined

I’ve created a Password Forgotten page, I have the view, and the controller ready.

And I have a user class. This is completed so far.

The end-user will need to input 3 values on the Password Forget page.

Email address (found in user table)

Zipcode (found in residence table via, user->user_details>residence)

No of. occupants in residence (found also in residence)

The question is, how can I lookup that the user has inputed valid numbers.

A user, can have multiple residences, and these are linked via the user_details table.

How can I check if for the given input there is a valid user.id?

You can use the ‘exist’ validator - http://www.yiiframework.com/doc/guide/1.1/en/form.model#declaring-validation-rules

I am not after the validation stuff, I need to do this in the model with some Active Record stuff.

In plain SQL would be

select distinct user.id from user as u

join user_details as ud on u.id=ud.user_id

join residence as r on r.id=ud.residence_id

where user.email=_EMAIL and r.zip_code=_ZIPCODE and r.occupent_no=_OCCUPANTS

How do I join the 3 tables and pass the 3 params?

How do I get the matched user ids?

As you are new to the forum I can presume that you are new even to Yii… so If you haven’t done so… first read the “Definitive guide to Yii” to get the basics… there is a section explaining relations… and a section explaining validation and validators…

Cool. Thank you.

I’ve created the classes with the relations in place. All have the same name as the above mentioned tables in the query.

How do I execute a query, that references 3 columns from different tables, and pass the 3 params?

How do I use the distinct stuff in Yii?

Why do you think you are not after the validation stuff… as you wrote above… you have a form where a user can enter some data… that data should be validated so you need a validator… a custom one maybe that in turn will call a model function to check what you need…

I have the Form validation, but I don’t need to be validated when the form is submitted. I just need to get the userid, that will process further.

Can you help with my #5 message?

Can you post your relations?

I don’t understand this… what are you validating for the recovery form… what if someone enters wrong details and there are no IDs…

If someone enters wrong details and there is no ID, I simple redirect them to a page that says: If you have provided valid details we have dispatched a new password to you.

I want to do the query in post #3.

For class `user`

    public function relations() {

        return array(

            'residences' => array(self::MANY_MANY, 'residences', 'user_details(residence_id,user_id)'),

            'details' => array(self::HAS_ONE, 'user_details',

                'user_id'),

        );

    }

For class `user_details`

    public function relations() {

        return array(

            'user' => array(self::BELONGS_TO, 'user', 'user_id'),

            'residences' => array(self::HAS_ONE, 'residence',

                'residence_id'),

        );

    }

For class `residence`

    public function relations() {

        return array(

            'users' => array(self::MANY_MANY, 'user', 'user_details(residence_id,user_id)'),

            'heating_type' => array(self::HAS_ONE, 'heating_type',

                'heat_type_id'),

            'irrigation_type' => array(self::HAS_ONE, 'irrigation_type',

                'irrigation_type_id'),

        );

    }

I’ve managed to go till this:

 $users = user::model()->with('residence')->find(array(

                    'select' => 'user.id',

                    'condition' => 'email=:email and zip_code=:zipcode and occupant_no=:occupant_no',

                    'params' => array(':email' => $this->email, ':zipcode' => $this->zipcode, 'occupant_no' => $this->residents),

                ));

But now, how do I add the distinct user.id to it?

Just add ‘distinct’=>true - http://www.yiiframework.com/doc/api/1.1/CDbCriteria#distinct-detail

Note that in user relations you have ‘residences’ but in the call you use with(‘residence’)

That was slight typo, how do I add the distinct to it?

$users = user::model()->with('residence')->find(array(

                	'distinct'=>true,

                	'select' => 'user.id',

                	'condition' => 'email=:email and zip_code=:zipcode and occupant_no=:occupant_no',

                	'params' => array(':email' => $this->email, ':zipcode' => $this->zipcode, 'occupant_no' => $this->residents),

            	));