Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

Class::DBI: Filtering a many-to-many mapping does not work as advertised on CDBI manpage?

by unlinker (Monk)
on Nov 08, 2008 at 19:51 UTC ( #722429=perlquestion: print w/ replies, xml ) Need Help??
unlinker has asked for the wisdom of the Perl Monks concerning the following question:

Venerable Monks
I want to implement a simple many-to-many relation with Class::DBI. Here is a simplified version of my scheme/objects:
package Hotels; __PACKAGE__->table('hotels'); __PACKAGE__->columns(Primary => 'hotel_id'); __PACKAGE__->columns(Others => qw{ name city }); __PACKAGE__->has_many(categories => [HotelCategories => 'category_id'] +); package Categories; __PACKAGE__->table('categories'); __PACKAGE__->columns(Primary => 'category_id'); __PACKAGE__->columns(Others => qw{ name }); __PACKAGE__->has_many(hotels => [HotelCategories => 'hotel_id']); package HotelCategories; __PACKAGE__->table('hotelcategories'); __PACKAGE__->columns(Primary => 'hotelcategory_id'); __PACKAGE__->columns(Others => qw{ hotel_id category_id ); __PACKAGE__->has_a(hotel_id => Hotels); __PACKAGE__->has_a(category_id => Categories);
I want to select all hotels of a particular category in a particular city and the Class::DBI manpage suggests the following construct:
$mycat = Categories->retrieve(3); for ( $mycat->hotels(city => 'Timbucktoo') ) { # do something with hotels in Timbucktoo of category 3 }
Unfortunately this dies with an Error which in effect says: "city is not a column of HotelCategories". Of course I know this! I believe I am mimicking this specific extract from the Class::DBI manpage:
Limiting Music::Artist->has_many(cds => 'Music::CD'); my @cds = $artist->cds(year => 1980); When calling the method created by has_many, you can also supply any additional key/value pairs for restricting the search. The above example will only return the CDs with a year of 1980.
Can a kind monk point me to the error I am making? Thank you.

Comment on Class::DBI: Filtering a many-to-many mapping does not work as advertised on CDBI manpage?
Select or Download Code
Re: Class::DBI: Filtering a many-to-many mapping does not work as advertised on CDBI manpage?
by NetWallah (Abbot) on Nov 09, 2008 at 00:26 UTC
    I have used Class::DBI just a few times, so please excuse potentially incorrect advice.

    IMHO, the links should be made like this:

    __PACKAGE__->has_many(hotels => [HotelCategories => 'Hotels']);
    Using the 'package name' instead of a specific column name for the linkage.

         Have you been high today? I see the nuns are gay! My brother yelled to me...I love you inside Ed - Benny Lava, by Buffalax

      Thanks. The documentation says that the following generic template
      A->has_many(bees => [AB => 'a'])
      will call the method 'a' on each of the objects of type AB. This is exactly what I thought I should have got. I will check your suggestion (where did you see that syntax?) and report.
      Thank you once again for the help
        The documentation for "has_many" reads:
        Class->has_many(method_to_create => "Foreign::Class");
        This method declares that another table is referencing us (i.e. storing our primary key in its table). It creates a named accessor method in our class which returns a list of all the matching Foreign::Class objects. In addition it creates another method which allows a new associated object to be constructed, taking care of the linking automatically. This method is the same as the accessor method with ``add_to_'' prepended.
        The syntax that you reference has this to say:
        Sometimes we don't want to return an instance of the Foreign::Class, but instead the result of calling a method on that object. We can do this by changing the Foreign::Class declaration to a listref of the Foreign::Class and the method to call on that class.
        Which I don't fully comprehend.

             Have you been high today? I see the nuns are gay! My brother yelled to me...I love you inside Ed - Benny Lava, by Buffalax

Re: Class::DBI: Filtering a many-to-many mapping does not work as advertised on CDBI manpage?
by perrin (Chancellor) on Nov 09, 2008 at 03:15 UTC
    The example you are copying is a one-to-many, not a many-to-many with a mapping table. Class::DBI's concept of using a mapping class for many-to-many is usually a bad idea. It will result in lots of unnecessary database calls. It's much better to use the approach described as Ima::DBI methods in the man page and write set_sql() statements with joins in them.
      Thanks. I assumed that since a many-to-many was a two way one-to-many via an intersection table (in my case HotelCategories) using a one-to-many example in either direction would work.
      Thanks for the suggestion, I will check out the Ima::DBI methods.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://722429]
Approved by marto
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (6)
As of 2015-07-05 22:32 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (68 votes), past polls