================================
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
_______________________________________________
List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
Wiki: http://dbix-class.shadowcatsystems.co.uk/
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
Searchable Archive: http://www.mail-archive.com/[email protected]/
