[Dbix-class] Need help with multi-join query

[Dennis Daupert]

================================
WHAT I'M TRYING TO DO:
================================
I have three tables: photos, galleries, tags.
In Catalyst, I'm trying to retrieve photo info by 
searching on photos, joined to their tags, and joined
to the galleries table that the photo's filesystem 
location. 
================================
MY APPROACH (I'm open to another, if there's a better way)
================================
I *think* I need to search on photos table, and join galleries
and tags; but I can't seem to get the syntax right, or 
I've done some silly thing in my setup.

I've tried variations of this, to no avail:

my $photo_obj = $c->model('CatapultDB::Photos')->search(
                { 
                 'tags.tag' => "$tag", 
                },
                { 
                  join => [qw/ tags galleries /],
                }
                )->page($page);

================================
SCHEMA DETAILS:
================================
CREATE TABLE Photos (
  id           SERIAL      NOT NULL PRIMARY KEY,
  gallery      INTEGER NOT NULL
                    references Galleries(id)
                    ON DELETE CASCADE,
  name         VARCHAR(150) NOT NULL,
  filename     VARCHAR(150) NOT NULL,
);

In Photos class:
__PACKAGE__->belongs_to("gallery", 
"Catapult::Schema::CatapultDB::Galleries", 
{ id => "gallery" });
__PACKAGE__->has_many("tags", 
"Catapult::Schema::CatapultDB::Tags", 
{ "foreign.photo" => "self.id" });
================================
CREATE TABLE tags (
  id           SERIAL  NOT NULL PRIMARY KEY,
  photo        INTEGER REFERENCES photos(id)
                    ON DELETE RESTRICT,
  tag          VARCHAR(100) NOT NULL
);

In Tags class
__PACKAGE__->belongs_to("photos", 
"Catapult::Schema::CatapultDB::Photos", 
{ id => "photo" });
================================
CREATE TABLE Galleries (
  id           SERIAL        NOT NULL PRIMARY KEY,
  name         VARCHAR (150) UNIQUE NOT NULL,
  directory    VARCHAR(50)   UNIQUE NOT NULL,
);

In GAlleries class:
__PACKAGE__->has_many("photos", 
"Catapult::Schema::CatapultDB::Photos", 
{ "foreign.gallery" => "self.id" },
);

NOTE, I have found that this query works in psql:

SELECT *
FROM photos
JOIN galleries
ON photos.gallery = galleries.id
JOIN tags
ON photos.id = tags.photo
WHERE tags.tag = 'sunshine';

/dennis


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20070520/88d8127f/attachment.htm