DataMapper: include_related and join fields

[eluser]Dracos[/eluser]
I'm trying to get DM to include fields from a related model's relationship join table, but I can't get DM to include the join fields using $a->* methods.

The A to B relationship is 1-m, in that A::has_one('b') and B::has_many('A'). Also, based on other factors A may not have a related B.

For now I've worked around it with a raw query using $this->db->query(), because DM strips off the join field in _process_query():

Code:

SELECT
    `A`.*,
    `A_B`.`b_id` AS b_id,
    `A_B`.`status` AS b_status
FROM
    (`A`)
LEFT JOIN
    `A_B` A_B ON `A`.`id` = `A_B`.`A_id`
WHERE
    `A`.`foo_id` = ?
    AND
    [ nested clause groups filtering on other fields in A ]

I can get B.id using include_related(), but that's only half the issue. I can't get A_B.status no matter what I do; the where clauses seem to erase include_join_fields() somehow.

I thought maybe include_related() could get the join fields, but no luck so far. Also, no variations of $b->a->* methods that set up the same query seem to work. This is also complicated by the fact that there are status fields in A, B, and A_B, but I can easily rename A_B.status.

[eluser]WanWizard[/eluser]
You don't include the DM code you tried, so I can't exactly tell you what goes wrong.

Join fields are always a challenge in more complex environments.

In this case, since there's a has_one from A to B, normalisation would tell you not to use a relationship table, but store the columns in A.

But if you want/need it like this, what I normally do is dump the relationship table, and create a normal table with a model for it, so you get A -> has_one -> AB -> has_many B.

Then you can easily do

Code:

$a->include_related('AB')->include_related('AB/B')->get();

then you'll get all fields back, prefixed with the name of the relation.