Re: recommended approach for multiple table single result fetch

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