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
_______________________________________________
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]
