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.
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.