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

Question

For historical reasons, we have a table at work that has integer values ​​in the text box that correspond to the identifier 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 a “one thing”, but instead of being configured reasonably, the union field is varchar and is called “thingy”.

So, in Postgres, I can do this to join 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 relation in DBIx :: Class? Here is an example of one thing I tried:

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

Answer 1

nwellnhof was close, but to get literal SQL for SQL :: Abstract, I had to do coderef like this:

 __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)} }, }; }, ); 

Answer 2

Using Literal SQL should do the trick:

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

Answer 3

I would change the data type of the field.

If this is not possible, you can add another field of type int and a trigger that passes varchar to int and stores it in the int field, which is then used for connections to improve performance.