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
_______________________________________________
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/dbix-class@lists.scsys.co.uk