How to check if two records have a self-referencing relation?

Question

Consider the following schema with users and their collegues (friends):

Users

User:
  columns:
    user_id:
      name: user_id as userId
      type: integer(8)
      unsigned: 1
      primary: true
      autoincrement: true
    first_name:
      name: first_name as firstName
      type: string(45)
      notnull: true
    last_name:
      name: last_name as lastName
      type: string(45)
      notnull: true
    email:
      type: string(45)
      notnull: true
      unique: true
  relations:
    Collegues:
      class: User
      local: invitor
      foreign: invitee
      refClass: CollegueStatus
      equal: true
      onDelete: CASCADE
      onUpdate: CASCADE

Join table:

CollegueStatus:
  columns:
    invitor:
      type: integer(8)
      unsigned: 1
      primary: true
    invitee:
      type: integer(8)
      unsigned: 1
      primary: true
    status:
      type: enum(8)
      values: [pending, accepted, denied]
      default: pending
      notnull: true

Now, let's say I two records, one for the user making a HTTP request (the logged in user), and one record for a user he wants to send a message to. I want to check if these users are collegues.

Questions:

1. Does Doctrine have any pre-build functionality to check if two records with with self-relations are related?
2. If not, how would you write a method to check this?
3. Where would you put said method? (In the User-class, UserTable-class etc)

I could probably do something like this:

public function areCollegues (User $user1, User $user2) {
    // Ensure we load collegues if $user1 was fetched with DQL that
    // doesn't load this relation
    $collegues = $user1->get('Collegues');
    $areCollegues = false;
    foreach($collegues as $collegue) {
       if($collegue['userId'] === $user2['userId']) {
          $areCollegues = true;
          break;
       }
    }
    return $areCollegues;
}

But this looks a neither efficient nor pretty. I just feel that it should be solved already for self-referencing relations to be nice to use.

EDIT:

Given that we set the following in UserTable::construct():

$this->setAttribute(Doctrine::ATTR_COLL_KEY, 'userId');

Then the above method could probably be written like:

public function areCollegues (User $user1, User $user2) {
    // Ensure we load collegues if $user1 was fetched with DQL that
    // doesn't load this relation
    $collegues = $user1->get('Collegues');
    return isset($collegues[$user2['userId']);
}

This should be more efficient than the first suggested method, but it still needs to fetch (and hydrate) more records than I would like.

Answer 1

Maybe this isn't what you're after but why not just query the table? It's a lot more efficient:

$john = // invitor id
$lisa = // invited id
$q = Doctrine_Query::create()
    ->select('c.invitor')
    ->from('CollegueStatus c')
    ->where('c.invitor = ? AND c.invited = ? OR c.invitor = ? AND c.invited = ?, array($john, $lisa, $lisa, $john))
    ->limit(1);
$result = $q->execute(array(), Doctrine_Core::HYDRATE_NONE);
if (!empty($result)) echo 'they are colleagues';

I've assumed a user can be either "invited" or the "invitor", hence the AND OR AND construct.

And you can put this inside a method anywhere you want.