Welcome Guest, Not a member yet? Register   Sign In
Active Record: COUNT WHERE?
#1

[eluser]BravoAlpha[/eluser]
Does the Active Record class have a way to COUNT that supports a WHERE clause?

The current MySQL driver uses this:
Code:
function count_all($table = '')
    {
        if ($table == '')
            return '0';
    
        $query = $this->query("SELECT COUNT(*) AS numrows FROM `".$this->dbprefix.$table."`");
        
        if ($query->num_rows() == 0)
            return '0';

        $row = $query->row();
        return $row->numrows;
    }
#2

[eluser]Michael Wales[/eluser]
Code:
$query = $this->db->getwhere('table', array('column'=>$column));
$count = $query->num_rows();
#3

[eluser]BravoAlpha[/eluser]
[quote author="walesmd" date="1188990019"]
Code:
$count = $query->num_rows();
[/quote]
Well, I knew that one. I was wondering if there's any signifigant performance difference using this instead of a COUNT statement (or using my current method and counting the result with PHP). (I realize that any difference would probably not matter for me actual usage.)
#4

[eluser]Michael Wales[/eluser]
I think the performance hit between any of the options would be minimal at best.

I believe, from best performer to worst, would be:
1. Perform a COUNT() query on the database.
2. Perform a SELECT query and use the num_rows() function to return the query metadata.
3. Perform a SELECT query, then iterate those results, to perform counting with a variable.

A general rule of thumb - perform as many operations on the MySQL server as possible - it's designed to work with data in this manner and can do so very quickly.
#5

[eluser]Jay Callicott[/eluser]
The problem with num rows is that it runs the full query and then just returns how many rows were returned. When the query is complex and slow like one I'm working on which has math calculations and a bunch of joins and having clauses then my count query gets way too slow.

The best way I guess is to run a SELECT COUNT(something) from (QUERY) as tbl1 but then you're having to do a custom query and that's a pain. I wish there was some function I could create but I'm not sure there is.
#6

[eluser]Jay Callicott[/eluser]
Hmm actually doing a subselect is just as bad. I don't have a clue how to do a count(*) query when there's a having clause which is what I have since the having clause is dependent on fields in the select. oh dear...
#7

[eluser]Alex007[/eluser]
Why not do this ?
Code:
$this->db->select("COUNT(*) AS MyCount");
$this->db->from("MyTable");
$this->db->where("field", $value);
$this->db->get();
#8

[eluser]Jay Callicott[/eluser]
Actually that doesn't work for complex queries like with having clauses.

I found a good solution.

SQL_CALC_FOUND_ROWS and FOUND_ROWS()

It's pretty easy and doesn't execute the whole query. Google those and you'll see what I mean.
#9

[eluser]Unknown[/eluser]
No offense, but I think advising people to go ahead and do the whole
Code:
select * from table
and then just do a num_rows() is pretty poor advice. Unless you're doing database level caching and are looking to cache that full query frequently or something.

However, typically the only reason I need a count_where type thing in the first place is to evaluate search results lengths.

Also, using
Code:
count(*)
is less efficient than something like
Code:
select count(id) as thingcount
.

All that being said, I went ahead and boneheaded together a method that seems to preserve previous db->where declarations, but I'm unsure about what file to create in my custom lib folder. I tried making a MY_DB class that extended DB... no good. Then I did one for the sql driver, also no good.

Any suggestions?


Code:
function countwhere($table = '', $where = null, $fieldname = '*')
    {
        if ($table != '')
        {
            $this->from($table);
        }

        if (!is_null($where))
        {
            $this->where($where);
        }
    
        $sql = $this->_compile_count($fieldname);

        $result = $this->query($sql);
        $this->_reset_select();
        return $result;
    }
    
    function _compile_count($fieldname = '*')
    {
        $sql = 'SELECT count('.$fieldname.') ';

        if (count($this->ar_from) > 0)
        {
            $sql .= "\nFROM ";
            $sql .= implode(', ', $this->ar_from);
        }

        if (count($this->ar_join) > 0)
        {        
            $sql .= "\n";
            $sql .= implode("\n", $this->ar_join);
        }

        if (count($this->ar_where) > 0 OR count($this->ar_like) > 0)
        {
            $sql .= "\nWHERE ";
        }

        $sql .= implode("\n", $this->ar_where);
        
        if (count($this->ar_like) > 0)
        {
            if (count($this->ar_where) > 0)
            {
                $sql .= " AND ";
            }

            $sql .= implode("\n", $this->ar_like);
        }
        
        if (count($this->ar_groupby) > 0)
        {
            $sql .= "\nGROUP BY ";
            $sql .= implode(', ', $this->ar_groupby);
        }
        
        if (count($this->ar_having) > 0)
        {
            $sql .= "\nHAVING ";
            $sql .= implode("\n", $this->ar_having);
        }

        return $sql;
    }


If we were men, we'd make some kind of _compile_conditions() method that both _compile_select and _compile_count used, but we're not men, we're mice.


Mason
#10

[eluser]Derek Allard[/eluser]
I know this thread is a getting old, but just wanted to mention that count_all_results() has been added into the svn. You'll need to update your system/database folder (and all files) and maybe also the userguide if you want that.

$this->db->count_all_results();

Permits you to determine the number of rows in a particular Active Record query. Queries will accept Active Record restrictors such as where(), or_where(), like(), or_like(), etc. Example:

Code:
echo $this->db->count_all_results('my_table');
// Produces an integer, like 25

$this->db->like('title', 'match');
$this->db->from('my_table');
echo $this->db->count_all_results();
// Produces an integer, like 17

I'd love to have you beat it up a bit, particularly if you are using a database besides MySQL.




Theme © iAndrew 2016 - Forum software by © MyBB