[CDBI] Making Class::DBI objects from a search_ method result?

Steven Mackenzie steven.mackenzie at aptile.co.uk
Mon Oct 24 16:41:39 BST 2005


Matt S Trout said:
> use base qw/Class::DBI::Sweet/;
> 
> my @persons = Person->search({ 'artistgroup.artist.cd' => $cd });

OK, so that's given me a class method "search" to find the rows given a
CD. And I guess it uses the has_a relations to get the right set of
persons, rather than the has many relations I was planning to use to go
the other way.

So the
use base qw/Class::DBI::Sweet/;
is in the Person package, or can I stick it anywhere?

I think that does exactly what I want, but not the way I expected to do
it ...

> unless you've got a many-many in the middle of there, in which case you
> probably want to fetch the many-many table and prefetch the person objects.

No, I'm normal in that respect.

I planned to use the set_sql approach for better performance for this
lookup - a single hit on the DB. Am I right about that?

I saw some discussion of sth_to_objects a few days back, but I'm still
not sure of the usage. Is that something I could use in this situation?

Answering my own question with a RTFM, I think I need to digest
http://cdbi.dcmanaged.com/wiki/Using_joins
and
http://cdbi.dcmanaged.com/wiki/Directly_execute_SQL

Steven

===

On Mon, Oct 24, 2005 at 03:13:43PM +0100, Steven Mackenzie wrote:

>> Hello,
>>
>> I would like to add a convenience method to once of my Class::DBI
>> objects, so that I can get a collection of related CDBI instance objects
>> via a JOIN, rather than by following has_many relations.
>>
>> >From looking at the doc
>> http://search.cpan.org/dist/Class-DBI/lib/Class/DBI.pm#has_a
>> and
>>
http://cdbi.dcmanaged.com/wiki/Beginners_guide#Entity_Relationship_Diagram_.28ERD.29
>> I think something like this should be possible:
>>
>>  # add a sub to get all persons in the band that created a CD; assumes
>>  # that the current CD was created by a band!
>>  CD->set_sql
>>   ( persons =>
>>     qq{
>>         SELECT p.*
>>         FROM CD cd
>>           JOIN artist a on cd.artistid = a.artistid
>>           JOIN artistgroup ag on a.artistid = ag.bandartistid
>>           JOIN person p on ag.personartistid = p.artistid
>>         WHERE cd = $self.cdid
>>       }
>>   );
>>
>>   # ... elsewhere ...
>>   my @persons = $cd->search_persons();
>>
>> The array @persons is now an array of hashes.  I'd rather it was an
>> array of PERSON rows though. Is there a nice way to do that?




More information about the ClassDBI mailing list