Doctrine/Symfony - Multiple one-to-many relations

Question

Here is an extract of the schema I actually have

Software:
  columns:
    title:
      type: string(255)
    id_publisher:
      type: integer
    id_developper:
      type: integer

Company:
  columns:
    name:
      type: string(255)
    nationality:
      type: string(255)

As you can see, my Software model has two external references: publisher and developper. My whish would like to create a one to many relation for each of these two references. Problem is they are both companies.

I first tried something like shown below on my Software model, but the relation works only for the first local reference id_publisher.

relations:
  Company:
    type: one
    foreignType: many
    local: [id_publisher, id_developper]
    foreign: id

Then I tried that (always on the Software Model):

relations:
  Publisher:
    class: Company
    type: one
    foreignType: many
    local: id_publisher
    foreign: id
  Developper:
    class: Company
    type: one
    foreignType: many
    local: id_developper
    foreign: id

But when I execute a query which count the number of soft linked to a company...

public function findAllQuery(Doctrine_Query $q = null) {
    $q = Doctrine_Query::create()
                    ->select('c.*, COUNT(s.id) AS count_software')
                    ->from('Company c')
                    ->leftJoin('c.Software s')
                    ->groupBy('c.id');

    return $q;
}

...only publishers are take into account in the COUNT clause.

So finally, my question is, how to deal with multiple one-to-many relations of a same model ?? Thanks for your time !

Answer 1

maybe you should try to add a foreign alias to tell doctrine which relation to deal with when firing your query:

relations:
  Publisher:
    class: Company
    type: one
    foreignType: many
    foreignAlias: PublishedSoftware
    local: id_publisher
    foreign: id
  Developer:
    class: Company
    type: one
    foreignType: many
    foreignAlias: DevelopedSoftware
    local: id_developer
    foreign: id

And in your query you have to join both relations and sum the individual counts:

$q = Doctrine_Query::create()
     ->select('c.*, COUNT(ps.id)+COUNT(ds.id) AS count_software')
     ->from('Company c')
     ->leftJoin('c.PublishedSoftware ps')
     ->leftJoin('c.DevelopedSoftware ds')
     ->groupBy('c.id')
 ;

The doctrine default is to use the model name as identifier for the relation, so if using more then one relation to the same model you really should rename at least one to let doctrine now what you're meaning. Without this you won't be able to just retrieve the collection of published software like so:

$pubSoftware = $myCompany->getPublishedSoftware();
$devSoftware = $myCompany->getDevelopedSoftware();

What Doctrine is not capable of (IMHO) is to treat both relations to the same model as one. So a call:

$allSoftware = $myCompany->getSoftware();

won't retrieve all related software on a multi-relation model but only those which can be retrieved by the relation called Software.

Hope that helps,

~~~ Cheers.