CODEWITHSUNDEEP
phpmysqlcakephprelational-databasecakephp-model
dclowd9901
dclowd9901

Reputation: 6836

CakePHP not pulling rows over a certain number for hasOne relationship

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...

Upvotes: 2

Views: 285

Answers (4)

dclowd9901
dclowd9901

Reputation: 6836

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.

Upvotes: 2

talelcool
talelcool

Reputation: 423

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 :) .

Upvotes: 4

junwafu
junwafu

Reputation: 333

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.

Upvotes: 3

JJJ
JJJ

Reputation: 33153

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.

Upvotes: 1

Related Questions