[Dbix-class] Multi Join

[John Napiorkowski]

--- Will Smith <willbelair at yahoo.com> wrote:

> Hi,
>  I am trying to do a multi join with some params. My
> code is:
>  
>  my $rs = [$c->model('myappDB::City')->search(
>      {
>        'calls.CallerID' => '1',
>        'calls.CallStatusID' => '0'
>      },
>      {       
>        join => { 'property' => 'calls' },
>        order_by => 'me.CityName'
>      } 
>      )];
>  -----------------------------------------
>  
>  in my DB.pm files:
>  
>  property:
>  __PACKAGE__->has_many(calls => 'myappDB::Call',
> 'PropertyID');
>  __PACKAGE__->belongs_to(city => 'myappDB::City',
> 'CityID');
>  
>  city:
>  __PACKAGE__->has_many(property =>
> 'myappDB::Property', 'CityID');
>  
>  calls:
>  __PACKAGE__->belongs_to(property =>
> 'accessDB::Property', 'PropertyID');
>  
>  -------------------------
>  and this is my outcome: (column names)
> City,PropertyName,CallNumberID
>  
>    Altoona,Courtyard by Marriott Altoona,
>    Atlanta,,
>    Columbus,,
>    Greeneville,Main Stay Greenville,
>    Mississauga,,
>    Pittsburgh,,
>    White Plains,Residence Inn White Plains,
>  
>  ---------------------
>  
>  This is my template:
>  [% FOREACH rs IN rs %]
>  [% rs.CityName %],[% rs.property.PropertyName %],[%
> rs.property.calls.CallNumberID %]<br>
>  [% END %]
>  
>  --------------
>  
>  As you can see, I have missing data. Is there
> something wrong that I did?
>  This is mydate from mysql query:
> 
>
+--------------+----------------------------------------+--------------+
>  | CityName     | PropertyName                      
>     | CallNumberID |
> 
>
+--------------+----------------------------------------+--------------+
>  | Altoona      | Courtyard by Marriott Altoona     
>     |          964 |
>  | Atlanta      | Residence Inn Atlanta             
>     |          764 |
>  | Columbus     | Courtyard by Marriott Columbus
> Airport |          904 |
>  | Greeneville  | Main Stay Greenville              
>     |         1064 |
>  | Mississauga  | Courtyard Mississauga-Airport     
>     |          704 |
>  | Pittsburgh   | MainStay Pittsburgh               
>     |         1004 |
>  | White Plains | Residence Inn White Plains        
>     |          864 |
> 
>
+--------------+----------------------------------------+--------------+
>  
>  I also, try different join by selecting the main
> table is calls, or property, and still getting stuck
> on the sorting
>  
>  
>  Thank you,
>  Will
>  

Hi,

Maybe it's a typo in the example you gave but I'm not
sure you can give the same name to different
relationships:

>  __PACKAGE__->has_many(property =>
> 'myappDB::Property', 'CityID');
>  
>  calls:
>  __PACKAGE__->belongs_to(property =>
> 'accessDB::Property', 'PropertyID');

Maybe

...->has_many(cityid => 'myappDB::Property', 'CityID')
...->has_belongs(property_id=> 'myappDB::Property',
'PropertyID')

would work better for you.

Because if 'property' is a resultset of a has_many,
you can't do:

rs.property.PropertyName

Because you get a "Resultset has no method called
'PropertyName' style error.  Which you are probably
not seeing since you probably have TT with all the
error messages hidden.

Remember, if it's a has_many style relationship, you
get a resultset, not a row.

--john


       
____________________________________________________________________________________
Boardwalk for $500? In 2007? Ha! Play Monopoly Here and Now (it's updated for today's economy) at Yahoo! Games.
http://get.games.yahoo.com/proddesc?gamekey=monopolyherenow