[Dbix-class] Joins with circular relations

[Bernhard Weisshuhn]

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