Ok, I need a MySQL pro.
- 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
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 ;)