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 => 'Gallery::Model::DBIC::GalleryMap', 'iid');
my @list = Gallery::Model::DBIC::Images->search_like({ 'GalleryMaps.gid' => 22 },{ join => qw( GalleryMaps )});
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.
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::GalleryMap', '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::GalleryMap', 'gid');
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');
.. 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.