doctrine findby relation

Question

I'm having trouble selecting a subset of data with doctrine.

I have 3 tables

Location Contact Contact_location

The contact and location tables hold a name and an id the other table holds only ID's. For instance:

Location
 loc_id: 1
 name: detroit
Contact
 contact_id: 1
 name: Mike
Contact_location
 loc_id: 1
 contact_id: 1

In doctrine there is a many to many relation between the location and contact tables with contact_location as the ref_class.

What i want to do is on my location page i want to find all contacts where for instance the loc_id = 1.

I tried:

 $this->installedbases = Doctrine::getTable('contact')->findByloc_id(1);

hoping doctrine would see the relation and get it, but it does not.

How can i make doctrine search in relevant related tables? I read it can be done using Findby but i find the documentation unclear.

Answer 1

Add a method on your table class:

class ContactTable extends Doctrine_Table
{
  public function findByLocationId($id)
  {
    return self::createQuery("c")
      ->innerJoin("c.Location l")
      ->where("l.loc_id = ?", $id)
      ->execute();
  }
}

then call it as follows:

$loc_id = 1;
$result = Doctrine::getTable("Contact")->findByLocationId($loc_id);

Doctrine should use the refclass to perform the inner join for you.

Answer 2

Change findByloc_id() to findByLocId(). The method is caught by magic __call().