Class::DBI method sth_to

ryantate has asked for the wisdom of the Perl Monks concerning the following question:

Where might I find out more about the Class::DBI method ->sth_to_objects? Or if there is no documentation out there, does anyone know how it works, and whether it can be used to create two objects, each of a different class?

The method is referenced in the Class::DBI docs and in a Cookbook recipe on the Class::DBI wiki as the method to call at the end of custom SQL queries. But I couldn't find documentation for it in any of the Class::DBI modules on CPAN or on the wiki or in a Google search.

The context is that I am looking at replacing chunks of my hand-rolled persistence abstraction layer with Class::DBI, but before I try that I want to find out as much as I can about how easy and powerful C::DBI can or cannot be when one drops down to hand-crafted SQL. I do a lot of JOINs, sometimes with more than two tables.

I came across this function when researching the first specific question I am trying to answer, which is how I could use Class::DBI to construct two distinct objects out of a single JOIN. Given a particular cookie ID, I want to join the appropriate cookie row with the approriate row from the user table, create one cookie and one user object (pretty simple stuff).

Right now, in my ::Cookie class, I override the ->load method in my hand-rolled persistence class but can still use my persistence class -- first to initialize the objects and then to turn the row returned by the DB into objects (through two other methods, which happen to me named ->load_init and ->load_import). The persistence class also provides me with methods to pull out a list of default fields for each class and the name of the tables for each class so I can use this information (if needed) in crafting the SQL query.

I am worried if I adopt Class:DBI, I won't be able to get this much support around my custom SQL queries. Poking around through the documentation and the wiki, I'm not sure there's even a way to get a list of columns out of Class::DBI to use in my custom SQL query. (If I decide I want an extra column loaded when I pull up a cookie, I don't want to have to update my code in more than one place.)

Comment on Class::DBI method sth_to_objects Select or Download Code

Replies are listed 'Best First'.
Re: Class::DBI method sth_to_objects by perrin (Chancellor) on Oct 08, 2004 at 21:39 UTC
That method takes a single sth with data for one type of object in it and turns each row into an object. It will only work for multiple objects if they have no overlapping column names. I posted code and discussion of this on the mailing list recently, so you should look in the archives for it. In general, there are no limitations in terms of what SQL you can use with Class::DBI. It's also very easy to get the list of column names from a class. I'd suggest asking specific questions on the mailing list for more details.	[reply]
Re^2: Class::DBI method sth_to_objects by ryantate (Friar) on Oct 08, 2004 at 22:18 UTC
Thanks much, Perrin. I just now read your post as well as a slightly older post (and this newer one), which both helped me understand the issue a bit better. It looks like I want is the ->construct method, no? `sth_to_objects` is fairly inflexible, it would seem more logical to simply have a method that takes a row arrayref, shorterning it for every column it needs. But the existing technique is better for making iterators, I suppose. Thanks for the link to the mailing list, I missed it the first few times I looked at the wiki (should be in bullets). The other thing that keeps throwing me off is the use of __CONSTANT__ syntax to pull certain info into the custom SQL. Not a corner of Perl I know much about.	[reply] [d/l]
Re^3: Class::DBI method sth_to_objects by knowmad (Monk) on Oct 11, 2004 at 19:03 UTC
In regards to the use of __CONSTANT__, these are simply substition values. From the POD: `To assist with writing SQL that is inheritable into subclasses, severa +l additional substitutions are available here: __TABLE__, __ESSENTIAL +__ and __IDENTIFIER__. These represent the table name associated wit +h the class, its essential columns, and the primary key of the curren +t object, in the case of an instance method on it.` [download] William	[reply] [d/l]