[Dbix-class] @bind confusion

[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
>                   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.

->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.

Of course I may be missing something - feel free to correct me if this is the case.

Cheers