[Dbix-class] Joining the same table several times with different conditions each join

[Andrew Beverley]

Dear all,

I'd like to join the same table multiple times, with different
conditions each join (and a variable number of joins). Equivalent SQL to
something like this:

   SELECT record.id,
          s1.value   AS firstname,
          s2.value   AS surname
     FROM record
LEFT JOIN string s1  ON s1.record_id=record.id
                    AND s1.schema_id=1
LEFT JOIN string s2  ON s2.record_id=record.id
                    AND s2.schema_id=2;
LEFT JOIN ...

Note: the number of LEFT JOINs is variable.

Joining with a custom join condition has been discussed before:

http://lists.scsys.co.uk/pipermail/dbix-class/2014-January/011566.html

The workaround as above was (still is?) to use a global variable.
However, I'm not sure that workaround can be used for the scenario
described above. What's the best way (if any) of achieving this with
DBIx::Class?

Of course, the table could be designed with multiple columns for each of
the values (surname and firstname being just 2 examples), but these are
not known at the time of coding and are configurable by the application.

Thanks,

Andy