[Dbix-class] Help with relationships

[Shane Corgatelli]

On Thu, 2008-04-10 at 21:07 -0500, Jonathan Rockway wrote:
> DBIC could probably "optimize" this (different input, same programmer
> intentions), but in the mean time:
> 
>   $schema->resultset('Resource')->
>       search({  X3000002 ... })->
>       related_resultset('recurrence')->
>       related_resultset('dates');
> 
> This keeps the row object out of the picture (introduced by find in your
> code), which probably saves a trip to the database.

Thanks. I think I understand what your saying. Although the docs are
using find in their example. I've change my code and I'm now getting a
join as expected. I'm having other problems now though. 

Using the following code:

   $results = $schema->resultset('Resource')
     ->search({ CustomerID => 'X300002', ... })
     ->related_resultset('recurrence')
     ->related_resultset('dates');

I get an sql error that CustomerID is ambiguous. I can fix this by using
"me.CustomerID" instead but it seems that the alias should be added
automatically (and it is in other uses). Also, the dates relationship
has an order_by clause specified in the relationship attributes. It is
getting dropped from the generated SQL:

SELECT dates.*
  FROM dsPlaylistResource me 
  LEFT JOIN dsRecurrence recurrence 
    ON ( recurrence.CustomerID = me.CustomerID 
     AND recurrence.RecurrenceID = me.RecurrenceID ) 
  LEFT JOIN dsRecurrenceDate dates 
    ON ( dates.CustomerID = recurrence.CustomerID 
     AND dates.RecurrenceID = recurrence.RecurrenceID ) 
 WHERE ( CustomerID = ? AND PlaylistID = ? AND Position = ? ):
'X300002', '1', '1'

I also have a relationship that has a where clause that further
restricts the dates relationship so that it only returns a subset of the
dates based on the type. However, when using related_resultset or
search_related both the order_by and the where is dropped.

One last usage question. In the relationship for excluded_dates the I've
specified a where clause that looks like 

 where => { Type => "exclude" }

When used with find it is added to the sql but without an alias ("AND
Type = ?"). As mentioned above it is dropped in the join scenario, but
if it was not it would break because there are two tables in the join
with a Type column. If the join was working I couldn't say "me.Type"
because "me" refers to the wrong table. However if I use
"excluded_dates.Type" it would work in the join, but break the find
usage. Is there a way to fix this without creating my own method or
forcing the user to add the where clause to the dates relationship? 

Thanks
-- 
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/20080411/3f1b24e3/attachment.pgp