Diff of two Doctrine_Collection instances
I have a collection of users who completed a certain test and a list of users who got an invite for that test. Now I'd like to fetch all the users who did not complete the test. I thought it'd be simple to diff the two collections (like arrays), but only Doctrine_Collection::merge()
is possible.
My datamodel (much left out for clarity):
Invite:
columns:
id: integer (10)
relations:
users:
foreignAlias: invites
class: User
refClass: UserInvite
UserInvite:
columns:
user_id: integer (10)
invite_id: integer (10)
r开发者_如何学Celations:
user:
class: User
foreignAlias: userInvite
invite:
class: Invite
foreignAlias: userInvite
Test:
columns:
id: integer (10)
user_id: integer (10)
invite_id: integer (10)
relations:
user:
class: User
foreignAlias: tests
invite:
class: Invite
foreignAlias: tests
Now these two collections are available to me:
$invite = new Invite;
$invite = $invite->users; // All the users who got an invite
$invite = $invite->tests; // All the tests performed for this invite
What's the best method to get all the users? I can perform an SQL query, but I't like to do this in OOP php or else a DQL query. In SQL, I can do something like this:
SELECT u.name, u.id
FROM user u
LEFT JOIN userinvite i
ON i.user_id = u.id
LEFT JOIN test t
ON t.user_id = u.id
WHERE i.id IS NOT NULL
AND t.id IS NULL
Okay, I now know I had to approach this problem via the User model itself. This DQL query fetches all the users who miss their test from a specific invite:
$user = new User;
$users = $user->getTable()
->getQueryObject()
->leftJoin('User.userInvite i')
->leftJoin('User.tests t')
->where('i.invite_id = ?', $invite->id)
->andWhere('i.user_id IS NOT NULL')
->andWhere('t.user_id IS NULL')
->execute();
精彩评论