a - cool! b- way too much! i would have settled for "yeah, the manager class is what i'd use"
i'm running with straight-sql right now, but i'll give it a shot using manager joins tomorrow morning! On Jun 13, 2006, at 8:48 PM, John Siracusa wrote: > 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 > //Jonathan Vanasco |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | RoadSound.com / Indie-Rock.net | Collaborative Online Management And Syndication Tools |- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - _______________________________________________ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
