cakePHP paginate with left join causes incorrect number of pages

Question

Hi everyone,

I'm having an issue with cakePHP pagination, specifically when I do a left join. There are 6 rows returned, and with a pagination limit of 3, it should calculate 2 pages, but it calculates 3 pages, with the last page being blank. Here is the options array I'm passing to paginate:

Array
(
    [fields] => Array
        (
            [0] => DISTINCT Video.id, Video.thumbnail_img, Video.title, 
                   Video.description, Video.tags, Video.views, Video.date,
                   ((
                       SELECT COUNT(Rating.id) 
                       FROM ratings AS Rating 
                       WHERE Rating.rating = 1 AND Rating.video_id = Video.id
                    ) - 
                    (
                       SELECT COUNT(Rating.id) 
                       FROM ratings AS Rating 
                       WHERE Rating.rating = 0 AND Rating.video_id = Video.id
                    )
                   ) AS avgRating
        )

    [joins] => Array
        (
            [0] => Array
                (
                    [table] => ratings
                    [alias] => Rating
                    [type] => LEFT
                    [conditions] => Array
                        (
                            [0] => Rating.video_id = Video.id
                        )

                )

        )

    [order] => Array
        (
            [avgRating] => DESC
        )

)

Any ideas why this is happening? I'm not sure of any other way to get the results I want without using the join, but I'm betting that's what's causing the problem.

Any suggestions would be appreciated. Thanks!

Answer 1

Foolish problem - in this case the join isn't even necessary. The same results are achieved without the join, since the sub-queries can be evaluated on their own. For this example, it's ok, but there may be further examples where a join is necessary and the count error does persist. Either way, my problem is solved!