Hello everbody, I'm pulling my hair on this one. I'm trying to specify a query where the join criteria of one nested table refers to a table already joined. No problem in SQL, but I can't seem to do it in DBIC-Syntax. It's kind of hard to explain for, so please take a look at this example (which is not the actual model, just something a made up to illustrate the problem):
CREATE TABLE englishwords ( englishword TEXT PRIMARY KEY ); CREATE TABLE languages ( language TEXT PRIMARY KEY ); CREATE TABLE translatedwords ( englishword TEXT NOT NULL REFERENCES englishwords(englishword), language TEXT NOT NULL REFERENCES languages(language), translatedword TEXT NOT NULL, PRIMARY KEY (englishword,language) ); CREATE TABLE users ( user TEXT PRIMARY KEY, language TEXT NOT NULL REFERENCES languages(language) ); CREATE TABLE userwords ( user TEXT NOT NULL REFERENCES users(user), englishword TEXT NOT NULL REFERENCES englishwords(englishword), PRIMARY KEY (user,englishword) ); We have a list of words and correspoinding translations in several languages. Users have a preferred language and a list of words they need for whatever it is that they use the application for. Silly, but easier to understand than the actual model. I now want to retrieve all words of a certain user from his 'userwords' list translated to his preferred language. In plain SQL I would write sth like this: SELECT translatedwords.translatedword FROM userwords JOIN users ON (users.user = userwords.user) JOIN translatedwords ON ( translatedwords.language = users.language AND translatedwords.englishword = userwords.englishword ) WHERE userwords.user='someuser'; Is there a way to specify this in DBIC with regular join or prefetch syntax? All my experiments ended up with the language table being selected twice. I naively tried stuff like: $schema->resultset('Translatedwords')->search( {'users.user' => 'someuser' }, { join => [ { englishword => { userwords => { user => 'language' } } }, { language => 'users'} ] } ); ... which does what the fine manual says, but not what I want. Is there a way to do this that I have overlooked? Or is this way of joining simply not supported? Do I maybe have to add some clever manual relation to the schema? For reference, these are the relations from the autogenerated schema: Translations::Englishwords->has_many( "translatedwords", "Translations::Translatedwords", { "foreign.englishword" => "self.englishword" }, ); Translations::Languages->has_many( "translatedwords", "Translations::Translatedwords", { "foreign.language" => "self.language" }, ); Translations::Translatedwords->belongs_to( "englishword", "Translations::Englishwords", { englishword => "englishword" }, ); Translations::Translatedwords->belongs_to( "language", "Translations::Languages", { language => "language" }, ); Translations::Languages->has_many( "users", "Translations::Users", { "foreign.language" => "self.language" }, ); Translations::Users->belongs_to( "language", "Translations::Languages", { language => "language" }, ); Translations::Englishwords->has_many( "userwords", "Translations::Userwords", { "foreign.englishword" => "self.englishword" }, ); Translations::Users->has_many( "userwords", "Translations::Userwords", { "foreign.user" => "self.user" }, ); Translations::Userwords->belongs_to( "user", "Translations::Users", { user => "user" } ); Translations::Userwords->belongs_to( "englishword", "Translations::Englishwords", { englishword => "englishword" }, ); Any help would be greatly appreciated. regards, bkw _______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/[EMAIL PROTECTED]