Muhammad Umar Muhammad Umar - 6 days ago 6
MySQL Question

Cakephp modify Join query to add count instead of getting all details of a table

I am trying to get recent 10 videos along with their total views and likes and if i have liked the video or not.

Here is my code

$videos = $this->Video->find('all', array(
'joins' => array(
array(
'table' => 'video_views',
'alias' => 'views',
'type' => 'INNER',
'conditions' => array(
'views.userId = Video.id',
)
),
array(
'table' => 'video_likes',
'alias' => 'likes',
'type' => 'INNER',
'conditions' => array(
'likes.userId = Video.id',
)
)
),
'fields' => array('Video.*'),
'order' => array('Video.creationDate DESC'),
'limit' => 10,
'offset' => $offset * 10
));


For now , it is getting 10 recent videos, their likes data and views data.

However i want to modify the query to get count(*) instead of JOining all views/likes data.

something like video['viewsCount'] = xxx

Also secondly i want to know from likes table if i have liked that specific video

How can i modify this query

Thanks to @newbeeDev

$videos = $this->Video->find('all', array(
'fields' => array(
'Video.*',
'Count(View.id) as views',
'Count(Like.id) as likes',
'(CASE when Liked.id is not null then 1 else 0 end) as liked'
),
'joins' => array(
array(
'type' => 'LEFT',
'table' => 'video_views',
'alias' => 'View',
'conditions' => array('Video.id = View.videoId')
),
array(
'type' => 'LEFT',
'table' => 'video_likes',
'alias' => 'Like',
'conditions' => array('Video.id = Like.videoId')
),
array(
'type' => 'LEFT',
'table' => 'video_likes',
'alias' => 'Liked',
'conditions' => array('Video.id = Liked.videoId AND Liked.userId = ' . $userId)
),
),
'group' => array(
'Video.id'
),
'order' => array('Video.creationDate DESC'),
'limit' => 10,
'offset' => $offset * 10
));

Answer

enter image description here enter image description here enter image description here enter image description here

just replace your joins with

    'joins' => array(
            array(
                    'table' => 'video_views',
                    'alias' => 'View',
                    'conditions' => array('Video.id = View.video_id')
            ),
            array(
                    'type' => 'LEFT',
                    'table' => 'video_likes',
                    'alias' => 'Like',
                    'conditions' => array('Video.id = Like.video_id and Like.user_id = 1')
            ),
    ),

your query builder would be like this

         $videos = $this->Video->find('all', array(
            'fields' => array(
                    'Video.*',
                    'Count(View.id) as views',
                    '(CASE when Like.id is not null then 1 else 0 end) as liked'
            ),
            'joins' => array(
                    array(
                            'table' => 'video_views',
                            'alias' => 'View',
                            'conditions' => array('Video.id = View.video_id')
                    ),
                    array(
                            'type' => 'LEFT',
                            'table' => 'video_likes',
                            'alias' => 'Like',
                            'conditions' => array('Video.id = Like.video_id and Like.user_id = 1')
                    ),
            ),
            'group' => array(
                    'Video.id'
            ),
            'order' => array('Video.creationDate DESC'),
            'limit' => 10,
            'offset' => $offset * 10
    ));