CakePHP not pulling rows over a certain number for hasOne relationship

Question

I have a client with a database that has several sizeable and interrelational models. We've come across an odd "bug" in a $hasOne relationship we're producing. The problem is this:

The model UsersItems has a $hasOne relationship with Users. It is as follows

var $hasOne = array(
    'Author' => array(
        'className' => 'User',
        'foreignKey' => 'id'
    )
);

This pulls and joins user data with the alias "Author" for these items (which also belong to Users in the context of the users having selected them for "bookmarking").

If the UserItem has an id of, say, 3445 (more middle of the range of the current size of the table), it pulls through just fine.

However, if a UserItem has, say an id of 5000 (the upper range of the table's current size), that $hasOne fails to pull in any information from a find(). Consequently, it would seem newer "bookmarked" items do not retrieve Author data, while others ("older") do just fine.

What would be causing this?

ADDED INFORMATION

The query is as follows:

SELECT `UsersItem`.`id`, `UsersItem`.`user_id`, `UsersItem`.`item_id`,
       `UsersItem`.`in_list`, `User`.`id`, `User`.`username`, `User`.`password`,
       `User`.`email`, `User`.`group_id`, `User`.`resethash`, `User`.`confirmhash`,
       `User`.`confirmed`, `Item`.`id`, `Item`.`user_id`, `Item`.`name`,
       `Item`.`category_id`, `Item`.`description`, `Item`.`pagelink`,
       `Item`.`purchaselink`, `Item`.`moderated`, `Item`.`image_filename`,
       `Item`.`votecount`, `Item`.`parent_id`, `Item`.`discover_order`,
       `Item`.`created`, `Item`.`slug`, `Item`.`normalized_name`,
       ((`Item`.`votecount`)/pow((3600*TIMEDIFF(`Item`.`created`, NOW()) + 12), .42)) AS `Item__rank`,
       `Author`.`id`, `Author`.`username`, `Author`.`password`, `Author`.`email`,
       `Author`.`group_id`, `Author`.`resethash`, `Author`.`confirmhash`,
       `Author`.`confirmed`
FROM `users_items` AS `UsersItem`
LEFT JOIN `users` AS `User` ON (`UsersItem`.`user_id` = `User`.`id`)
LEFT JOIN `items` AS `Item` ON (`UsersItem`.`item_id` = `Item`.`id`)
LEFT JOIN `users` AS `Author` ON (`Author`.`id` = `UsersItem`.`id`)
WHERE `UsersItem`.`user_id` = 1118
  AND `UsersItem`.`in_list` = 1
ORDER BY `UsersItem`.`id` DESC

SECOND ADDENDUM

This query does work:

SELECT `UsersItem`.`id`, `UsersItem`.`user_id`, `UsersItem`.`item_id`, `UsersItem`.`in_list`, `User`.`id`, `User`.`username`, `User`.`password`, `User`.`email`, `User`.`group_id`, `User`.`resethash`, `User`.`confirmhash`, `User`.`confirmed`, `Item`.`id`, `Item`.`user_id`, `Item`.`name`, `Item`.`category_id`, `Item`.`description`, `Item`.`pagelink`, `Item`.`purchaselink`, `Item`.`moderated`, `Item`.`image_filename`, `Item`.`yeekcount`, `Item`.`parent_id`, `Item`.`discover_order`, `Item`.`created`, `Item`.`slug`, `Item`.`normalized_name`, ((`Item`.`yeekcount`)/pow((3600*TIMEDIFF(`Item`.`created`, NOW()) + 12), .42)) AS `Item__rank`, `Author`.`id`, `Author`.`username`, `Author`.`password`, `Author`.`email`, `Author`.`group_id`, `Author`.`resethash`, `Author`.`confirmhash`, `Author`.`confirmed` FROM `users_items` AS `UsersItem` LEFT JOIN `users` AS `User` ON (`UsersItem`.`user_id` = `User`.`id`) LEFT JOIN `items` AS `Item` ON (`UsersItem`.`item_id` = `Item`.`id`) LEFT JOIN `users` AS `Author` ON (`Author`.`id` = `Item`.`user_id`) WHERE `UsersItem`.`user_id` = 1118 AND `UsersItem`.`in_list` = 1 ORDER BY `UsersItem`.`id` DESC

Note the difference in this particular bit:

LEFT JOIN `users` AS `Author` ON (`Author`.`id` = `Item`.`user_id`)

I was pulling the wrong id as the Author id previously, so I know that was the problem; now I'm trying to figure out how to get Cake to generate this correct query...

Does this query work if you execute it manually on the database? Is there an entry in `users` which matches `UsersItem.id`? — deceze, May 01 '11 at 00:04
The query works no better if executed manually on the database. The same items come back with the missing information pertaining to the Author. There does not currently exist a corresponding entry in Users that would match UsersItem.id. I think I see where the answer is headed... — dclowd9901, May 03 '11 at 04:20

score 4 · Answer 1 · edited Jun 20 '20 at 09:12

Maybe you should switch the Author relation to belongsTo instead of hasOne in your item Model like this :

var $BelongsTo= array(
'Author' = array(
    'className' => 'User',
    'foreignKey' => 'user_id');

the book explain it very well , foreign key in belongsTo relation is what you need :

foreignKey: the name of the foreign key found in the current model. This is especially handy if you need to define multiple belongsTo relationships. The default value for this key is the underscored, singular name of the other model, suffixed with ‘_id’.

Hope I helped :) .

score 3 · Answer 2 · answered May 04 '11 at 09:24

3

If I understood your query correctly, your association should have been like this:

On UserItem model:

var $belongsTo= array(
    'User', 'Item'
);

On Item model:

var $hasMany = array(
    'UserItem'
);

var $hasOne = array(
    'Author' = array(
        'className' => 'User',
        'foreignKey' => 'id'
);

I hope this helps.

answered May 04 '11 at 09:24

junwafu

333
2
7

How do you query your model ? using Containable behavior ? can we get the find query you are executing? – talelcool May 05 '11 at 08:33
this is not tested. i just typed this while thinking about it. – junwafu May 05 '11 at 09:07

dclowd9901 · Accepted Answer · 2011-05-11T03:57:21.503

None of the answers here worked out for me. For better or worse, what I ended up doing was creating a virtual field that would populate the value of Author's name.

The problem with the relationships model is they only apply when you're calling through the model in question. In my case, there isn't (and should not be) any relationship between the UsersItems table and the name of the person who Authored the item in question.

Virtual tables are called in every instance of that model's appearance. That means that, even in the UsersItems->Items relationship, Items will populate its virtual fields.

What I ended up creating in the item.php model file looked a little something like:

var $virtualFields = array(
    'author' => 'SELECT `username` FROM `users` WHERE `id = Item.user_id`'
);

I'll wait to hear back from others as to whether or not this is a suitable solution to this problem, but it's the only one so far that has worked for me. Because there is no associated author_id column in the UsersItems table, I just don't see how it'd be possible to relate the two tables.

I think you were looking for something like this - http://bakery.cakephp.org/articles/eldonbite/2008/09/18/subclass-behavior — Rifat, May 07 '11 at 16:42

score 1 · Answer 4 · answered Apr 28 '11 at 10:42

My wild guess is that at some point the ids of Author and UserItem have gone out of sync i.e. Author id 3445 hasOne UserItem id 3445 but Author id 5000 hasOne UserItem 5001 or something, and the query tries to fetch the id of a wrong table. But without seeing the generated queries it's hard to say.

CakePHP not pulling rows over a certain number for hasOne relationship

4 Answers4