Doctrine : many to many with a date of assignation in the refclass

Question

residentSector:
        columns:
            id_resident_sector:
                type: integer
                primary: true
                autoincrement: true
            id_resident:
                type: integer(8)
            id:
                type: integer(8)
            date:
                type: timestamp 
residents:
        columns:
            id_resident:
                type: integer(8)
                primary: true
                autoincrement: true
            firstname:
                type: string(50)
            lastname:
                type: string(50)        
        relations:           
            Sectors:
                class: Sectors
                local: id_resident
                foreign: id
                refClass: residentSector 
Sectors:
        columns:
                id:
                     type: integer(4)
                     primary: true
                     autoincrement: true
                sector_name:
                      type: string(50)
                id_resp:
                      type: integer(4)
                visibility:
                      type: integer(1)

I want to select all the "residents" of a given "sector" at à given date (the lastest date as example).

My problem is the date field is in the refclass (because it's the date of assignation) so

->where('residents.Sectors.date =max(residents.Sectors.date) ')

won't work obviously because the refclass is not part of the collection ..

what is the good way to define the doctrine relation so i can get the latest sector in date of a resident ?

Thanks in advance !

Answer 1

Unfortunately Doctrine doesn't provide any built-in way to achieve what you're trying to do. However many-to-many relationship in fact uses double one-to-many relationship.

So what you need is to is:

1. Make correct DQL query:

   Doctrine_Query::create()
   ->from('Residents r')
   ->innerJoin('r.XXX rs WITH rs.date = ?', '08-04-2010')
   ->innerJoin('r.Sector s');
   

   Where XXX is the name of ResidentSector table. I'm not quite sure whether its name will be same as table name, but you can get its name from table definition generated by Doctrine.

2. After you execute the query you will have to do all the mapping by your own, using Doctrine_Record::mapValue.

Answer 2

I dont know how to use doctrine but having many to many relationships results in an abundance of duplicate data.

You should maybe have 3 tables (residents, sectors, residentsToSectors) have date assigned in the residentToSectors table and then grab the information for either resident or sector based on whatever data you insert in the WHERE clause?