On 6/13/06 6:09 PM, Jonathan Vanasco wrote: > in plain sql it would be: > > SELECT * > FROM account , email , verification_string > WHERE > account.id = email. account_id AND > email.id = verification_string.id AND > email.email_address= %s AND > verification_string.verification_string= %s > > can this be done trivially in rosedb using the table/class objects?
Yes, this is exactly what the Manager class is designed to do. You can find documentation here: http://search.cpan.org/dist/Rose-DB-Object/lib/Rose/DB/Object/Manager.pm The tutorial shows how to create a Manager class for each of your Rose::DB::Object-derived classes: http://search.cpan.org/dist/Rose-DB-Object/lib/Rose/DB/Object/Tutorial.pod#M ultiple_objects and how to use a Manager to formulate queries that use JOINs behind the scenes: http://search.cpan.org/dist/Rose-DB-Object/lib/Rose/DB/Object/Tutorial.pod#A uto-joins_and_other_Manager_features In your example, assuming standard class and relationship names, the Manager call would look something like this: $emails = Email::Manager->get_emails( require_objects => [ qw(account verification_string) ], query => [ 'email.email_address' => 'bar', 'verification_string.verification_string' => 'foo', ]); after which $emails would be a reference to an array of Email objects, each with its own Account and VerificationString sub-objects already attached. foreach my $email (@$emails) { # No database access performed here; all data already # loaded by the Manager call above. my $addr = $email->email_address; my $ok = $email->account->is_verified; my $vs = $email->verification_string->verification_string; ... } The SQL produced by the Email::Manager would look something like this: SELECT t1.id, t1.account_id, t1.email_address, t2.id, t2.is_verified, t3.id, t3.email_id, t3.verification_string FROM email t1, account t2, verification_string t3 WHERE t1.email_address = ? AND t3.verification_string = ? AND t1.account_id = t2.id AND t1.id = t3.email_id ORDER BY t1.id with "bar" and "foo" bound to the appropriate DBI place-holders ("?"). (The ORDER BY clause is inserted automatically if the relationship between email and verification_string is one-to-many. If you've set it up as one-to-one or many-to-one instead, then it would be omitted.) You can formulate a similar query using the Account::Manager or VerificationString::Manager as well. Which is best depends on how you've set up your relationships and what kind of indexes you have on these tables. -John _______________________________________________ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
