Passing CAST(foo AS type) as a relationship condition in DBIx::Class
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)} }, }; },);
Using Literal SQL should do the trick:
__PACKAGE__->has_one( 'other_thing', 'MySchema::OtherThing', { 'foreign.id' => { '=', \'CAST(self.thingy AS int)' } },);
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.