[Dbix-class] @bind confusion

[Wolfgang Kinkeldei]

Peter,

Am 03.08.2010 um 14:34 schrieb Peter Rabbitson:

> Wolfgang Kinkeldei wrote:
>> Hi Peter,
>> If you need the full schema files, I could provide a tarball for you.
> 
> No need for these.
> 
>> 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.
>> <snipped a lot of stuff>
>> my $nr_uploads =
>>    $schema->resultset('Subelement')
>>    ...
>>      ->count_rs
>>      ->as_query;
>> my $nr_uploads_done =
>>    $schema->resultset('File')
>>      ->search(
>>          {
>>            ...
>>          },
>>          {
>>              alias => 'file',
>>              join => {subelement => ['step', 'element']},
>>              select => 'count(*)',
>>              as => 'nr_uploads_done',
>>          })
>>      ->as_query;
>> my $concept_rs =
>>    $schema->resultset('Concept')
>>      ->search(
>>          {
>>            ...
>>          },
>>          {
>>              'select' => [
>>                ...
>>                $nr_uploads,
>>                $nr_uploads_done,
>>              ],
>>              ...
>>          });
>>          my @concepts = $concept_rs->all;
> 
> From this you have the supposedly working:
> 
>> 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

the subquery's first table is aliassed to 'subelement' -- not 'me' as usual. The 'me.concept_id' refers to the concept table from the outer query. Therefore, we are correlated.

>>                  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 ...
> 
> Which is *just as bogus* as your original query. The insides of each $count_rs
> are not correlated to anything outside of the parenthesis that enclose the first
> SELECT statement. As I noted previously - you might as well be selecting random
> hardcoded values - there will be no change in the result.

'me' was not what you thought it was...

> ->count_rs->as_query was not designed to be used in a select => [] list, because
> it is *completely useless* there (as it provides a standalone number, *insulated from
> any higher-up queries*). All it can be used for is in WHERE clauses to compare counts
> of something to either a column value or a function result or something.

not useless, it *is* counting the right things, believe me :-)


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:wolfgang at kinkeldei.de -->