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!
signature.asc
Description: This is a digitally signed message part
_______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/[EMAIL PROTECTED]
