Ok I'm making my own sessions and user code because the provided ones are overkill for my purposes.
Model_User has many Model_Session
Model_Session belongs to Model_User
If I run this code, it works fine:
$session = Model_Session::find_by_session($id);
The session is loaded from the database without any issues.
If I pre-load the user then it fails every time and returns no results:
$session = Model_Session::find_by_session($id, array('related' => 'user'));
$id is identical both times.
The generated query for the second one is this:
SELECT `t0`.`session` AS `t0_c0`, `t0`.`user_id` AS `t0_c1`, `t0`.`flash` AS `t0_c2`, `t0`.`created_at` AS `t0_c3`, `t0`.`updated_at` AS `t0_c4`, `t0`.`id` AS `t0_c5`, `t1`.`username` AS `t1_c0`, `t1`.`password` AS `t1_c1`, `t1`.`email` AS `t1_c2`, `t1`.`first_name` AS `t1_c3`, `t1`.`last_name` AS `t1_c4`, `t1`.`group` AS `t1_c5`, `t1`.`pricingtier_id` AS `t1_c6`, `t1`.`created_at` AS `t1_c7`, `t1`.`updated_at` AS `t1_c8`, `t1`.`id` AS `t1_c9` FROM (SELECT `t0`.`session`, `t0`.`user_id`, `t0`.`flash`, `t0`.`created_at`, `t0`.`updated_at`, `t0`.`id` FROM `sessions` AS `t0` ORDER BY `t0`.`id` ASC LIMIT 1) AS `t0` LEFT JOIN `users` AS `t1` ON (`t0`.`user_id` = `t1`.`id`) WHERE (`t0`.`session` = '4WRKxb0PvzFcLZ1A') ORDER BY `t0`.`id` ASC
The problem arises in the FROM. You get "LIMIT 1" at the end of the session table's SELECT.
In the main query, the session id is looked for with `t0`.`session` = '4WRKxb0PvzFcLZ1A'
I have two rows in the sessions table, and I am trying to fetch the second one, but the LIMIT 1 means only the first one is looked at to see if the session matches.
Am I doing something wrong? Where on earth is that LIMIT 1 coming from?
You rewite sessions using ORM because the existing ones are "overkill"? Ah, well...
A find() is always limited to one, with one exception: find('all'). The limit is added because you create a join, which can potentionally result in multiple records.
If you want another user record to be found, you could add an order_by or a where to the find to limit the user records being found. Or use Model_Session::query()-> and chain the methods on it to get a multi-record result.
Yeah I don't actually need a lot of the features and since 2 models and a base controller are so simple its easier for me to knock out a simpler one.
I suppose my main question is why the WHERE clause is outside the sub query and the limit 1 is inside it. If both were inside the sub query I'd get exactly the result I was looking for.
This has been explained a million times.
The ORM is NOT a query builder. it's an ORM. One of it's jobs is to guard the integrity of the data. If you limit on the ourside you get incomplete results (if your related table contains more records then the limit). Since the ORM caches results, it means you can no longer access any missing data, since the ORM finds the key in the cache and assumes the cache contains everything.
If you really want to limit on the outside, use rows_limit()/rows_offset() instead.
No you misunderstand me.
I don't want the LIMIT 1 on the outside. I can see it's purpose.
I want the relevant WHERE clause to be on the inside with the LIMIT 1 so I get the row I'm looking for and not the first row in the table.
Looking again at the query you posted, I noticed it doesn't reference 'users' at all, but 'sessions' twice. But your ORM query does. The subquery should contain a subquery on users, because that's what you relate to.
Something wrong with the relationship definition?
Donate
