Passing CAST(foo AS type) as a relationship condition in DBIx::Class

Question

For historical reasons, we have a table at work that has integer values in a text field that correspond to the ID's in another table. Example:

CREATE TABLE things (
    id     INTEGER,
    name   VARCHAR,
    thingy VARCHAR
);

CREATE TABLE other_things (
    id     INTEGER,
    name   VARCHAR,
);

So a "thing" has-one "other thing", but rather than being set up sensibly, the join field is a varchar, and called "thingy".

So in Postgres, I can do this to join the two tables:

SELECT t.id, t.name, ot.name FROM things t 
  JOIN other_things ot ON CAST(t.thingy AS int) = ot.id

How can I represent this relationship in DBIx::Class? Here's an example of one thing I've tried:

package MySchema::Thing;

__PACKAGE__->has_one(
    'other_thing',
    'MySchema::OtherThing',
    { 'foreign.id' => 'CAST(self.thingy AS int)' },
); 

Tuscarora

Answer 1

nwellnhof was close, but to get the literal SQL to SQL::Abstract, I had to do a coderef like so:

__PACKAGE__->has_one(
    'other_thing',
    'MySchema::OtherThing',
    sub {
        my $args = shift;
        return {
            qq{$args->{'foreign_alias'}.id} => { q{=} => \qq{CAST($args->{'self_alias'}.dept AS int)} },
        };  
    },
); 

Tuscarora

Answer 2

Using Literal SQL should do the trick:

__PACKAGE__->has_one(
    'other_thing',
    'MySchema::OtherThing',
    { 'foreign.id' => { '=', \'CAST(self.thingy AS int)' } },
);

Goatskin

Gives me "Invalid rel cond SCALAR(...)". :( – Tuscarora

Answer 3

I'd change the datatype of the field.

If that's not possible you could add another field of type int and a trigger that casts the varchar to an int and stores it in the int field that you then use for the joins to improve performance.

Railing