Hi Peter,
If you need the full schema files, I could provide a tarball for you. However,
this is nothing that I would like to see in the mailing list as a whole. It
would not help most of the people reading this thread...
Am 03.08.2010 um 13:20 schrieb Peter Rabbitson:
> Wolfgang Kinkeldei wrote:
>> Hello,
>> when using subqueries in the 'from' part of a query, sometimes the list of
>> @bind parameters is not maintained correct. As a simple testcase please find
>> a primitive subselect enclosed that just does a count with a correlated
>> subquery.
>> <snip>
>> # a simple counting subquery
>> my $subquery = $schema->resultset('PersonRole')
>> ->search( { role_id => 'admin' } ) # 1st bind param
>> ->count_rs
>> ->as_query;
>> my @people = $schema->resultset('Person')
>> ->search(
>> {
>> 'me.person_id' => 42, # 2nd bind param
>> },
>> {
>> select => [ $subquery ],
>> # whatever you join here, @bind gets confused
>> join => 'person_regions'
>> })
>> ->all;
>> <snap>
>
>
> All I can say at this point is that the problem lies somewhere in the way
> count_rs processes arguments. However I need to see a *real* world search()
> query to wrap my head around which use-case did I miss when I designed the
> feature. Please provide me with a complete meaningful search()/DBIC_TRACE
> pair, no matter how complex as long as it is complete.
This is the search taken from my project. The points marked like (1), (2), (3)
contain places where changes make the query work or fail.
A working example omitting bind parameters in the subqueries is this (the
COUNTing here is done in two different ways but this has no influence of error
or success):
my $nr_uploads =
$schema->resultset('Subelement')
->search(
{
'element.concept_id' => { '=', \'me.concept_id' },
# (1)
# vanilla SQL works
'step.flags' => { '~' => \q{E'\\\\mallow_uploads\\\\M'} },
# (1)
# with bind values here, things get bad
#'step.flags' => { '~' => '\\mallow_uploads\\M' },
},
{
alias => 'subelement',
join => ['step', 'element'],
})
->count_rs
->as_query;
my $nr_uploads_done =
$schema->resultset('File')
->search(
{
'element.concept_id' => { '=', \'me.concept_id' },
# (2)
# vanilla SQL works
'file.origin' => { '=' => \q{'satellite'} },
'file.path' => { '~' => \q{'/A001'} },
# (2)
### with bind values here, things get bad.
### reason: collecting the binds as 'where' for subselect _AND_
as 'from' for main select
# 'file.origin' => { '=' => 'satellite' },
# 'file.path' => { '~' => '/A001' },
### FIXME: looks we are not interested where the files came from,
he?
},
{
alias => 'file',
join => {subelement => ['step', 'element']},
select => 'count(*)',
as => 'nr_uploads_done',
})
->as_query;
my $concept_rs =
$schema->resultset('Concept')
->search(
{
-not_bool => 'step.is_final',
'responsibilities.responsibility_kind_id' => 'agency',
-bool => 'satellites.active',
'satellites.satellite_id' => 4,
},
{
# (3)
# when throwing away the joins, things will also work.
join => [
'step',
{responsibilities => {person => {agency => 'satellites'}}},
],
'select' => [
qw(me.concept_id me.name me.job_no),
\q{step.name},
\q{folder_name(me.name || '_' || me.job_no, me.concept_id)},
$nr_uploads,
$nr_uploads_done,
],
'as' => [qw(campaign_id name job_no step_name folder_name
nr_uploads nr_uploads_done)],
});
my @concepts = $concept_rs->all;
SELECT me.concept_id, me.name, me.job_no, step.name, folder_name(me.name || '_'
|| me.job_no, me.concept_id), (SELECT COUNT( * ) FROM subelement subelement
JOIN step step ON step.step_id = subelement.step_id JOIN element element ON
element.element_id = subelement.element_id WHERE ( ( element.concept_id =
me.concept_id AND step.flags ~ E'\\mallow_uploads\\M' ) )), (SELECT count(*)
FROM file file LEFT JOIN subelement subelement ON subelement.subelement_id =
file.subelement_id LEFT JOIN element element ON element.element_id =
subelement.element_id WHERE ( ( element.concept_id = me.concept_id AND
file.origin = 'satellite' AND file.path ~ '/A001' ) )) FROM concept me JOIN
step step ON step.step_id = me.step_id LEFT JOIN responsibility
responsibilities ON responsibilities.concept_id = me.concept_id LEFT JOIN
person person ON person.person_id = responsibilities.person_id LEFT JOIN agency
agency ON agency.agency_id = person.agency_id LEFT JOIN satellite satellites ON
satellites.agency_id = agency.agency_id WHERE ( ( satellites.active AND (NOT
step.is_final) AND responsibilities.responsibility_kind_id = ? AND
satellites.satellite_id = ? ) ): 'agency', '4'
when changing the condition marked (1) to use bind parameters:
my $nr_uploads =
$schema->resultset('Subelement')
->search(
{
'element.concept_id' => { '=', \'me.concept_id' },
# (1)
# vanilla SQL works
# 'step.flags' => { '~' => \q{E'\\\\mallow_uploads\\\\M'} },
# (1)
# with bind values here, things get bad
'step.flags' => { '~' => '\\mallow_uploads\\M' },
},
{
alias => 'subelement',
join => ['step', 'element'],
})
->count_rs
->as_query;
-- remaining part stays the same, the trace displays:
SELECT me.concept_id, me.name, me.job_no, step.name, folder_name(me.name || '_'
|| me.job_no, me.concept_id), (SELECT COUNT( * ) FROM subelement subelement
JOIN step step ON step.step_id = subelement.step_id JOIN element element ON
element.element_id = subelement.element_id WHERE ( ( element.concept_id =
me.concept_id AND step.flags ~ ? ) )), (SELECT count(*) FROM file file LEFT
JOIN subelement subelement ON subelement.subelement_id = file.subelement_id
LEFT JOIN element element ON element.element_id = subelement.element_id WHERE (
( element.concept_id = me.concept_id AND file.origin = 'satellite' AND
file.path ~ '/A001' ) )) FROM concept me JOIN step step ON step.step_id =
me.step_id LEFT JOIN responsibility responsibilities ON
responsibilities.concept_id = me.concept_id LEFT JOIN person person ON
person.person_id = responsibilities.person_id LEFT JOIN agency agency ON
agency.agency_id = person.agency_id LEFT JOIN satellite satellites ON
satellites.agency_id = agency.agency_id WHERE ( ( satellites.active AND (NOT
step.is_final) AND responsibilities.responsibility_kind_id = ? AND
satellites.satellite_id = ? ) ): '\mallow_uploads\M', '\mallow_uploads\M',
'agency', '4'
Cannot bind unknown placeholder 4 (4) at
/Library/Perl/5.10.0/DBIx/Class/Storage/DBI.pm line 1475.
The query part looks absolutely perfect, only the subquery-bind-parameter is
used too often :-(
Best,
Wolfgang Kinkeldei
--
' /\_/\ ' .print[split??,"".(($/=q|Cms)+-03467:;<=|)=~tr!C-z -B! -z!)x
'( o.o )' .$/]->[hex]foreach split qr<>,qq+1ecd039ad65b025b8063475b+||
' > ^ < ' .q<!-- Wolfgang Kinkeldei - mailto:[email protected] -->
_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/[email protected]
