[Dbix-class] @bind confusion

Peter Rabbitson rabbit+dbic at rabbit.us
Tue Aug 3 13:37:58 GMT 2010


Wolfgang Kinkeldei wrote:
> 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...

Indeed, and you have provided all the code to make this obvious. I just didn't
see it, so all blame goes this way. The bind repetition problem is simple in
itself and will be fixed shortly.

Sorry for taking so long to get wat you are actually doing.

Cheers



More information about the DBIx-Class mailing list