suaveant has asked for the wisdom of the Perl Monks concerning the following question:

I have started playing with Catalyst and I am trying to do things "the right way" by using a database abstraction to make my life simpler. Of course... I have to shift my paradigm in order to do it ;) and I am looking for some help.

Basically the Catalyst helper already set up the basics of my tables, and now I am trying to figure out how to make queries and what else I need to do to hook things together. I got a basic search on a table working using search_like(caption => '%test%'); and I got a join to work by setting up a has_many relationship, but only one way.

Database structure
I am making an image gallery system, the tables I am currently interested in is a list of images, a list of galleries and a map of image ids (iid) to gallery ids (gid) to specify which image is in which galleries. So my 'iid' maps from the 'images' table to the 'gallery_map' table one or more times, which has only 'iid' and 'gid'. The 'gallery_map' table gives the 'gid' which maps me to the 'galleries' table, which stores a name and description of the gallery.

so images 1-* gallery_map 1-1 galleries

Qusetions
now... question one, what kind of relationships should I set up to allow any kind of querying back and forth between these... just a has_many from images to gallery_map and a has_one from gallery_map to galleries or are there others I should include as well.

question two, I was able to search for a gid in gallery_map and get back the images it contained using: 

# relationship in Images is __PACKAGE__->has_many(GalleryMaps => 'Gall
+ery::Model::DBIC::GalleryMap', 'iid');

my @list = Gallery::Model::DBIC::Images->search_like({ 'GalleryMaps.gi
+d' => 22 },{ join => qw( GalleryMaps )});

[download]
but when I tried to search for an iid in images joined with gallery_map to get the galleries an image is in I got lost... it seems to want the relationship descriptor to specify the table a field is from since Images and images both gave errors... what am I doing wrong there?

I realize I may just be going about this all wrong, I'm used to just building the queries myself, I am probably making this more difficult than it is. I appreciate any help, maybe I won't have to bang my head against this stuff for 2 days to work it out.

                - Ant
                - Some of my best work - (1 2 3)

Replies are listed 'Best First'.
Re: Help with joins in DBIx::Class
by castaway (Parson) on Feb 02, 2006 at 17:35 UTC
    Relationships:

    Depends on what you want to be able to see from what other thing.. Do you want to be able to list images from a gallery? Do you want to be able to get the galleries an image is in? All that is is the reverse of the previous relationship, i.e.: 

      # in Images:
  __PACKAGE__->has_many('GalleryMaps' => 'Gallery::Model::DBIC::Galler
+yMap', 'iid');

  # in GalleryMap:
  __PACKAGE__->belongs_to('iid' => 'Gallery::Model::DBIC::Images');
  __PACKAGE__->belongs_to('gid' => 'Gallery::Model::DBIC::Galleries');

  # in Galleries
  __PACKAGE__->has_many('GalleryMap' => 'Gallery::Model::DBIC::Gallery
+Map', 'gid');

    [download]
    Now, using the latest release, which is 0.05, you can do: 
      # Get images in a gallery, assuming $g is a gallery:
my @images = $g->GalleryMaps->search_related('iid');

 # Get galleries an image is in, assuming $i is an image:
my @galleries = $i->GalleryMaps->search_releated('gid');

    [download]

    .. All code untested, but thats how it works.. The actual joins between the tables will be done behind the scenes. Try setting DBIX_CLASS_STORAGE_DBI_DEBUG=1 in your shell environment, to see the SQL queries it creates.

    C.

[reply]
[d/l]
[select]
      Ahh.. cool.. I wasn't sure about the whole belongs_to bit, thanks.

                      - Ant
                      - Some of my best work - (1 2 3)

[reply]

Back to Seekers of Perl Wisdom