| Subject: | query builder: too many table name prefixes when sorting on an element containing multiple dots |
Hey,
Summary: the query builder includes too many table name prefixes when
sorting is done on an element containing more than one dot.
My version information is at the bottom.
Scenario: We have three tables: "oe" containing orders, "project"
containing projects and "project_types" containing project_types. The
relationships are specified on the PostgreSQL dabase as follows:
* oe.globalproject_id references project.id
* project.project_type_id references project_types.id
* project_type has a column called "description"
What we want is to retrieve orders sorted by the project type's
description. So the query builder is called roughly like this ("Order"
is the manager class for the "oe" table):
my $orders = SL::DB::Manager::Order->get_all(
with_objects => [ 'globalproject', 'globalproject.project_type' ],
sort_by => 'globalproject.project_type.description',
);
This results in an error message from PostgreSQL:
DBD::Pg::st execute failed: FEHLER: Schema »t2« existiert nicht at
/usr/share/perl5/vendor_perl/DBIx/Log4perl/st.pm line 62.
Translation: schema "t2" does not exist.
I've logged the query by using DBIx::Log4perl, and this is the gist of
it (only the relevant columns):
SELECT
t1.id, t1.globalproject_id,
t2.id, t2.project_type_id,
t3.id, t3.description
FROM
oe t1
LEFT OUTER JOIN (project t2 JOIN project_types t3 ON
(t2.project_type_id = t3.id)) ON (t1.globalproject_id = t2.id)
ORDER BY t2.t3.description
The problem is the "ORDER BY t2.t3.description". For PostgreSQL this
means that "t2" is supposed to be a schema name.
If I remove the "t2." manually ("ORDER BY t3.description") then the
query works just fine.
A workaround is to leave out the first table's name in the 'sort_by'
argument:
my $orders = SL::DB::Manager::Order->get_all(
with_objects => [ 'globalproject', 'globalproject.project_type' ],
sort_by => 'project_type.description',
);
This works, but only if no other table in that query contains an
attribute/relationship called 'project_type'.
Rose::DB::Object 0.800
Perl 5.16.2
PostgreSQL 9.2.1 on Arch Linux 64bit
Linux chai-latte 3.7.4-1-ARCH #1 SMP PREEMPT Mon Jan 21 23:05:29 CET
2013 x86_64 GNU/Linux
Kind regards,
mosu