[Dbix-class] Joins with circular relations

[Matt S Trout]

On Wed, Oct 03, 2007 at 06:17:54PM +0200, Bernhard Weisshuhn wrote:
> 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';

Just move on or other of the two cond in the second join into the WHERE clause.

-- 
      Matt S Trout       Need help with your Catalyst or DBIx::Class project?
   Technical Director                    http://www.shadowcat.co.uk/catalyst/
 Shadowcat Systems Ltd.  Want a managed development or deployment platform?
http://chainsawblues.vox.com/            http://www.shadowcat.co.uk/servers/