RE: Can we have one SQL query producing two CDBI classes

[prev] [thread] [next] [Date index for 2005/04/21]

I found this in the list archives. Any more input from someone who has
tried to do this, on how robust this is, or how to involve
not-really-a-cache, would be appreciated.

On Mon, Sep 13, 2004 at 10:22:37PM -0400, Perrin Harkins wrote:=20
> Andrew Pimlott wrote:=20
>=20
> >One situation in which I think Class::DBI may be lacking is when you=20=

> >need to access a large number of database entities, so many that you=20=

> >can't afford to load them on demand. I'd like to write a big query=20
> >that joins many tables, then use the results of the query to populate

> >many Class::DBI objects of various types, as well as all of the=20
> >relationships between them (so I never have to go back to the=20
> >database to use these objects). For bonus points, it should also be=20=

> >possible to force fetching the rows of this big query as needed,=20
> >instead of all at once.=20
>=20
> The ability to construct objects from data that you fetched on your=20
> own=20
> is already there, in the form of the method "construct".=20

And here's an example you posted yourself a while ago:=20

sub retrieve_with_bar {=20

    my ($class, $id) =3D @_;
    my $sth =3D $class->sq_get_both();
    $sth->execute($id);
    my $row =3D $sth->fetchrow_arrayref();
    my $foo =3D Foo->construct({
                              id    =3D> $row->[0],
                              other =3D> $row->[1],
                             });
    my $bar =3D Bar->construct({
                              id    =3D> $row->[2],
                              other =3D> $row->[3],
                             });
    $foo->{'_bar_object'} =3D $bar;
    return $foo;

}=20

> The tricky=20
> part would be making the relationship accessors use an in-memory cache

> instead of the database. I would suggest doing that part by writing=20
> your own relationship type that looks at a cache first and then calls=20=

> the normal has_many relationship code if it doesn't find anything.=20

Umm. Seems like a bug/limitation of the caching code that construct()
isn't integrated with it in some way. Currently all uses of
sth_to_objects() don't consider the cache at all.=20

Tim.=20

> -----Original Message-----
> From: Ian McDonald-ONLINE=20
> Sent: 21 April 2005 19:34
> To: cdbi-talk@xxxxxx.xxxxx.xxx
> Subject: Can we have one SQL query producing two CDBI classes
>=20
>=20
> Hi,
>=20
> I've previously tried to do something of the form:
>=20
> SingersClass->set_sql(
> 	both_persons_and_singers =3D> 'select * from PERSONS, SINGERS
> 	where SINGERS.PERSON_ID =3D PERSONS.ID'
> );
>=20
> My $sth =3D SingersClass->sql_both_persons_and_singers;
> $sth->execute();
>=20
> @persons =3D PersonsClass->sth_to_objects($sth);
> @singers =3D SingesrClass->sth_to_objects($sth);
>=20
> It didn't work, but I haven't got into the nitty-gritty of=20
> debugging it.
>=20
>=20
> My only question is: would you expect it to work?=20
>=20
> Thanks,
>=20
> Ian
>=20
> --
> Dr Ian McDonald
> Software Engineer, Interactive Drama & Entertainment
> A300, 56-58 Wood Lane (CH), London W12 7SB
> 020 822 56162
> ian.mcdonald@xxx.xx.xx / "Ian McDonald-ONLINE" (not "Ian MacDonald")
>=20
>=20
>=20
http://www.bbc.co.uk/

This e-mail (and any attachments) is confidential and may contain
personal views which are not the views of the BBC unless specifically
stated. If you have received it in error, please delete it from your
system.=20
Do not use, copy or disclose the information in any way nor act in
reliance on it and notify the sender immediately. Please note that the
BBC monitors e-mails sent or received.=20
Further communication will signify your consent to this.

http://www.bbc.co.uk/

This e-mail (and any attachments) is confidential and may contain
personal views which are not the views of the BBC unless specifically
stated.
If you have received it in error, please delete it from your system.=20
Do not use, copy or disclose the information in any way nor act in
reliance on it and notify the sender immediately. Please note that the
BBC monitors e-mails sent or received.=20
Further communication will signify your consent to this.