Data mapper doesnt return total_rows

Question

$b->include_related('tent',NULL,TRUE,TRUE)->include_related('booking/guest',NULL,TRUE,FALSE)->include_related('booking/bookingbasis',NULL,TRUE,FALSE)->include_related('booking',NULL,TRUE,FALSE);
            $b->group_by('booking_id');
            $b->select_sum('booking_total','sum_booking_booking_total');
            $b->select_sum('booking_taxes','sum_booking_booking_taxes');
            $b->get_paged(1,1);
            $total = $b->paged->total_rows;

There is 2 total num rows. But $total returns only 1.

Other thing is if i remove following code it returns 2. The issue getting start from Grouping.

$b->group_by('booking_id');
                $b->select_sum('booking_total','sum_booking_booking_total');
                $b->select_sum('booking_taxes','sum_booking_booking_taxes');

Anyone can guess what is the issue.

Check the generated sql query (you can get it with `$b->check_last_query();`) directly in your sql console to see what's returned, probably grouping on a different column than you think. — complex857, Aug 14 '12 at 07:11
dude i cheked the query it returns data what i want, query is working but the porblem is $total = $b->paged->total_rows; should be retuens 2, but it returns 1 once i replace $b->get_paged(1,1); by $b->get and after that i printed $b->result_count again it returns 2. the problem is in $b->get_paged(1,1). Can you guess why ? — Gihan Dilusha, Aug 14 '12 at 07:44
I'm afraid not. Could be a bug in the Datamapper library. For the `total_rows` it runs a count(*) query, if the result is wrong then most likely that query generated wrongly. With the profiler class (`$this->output->enable_profiler(TRUE);`), you can output every query executed, so you maybe see something interesting. You could also try to file a bugreport with the full table scheme+data so they can reproduce your results. — complex857, Aug 14 '12 at 08:01

score 0 · Answer 1 · answered Jun 10 '14 at 13:53

I see it's an old post, but I'm facing the same issue and thought I could at least give some input.

As mentioned, the problem lies the 'group_by'. This is because the count for the total-results is devided over the groups that are made because of the group_by.

To get the desired total-result, you should peform a DISTINCT select. However, this will cause problems for the returned row-set.

So, the issue is that you want to use DISTINCT to get the right paged->total_rows, but GROUP_BY for the right set of objects.

Possible solution

open the datamapper.php library
search for 'public function get_paged'
make the function behave in such a way that groupby is transformed into distinct select for the total.

Replace

$total = $count_query->db->ar_distinct ? $count_query->count_distinct() : $count_query->count();

With

$distinct_column = 'id';
if($count_query->db->ar_groupby){
    $distinct_column = implode(",",$count_query->db->ar_groupby);
    $count_query->db->ar_distinct = TRUE;
    $count_query->db->ar_groupby = NULL;
}
$total = $count_query->db->ar_distinct ? $count_query->count_distinct(NULL, $distinct_column) : $count_query->count();

This way, the groupby-field(s) will be used in the distinct-count and set to NULL to be ignored in the total-results.

Data mapper doesnt return total_rows

1 Answers1