Selecting all parent users and their related children

Question

Ok, I need a MySQL pro.

The setting:

• Hundreds of users
• User can be a child of other users, through relation field 'myParents'
• I need a list of users, and their child(ren), related to an entry (through relation field belongsToGroups)

I don't want to use $criteria->find(), because it returns objects, which makes it terribly slow. So, I want to create a db query. What I have now, is this:

$parentsWithChildren = craft()->db->createCommand()
        ->select('u.id userId, u.firstName parentFirstName, u.lastName parentLastName, c.firstName childFirstName, c.lastName childLastName')
        ->from('users u')
        ->join('relations r', 'u.id = r.sourceId')
        ->join('relations uc', 'u.id = uc.targetId')
        ->join('usergroups_users g', 'u.id = g.userId')
        ->join('users c', 'uc.sourceId = c.id')
        ->where('r.targetId = '.$elementId)
        ->andWhere('uc.fieldId = 92')
        ->andWhere('g.groupId != 5')
        ->order('u.id')
        ->queryAll();

This return a nice list of all users, children included, but only if a user has children. In short: I also need the users without children. Any SQL guru willing to help me out, it's been a long day ;)

Answer 1

One option would be to use leftJoin() instead of join(), which will still return the record if the joining table row doesn't exist. The values will be joined as NULL.

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

http://www.w3schools.com/sql/sql_join_left.asp

If you needed more control, you could always incorporate some aggregate functions in SQL to do a count an the records found, and you could perform additional conditional statements with the HAVING, instead of WHERE (which won't work) since aggregate data isn't stored in the db.