[Dbix-class] Help with relationships

[Shane Corgatelli]

Hello, I'm new to DBIx::Class and the behavior I'm getting when using
relationships doesn't match what the documentation leads me to believe
should happen.

According to the documentation for DBIx::Class::Relationship

  my $cheapfredbooks = $schema->resultset('Author')->find({
    Name => 'Fred',
  })->books->search_related('prices', {
    Price => { '<=' => '5.00' },
  });

would produce a query like:

  SELECT * FROM Author me
  LEFT JOIN Books books ON books.author = me.id
  LEFT JOIN Prices prices ON prices.book = books.id
  WHERE prices.Price <= 5.00

I'm doing something similar:

   $results = $schema->resultset('Resource')->find('X300002', 1, 1)
     ->recurrence
       ->search_related('dates');

   while ( my $d = $results->next ) { ... }

But the sql I get is (I've cut the selected fields for brevity):
   SELECT me.* 
      FROM dsPlaylistResource me 
      WHERE ( ( me.CustomerID = ? AND me.PlaylistID = ? AND me.Position = ? ) ): 'X300002', '1', '1'
   SELECT me.* 
      FROM dsRecurrence me 
      WHERE ( ( ( me.CustomerID = ? AND me.RecurrenceID = ? ) ) ): 'X300002', '1'
   SELECT me.* 
      FROM dsRecurrenceDate me 
      WHERE ( me.CustomerID = ? AND me.RecurrenceID = ? ) ORDER BY SelectedDate: 'X300002', '1'

Is there a reason why this is performing multiple queries? It seems that
the same thing could be achieved using a single join. The documentation
makes it sound that something like the following should be happening.

SELECT *
  FROM dsPlaylistResource pl
  LEFT JOIN dsRecurrence r
    ON r.CustomerID = pl.CustomerID
   AND r.RecurrenceID = pl.RecurrenceID
  LEFT JOIN dsRecurrenceDate rd
    ON rd.CustomerID = r.CustomerID
   AND rd.RecurrenceID = r.RecurrenceID
  
Here are the relationships that I'm using:

Resource->might_have(
   recurrence => 'Recurrence',
   {
      'foreign.CustomerID'   => 'self.CustomerID',
      'foreign.RecurrenceID' => 'self.RecurrenceID',
   },
);

Recurrence->has_many( 
   dates => 'RecurrenceDate',
   { 
      'foreign.CustomerID'   => 'self.CustomerID',
      'foreign.RecurrenceID' => 'self.RecurrenceID',
   },
   {
      order_by               => 'SelectedDate',
   },
);

RecurrenceDate->belongs_to( 
   recurrence => 'Recurrence',
   { 
      'foreign.CustomerID'   => 'self.CustomerID',
      'foreign.RecurrenceID' => 'self.RecurrenceID',
   },
);

Thanks in advance.

-- 
Shane Corgatelli

Programming Manager
EZ-NetTools
www.eznettools.net

EZ-NetTools - We make it easy!
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: This is a digitally signed message part
Url : http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20080410/09668a76/attachment.pgp