Skip Menu |
 

This queue is for tickets about the DBIx-Class CPAN distribution.

Report information
The Basics
Id: 125075
Status: open
Priority: 0/
Queue: DBIx-Class

People
Owner: Nobody in particular
Requestors: dmck [...] interactive.co.uk
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: (no value)



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
Download (untitled) / with headers
text/plain 616b
On Wed Apr 11 23:48:43 2018, dmck@interactive.co.uk wrote: Show quoted text
> > ... > 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...'
Thank you for the report, sorry for not replying earlier. Both of the above are valid observations. The fix for the first portion is almost ready, though unlikely to ship with the immediate-next version. Regarding better diagnostic: I will have a think on how to provide this information in a reasonable manner...


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.