[eluser]frith[/eluser]
@chromice
Ok now I've got a real question.
I'm having trouble with some join relationships
I have the table users which has the following fields
id
email
password
and profiles which has
id
user_id
first_name
....
What I'd like to do is retrieve a user by id or by email while joining profiles
I've tried the following code
$user = $this->user->join($this->profile)->find_by_id(2,RETURN_NEW,ACTIVE_RECORD);
but I get the an sql error:
Unknown column 'users.profile_id' in 'on clause'
SELECT users.*, user_profiles.user_id AS profile_user_id, user_profiles.first_name AS profile_first_name FROM users LEFT JOIN profiles AS user_profiles ON
users.profile_id = user_profiles.id WHERE users.id = 2
The correct sql I'd like it to produce is
SELECT users.*, user_profiles.user_id AS profile_user_id, user_profiles.first_name AS profile_first_name FROM users LEFT JOIN profiles AS user_profiles ON
users.id = user_profiles.user_id WHERE users.id = 2
I can find by profile joining users and it works fine because profile has the user_id but that's not my requirement
I've tried changing the relationship statements in the profile and user models to every combination of _has_one and _belongs_to but it doesn't seem to make a difference.
Am I missing something. Can you only join through a child object?
Thanks