DBIC virtual view missing data

Question

I have a problem population the result set, using a DBIC view. All columns related to the joined table are empty.

My view definition is defined as follows:

    package MySchema::CustomResults::ORDERS;
    
    use Modern::Perl;
    use base qw/DBIx::Class::Core/;
     
    __PACKAGE__->table_class('DBIx::Class::ResultSource::View');
     
    __PACKAGE__->table('ORDERS');
    __PACKAGE__->result_source_instance->is_virtual(1);
    
    __PACKAGE__->result_source_instance->view_definition(
        'SELECT p.PROJECT_ID, p.PROJECT, p.Center_Id, p.IMPORT_TIMESTAMP, p.Delivery_CountryCode, p.SHIP_DATE, pp.VALUE AS ORDER_TYPE, pp.ADD_TIMESTAMP
        FROM PROJECT p 
        LEFT JOIN PROJECT_ADDITIONS pp ON p.PROJECT_ID = pp.PROJECT_ID AND pp.FIELD = "ORDER_TYPE"
        WHERE p.PROJECT_ID IN (?)'
    );
    
    __PACKAGE__->add_columns(
        "project_id",
        { data_type => "integer", is_nullable => 0 },
        "project",
        { data_type => "varchar", is_nullable => 0, size => 50 },
        "center_id",
        { data_type => "varchar", is_nullable => 1, size => 50 },
        "delivery_countrycode",
        { data_type => "char", is_nullable => 1, size => 2 },
        "import_timestamp",
        {
            data_type => "datetime",
            datetime_undef_if_invalid => 1,
            default_value => "0000-00-00 00:00:00",
            is_nullable => 1,
        },
        "ship_date",
        {
            data_type => "datetime",
            datetime_undef_if_invalid => 1,
            is_nullable => 0,
        },
        "order_type",
        { data_type => "varchar", is_nullable => 1, size => 255 },
        "add_timestamp" =>
      {
        data_type => "datetime",
        datetime_undef_if_invalid => 1,
        default_value => "0000-00-00 00:00:00",
        is_nullable => 1,
      },
    );
    
    
    1;

If I search for a data set using the follwoing code:

    my @associatedOrders = $self->schema->resultset('ORDERS')->search({},
        {
            bind    => [ $projectId ]
        }
    )->all();

I get all expected rows, but the data of the joined table PROJECT_ADDITIONS pp, order_type and add_timestamp are always undef, if I execute this in my SQLlite in memory database. If I use a MySQL database, I get the complete data set.

Answer 1

The problem with that View is the SQL query in the view definition. The argument in the LEFT JOIN stands in double-quotes, which will be interpreted as keyword in SQLite.

I changed:

LEFT JOIN PROJECT_ADDITIONS pp ON p.PROJECT_ID = pp.PROJECT_ID AND pp.FIELD = "ORDER_TYPE"

into:

LEFT JOIN PROJECT_ADDITIONS pp ON p.PROJECT_ID = pp.PROJECT_ID AND pp.FIELD = 'ORDER_TYPE'

and it works as expected.