| Subject: | Test for whether or not a join is required not doing the right thing |
| Date: | Wed, 11 Apr 2018 22:26:50 +0100 |
| To: | bug-DBix-Class [...] rt.cpan.org |
| From: | David McKelvie <dmck [...] interactive.co.uk> |
I want to create a query:
SELECT
"me"."id"::int,
"me"."project_name",
TRUNC(EXTRACT(EPOCH FROM "me"."upload_date" AT TIME ZONE 'Europe/London'))::bigint,
"me"."num_surveys"::int,
count(*) AS num_uploaded
FROM "cctv_survey_group" "me"
LEFT JOIN inspection_record ON inspection_record.is_deleted = false AND
profile = 'CCTVSurvey' AND
location_status_comment like me.project_name || '/%'
WHERE ( "me"."is_deleted" = false )
GROUP BY "me"."id","me"."project_name", "me"."upload_date", "me"."num_surveys"
;
ie there is a "cctv_survey_group" table and I want to add a field to the
resultset which counts the numbers of inspection_record that match the project_name
I originally had
$rs = $rs->search(undef, {
'+select' => [ \ ( "( select count(*) from inspection_record where profile = 'CCTVSurvey' ".
"and is_deleted = false and location_status_comment like me.project_name || '/%' )" ) ],
'+as' => [ 'num_imported' ]
});
where $rs is the ResultSet got by just querying on "cctv_survey_group".
This generated
SELECT "me"."id"::int, "me"."project_name",
TRUNC(EXTRACT(EPOCH FROM "me"."upload_date" AT TIME ZONE
'Europe/London'))::bigint,
"me"."num_surveys"::int,
( select count(*) from inspection_record where profile = 'CCTVSurvey' and
is_deleted = false and location_status_comment like me.project_name || '/%' )
FROM "cctv_survey_group" "me" WHERE ( "me"."is_deleted" = false );
which is correct, but quite slow to execute.
So I tried to speed this up.
I added a has_many relationship
__PACKAGE__->has_many(
"imported",
"Drain::Schema::Result::InspectionRecord",
sub {
my $args = shift;
return {
"$args->{foreign_alias}.location_status_comment" =>
\ "LIKE $args->{self_alias}.project_name || '/%' ",
"$args->{foreign_alias}.is_deleted" => \ " = false",
"$args->{foreign_alias}.profile" => \ " = 'CCTVSurvey'",
};
},
{ cascade_copy => 0, cascade_delete => 0, },
);
and modified the search to be
$rs = $rs->search(undef, {
'join' => "imported",
'group_by' => \ '1, 2, 3, 4',
'+select' => [ \ 'count(*)' ],
'+as' => [ 'num_imported' ]
});
But this generated SQL which did not do a join at all.
But
$rs = $rs->search(undef, {
'join' => "imported",
'group_by' => \ '1, 2, 3, 4',
'+select' => [ \ 'count(imported.id)' ],
'+as' => [ 'num_imported' ]
});
where I replaced the '*' with 'imported.id', did generate the desired SQL with the join.
I found it quite confusing and time consuming to debug this problem.
Why was it not generating a join even tho I had asked for one?
Presumably, there is some code that checks to see if the joined table is being
referenced and if not then the join is not added.
I think this is a case where that check is not doing the right thing.
And it would have speeded my investigations up a great deal if DBIx had
produced a warning saying 'You asked for a join, but Im not going to because...'
David Mckelvie
david:server$ perl -MDBIx::Class -e 'print $DBIx::Class::VERSION;'
0.082840