When selecting via a join, is it possible to pass back all the linked sub-records rather than only those selected by the join?

[jfrm]

This has caught me out several times over the years and always seems to waste an inordinate amount of time debugging.  (You'd have thought I'd have learnt by now).

Simple scenario e.g. table of 'kings' with a one-to-many relationship to table of 'wives'

my @query = (   'wives.name' => 'Jane'   );

my $kings = DB::King::Manager->get_kings(query => \@query, with_objects => 'wives');

might return a number of kings records, one of which is 'Henry VIII' but it will ONLY come with the one wife record attached - that of 'Jane Seymour'.

What I often want is a way for each top-level record, to come back with all sub-records.  So I would like the 'Henry VIII' record to come back with the full complement of 6 Queen records.

I realise I can omit the query and then iterate through picking out what I want long-windedly.  My question is, is there a Rose construct that will only return Kings with a Queen named Jane but will return all the Queens attached to any such King?

many thanks for any help and all hail to the inordinate usefulness of Rose::DB.

cheers,

James.

[Jeremy Begg]

Hi James,

I don't know that what you ask is possible in a single query (but I'm happy to be proven wrong).

If you had a mapping table sitting between the King and Wife tables you could do something like this:

my @query = ('wives.name' => 'Jane');

my $kings = DB::King::Manager->get_kings(query => \@query);

if ($kings && @$kings) {

    my $wives = $king->wives;

    foreach my $wife (@$wives) {

        ....

    }

}

So you still get only those Kings which have a wife called Jane, and you get to see all the wives for each of those Kings.

It's just two steps.

Regards,

Jeremy Begg

[John Siracusa]

You can do this with a subselect, if your database supports it. Here's an example using PostgreSQL. Database contents:

test=> select * from kings join wives on (wives.king_id = kings.id) order by kings.name, wives.name;

 id | name | id | name | king_id 

----+------+----+------+---------

  1 | Alex |  1 | Ann  |       1

  1 | Alex |  2 | Bee  |       1

  1 | Alex |  3 | Jane |       1

  2 | Bob  |  4 | Sue  |       2

  3 | Carl |  5 | Jane |       3

  3 | Carl |  6 | Jen  |       3

  4 | Dave |  7 | May  |       4

(7 rows)

Code:

my $kings =
    King::Manager->get_kings(
        require_objects => 'wives',
        query => [
            id => [ \q(select king_id from wives where name = 'Jane') ],
        ],
);

foreach my $king (@$kings) {
    print $king->name, "'s wives: ", join(', ', map { $_->name } $king->wives), "\n";
}

Result:

Alex's wives: Ann, Bee, Jane

Carl's wives: Jane, Jen

-John

--
Source: https://github.com/siracusa/rose
CPAN: http://search.cpan.org/dist/Rose-DB-Object
---
You received this message because you are subscribed to the Google Groups "Rose::DB::Object" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rose-db-objec...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/rose-db-object/1911c34c-37eb-42d3-84e3-ea75a0aa42d1n%40googlegroups.com.