[Dbix-class] DBIx::Class troubles with retrieving thread reply count on discussion software

[Ash Berlin]

Devin Austin wrote:
> Hi everyone, I'm new to this, but I thought I'd post a question.
>
> I"m having trouble trying to retrieve the reply count to each thread 
> in my message board software ( 
> http://www.timorperfectus.com/users/devin/).  I've tried a few things, 
> but this is the current code I'm trying to get to work:
>
> my $comments = $schema->resultset('News')->search(
>         {
>         replyid => 0
>         },
>         {
>           rows => 3,
>           page => scalar $q->param('page') || 1,
>           order_by => 'date DESC'
>         }
>     );
>    
That order by will only work so long as quoting is disabled (default). 
Instead try order_by => \'date DESC'
>    
>    
>     ## let's get the comments
>     for ( $comments->all )  {
Ew. What "all" does it queries the database and inflates all the rows 
into objects. Since you are iterating across it, a much better plan 
would be:

while (my $comment = $comments->next) {
>        
>         my $count = $schema->resultset('News')->search(
>         {
>             replyid => $_->postid
>         },
>         {
>             select => [
>                 {
>                  count => '*'
>                                 }
>             ],
>             as => [ 'count' ]
>         }
>         );
Innovative. However a simpler way would be to use the ->count function 
of a RS. From the docs (DBIx::Class::ResultSet) :

       Performs an SQL "COUNT" with the same query as the resultset was 
built
       with to find the number of elements. If passed arguments, does a 
search
       on the resultset and counts the results of that.

The following should do the trick

       my $count = $schema->resultset('News')->count({ replyid => 
$comment->postid });
>         my $c = $count->next;
>        
>         my %row_data;
>         $row_data{'post_subject'}       = $_->subject;
>         $row_data{'post_author'}    = $_->author;
>         $row_data{'post_date'}        = $_->date;
>         $row_data{'post_id'}        = $_->postid;
>         $row_data{'post_content'}    = $_->story;
>         $row_data{'replies'}        = $c->count;
>         #$row_data{'is_hidden'}  = $_->is_hidden;
>    
And obviously change the above to match.
>    
>         # push it into the the loop....
>         push ( @end_posts, \%row_data );
>        
>     }
> If this is too much code to look at let me know and I'll trim it 
> down...anyone have any ideas? Do you need to see my schema?
>
> Thanks!
>
> -devin
>
> -- 
> timorperfectus.com <http://timorperfectus.com> - web design to 
> frightening perfection.
>
>

HTH
Ash