Active Record: COUNT WHERE?

[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