Lots of queries to pull up user photos and their comments. (CakePHP)

Question

What I'm trying to do is pull up profile info w/ the profile's comments. I get everything as expected. No error returned, array is formatted perfectly. My concern is the queries that are run. It runs one query for each ID to get its photo (the start of the query is noted in comments in the code below). I guess this is the only way to get photos by user_id? All in separate queries? Is there a better way?

I'm picturing a profile with 40+ comments and it's scary to imagine. Is this where memcached comes in?

$profile = $this->Profile->find('first', array(
                            'conditions' => array('Profile.url' => $url),
                            'contain' => array(
                                    'User' => array(
                                            'fields' => array(
                                                    'User.id','User.name'
                                            ),
                                            'Photo' => array(
                                                    'fields' => array(
                                                            'Photo.thumbnail','Photo.image'
                                                    )
                                            )
                                    ),
                                    'Comment' => array(
                        'User' => array(
                            'fields' => array(
                                'User.name'
                            ),
                            'Photo' => array( // right here
                                'fields' => array(
                                    'Photo.thumbnail'
                                )
                            )
                        )
                    )
                            )
                    )); 

edit:

I obviously have a User table, as well as a Comment table. I also have a Photo table that stores URL's of the users images, foreign key = user_id. So while i query all Comments from a specific profile and it's Comment.user_id, I also need to grab the Photo.thumbnail from the Photo table by its user_id.

Answer 1

You can make it much faster using a LEFT JOIN.

edited code has been edited to better respond to the question (two left joins are needed - not one).

SELECT c.text, i.profiles, p.photo_url
FROM comments AS c
LEFT JOIN profiles AS i on c.profile_id = i.id
LEFT JOIN photos AS p ON i.id = p.user_id
WHERE c.post_id = 32
LIMIT 20

This is just a generic SQL but would:

1. pull all comments (in the comments table) bellowing to article 32
2. left join the profiles table matching the comment poster id to the comment
3. pull the first 20 comments

And with only one query you would pull all 30 comments + their associated profiles.

Hope it helps!

Answer 2

You can replace the array key 'contain' with the array key 'link', and it builds the left joins for you - see linkable behavior. The downside is that all relations linked must be from the hasOne or hasMany to the belongsTo relation.

Answer 3

You might also want to look at Nate's article in the Bakery about doing joins "the cake way".

http://bakery.cakephp.org/articles/view/quick-tip-doing-ad-hoc-joins-in-model-find