CODEWITHSUNDEEP
phpmysqlcakephp
Muhammad Umar
Muhammad Umar

Reputation: 11782

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
        ));

Upvotes: 2

Views: 444

Answers (1)

Beginner
Beginner

Reputation: 4153

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
    ));

Upvotes: 2

Related Questions