Return from method Count() doesn't match with the actual rows

[deerawan]

Hi, I found a bug when I use this bundle

I have a complex query

$products = Product::join($productlist_table,"$productlist_table.kodebrg",'=',"$product_table.kodebrg")
->where("$product_table.infobrosur", '=', 'Y')
->group_by("$product_table.kodebrg")
->select(array("$product_table.kodebrg",'namabrg', 'kdspek', "$harga_jual_column as hargajual"));

This query return 500 rows and works well. Then I search from datatables looking for text "ESIA" and it return 10 rows in table but the count return from the bundle is 497 rows not 10. So, the pagination shows wrong number.

Here is the bundle's function

private function count()
{
    $copy_query = $this->query;
    $this->count_all = $copy_query->count(); // return 497 rows not 10
}

So I solve the problem by changing method count() to get()

private function count()
{
    $copy_query = $this->query;        
            $rows = $copy_query->get();   // CHANGE THE CODE             
    $this->count_all = count($rows);
}

and now it runs. I don't know maybe someone else face same problem with me.

[deerawan]

When I debug the code I found the raw SQL generated

"SELECT COUNT(*) AS aggregate FROM barang INNER JOIN daftarhargabarang ON daftarhargabarang.kodebrg = barang.kodebrg WHERE barang.infobrosur = ? AND LOWER(barang.namabrg) LIKE ? GROUP BY barang.kodebrg"

When I try it in the phpmyadmin, it return 497 rows. So the method count() is not wrong but the way to get the total rows of datatables in the bundle is not exactly correct by using count().

What do you think? :)

[deerawan]

Here is a final code of method count()

private function count()
{
    $copy_query = $this->query;     
            $this->count_all = count($copy_query->get());

            if($this->query_type == 'eloquent') {
                $this->query->table->selects = $this->columns;
            } else {
                $this->query->selects = $this->columns;
            }                                
}

I have to reassign the selects again because after $copy_query->get(), laravel removes selects. Very weird. :p

[bgultekin]

Using count($copy_query->get()); is not the optimized way :). We shouldn't use this to just get the count of all lines which query returns because it will use more memory and cpu.

Group_by is working a little bit weird with count. Actually, I expect that when you tried in phpmyadmin, you saw lines more than one which has aggregate column for each group of group_by statement. Did you see anything like this? Besides can you share your normal query which returns lines to show at list.

BTW, I guess, copy_query is not neccassary. I don't know, why I used it. But, it will work without it, too.

Lastly, thanks for your investigation and interest 👍

[deerawan]

Yes Bilal, you are absolutely right. I saw lines more than one with same number, 497.

497
497
497
497
...
497

[bgultekin]

Because you are using group by statement, you should use different sql structure to calculate count of lines like this:

SELECT COUNT(count) FROM (SELECT COUNT(source) AS count
FROM call_details
GROUP BY source
HAVING count > 1) as A

(http://stackoverflow.com/questions/6709747/mysql-query-using-sum-of-count)

I will update the code so that you can use your count function (even more) when you create your query.

[deerawan]

I see. Thank you for your help. :D Anyway, your name same like famous muadzin in history, bilal

[bgultekin]

Yes it is :) . By the way, sorry for delay. I couldn't update the code because of exams and works. I hope, I will update soon :) .