Setting up models with left joined tables in PHPActiveRecord

Question

I'm trying to figure out how to model the following in PHPActiveRecord, but am getting stuck and wanted to see if someone could help me out. I'll use a simplified example:

Table List:

atable (id [PK], ...)
btable (id [PK], ...)
ctable (atable_id [FK], btable_id [FK], statuscode)

The foreign keys on ctable are a composite primary key, therefore, there is only one instance of a given atable_id and btable_id in the table.

I'd like to be able to left join atable with ctable and btable to get at the statuscode column. So a sample SQL would look like this:

SELECT     a.id, b.id, c.statuscode
FROM       atable a
LEFT JOIN  ctable c on c.atable_id=a.id 
LEFT JOIN  btable b on c.btable_id=b.id

How would I setup my 3 models (atable, btable, ctable) to be able to get at the above information?

Thanks.

Answer 1

As far as I know, phpactiverecord does not support composite primary keys, so for some functionality you might want to define a seperate key anyway.

Further: the statuscolumn would not be unique in this case? There could be multiple btable_id entries for your atable_id, so there would not be a single hit on that -> this is not something that has much to do with creating the objects?

Apart from that, creating the objects shouldn't be too difficult You'd have relations that reflect the connection between ctable.atable_id and atable.id (and as well for btable obviously).

one ctable entry (object, model) 'belongs' to an atable and a btable, so you can just define a belongs-to relation for those two.
The other way around, an atable row (object, model) can have multiple ctable rows, so define that relation as hasmany.

to get from an atable to the status you could then do

foreach ($atable->ctables as $ctable) {
    echo $ctable->btable->id.' '.$ctable->statuscode;
}