如何返回所有行，即使它们在连接表中没有行

Question

I have three tables, Users, Profiles and Requests. Only users that have created requests, will have a row in the requests table. But I still need to get all the users back and display the total number of requests, per user. I have worked out the query below, which only returns the users who have a record in the requests table, although I need all users and for it to return 0 for the users requests, if a matching row is not found.

 public function getAllUsers()
    {
        $select = $this->userRepository->select();
        $select->setIntegrityCheck(false)
               ->from('users', array('id', 'username', 'date_created', 'active', 'last_login', 'role'))
               ->join('profiles', 'users.id = profiles.user_id', array('profile_id' => 'id', 'first_name', 'last_name', 'email', 'avatar', 'on_mailing_list'))
               ->join('recommendation_requests', 'users.id = recommendation_requests.user_id', array('requests' => 'count(*)'))
               ->order('users.date_created ASC');
        return $this->userRepository->getAdapter()->fetchAll($select);
    }

What would I need to change so that it returns all users, even if they don't have any requests and returns 0 for the requests column, not the total number of requests. I've tried joinLeft, joinRight, joinInner....I'm not a SQL guy so I'm a little stumped.

Answer 1

Firstly to list all users use OUTER JOIN e.g. LEFT JOIN

Secondly change

COUNT(*)

to

COUNT(recommendation_requests.user_id)

Because you're using an OUTER JOIN you always have at least one row in your resultset for each user and that is why COUNT(*) gives 1 instead of 0 for users that don't have any requests. Instead you need to count values of recommendation_requests.user_id.