What the best way to reduce the number of queries

Question

I have a helper class DAO (I do not know if is OK have this) for get Categories from MySQL DB, the struct is basically this:

<?php

require_once '../include/PDOConnectionFactory.php';

class CategoryDAO extends PDOConnectionFactory
{
    /**
     *
     * @var PDO $conn 
     */
    private $conn;

    public function __construct()
    {
        $this->conn = PDOConnectionFactory::getConnection();
    }
}
?>

This class have these methods (some then):

getMaxLevel()
getAllCategories()
getAllCategoriesOfLevel($level)
haveChildCategory($categoryName)
getIdCategory($categoryName)
getCategoryName($idCategory)

Edit: The body of the method getAllCategories() is similar to this below, and almost all method of this class call this getAllCategories():

public function method()
    {
        try {
            $stmt = $this->conn->prepare("SELECT * FROM category");
            $stmt->execute();
            $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (Exception $e) {
            echo $e->getMessage();
        }

        return $result;
    }

I do not know what the best approach to reduce the redundancy of queries, I think in these:

1. Instance the class object, call getAllCategories() and call the others methods passing the result by parameter.
2. Have a private property with the result of getAllCategories(), populate when the objects is created by __construct().

But in these cases I see this drawback:

1. Do not appear be a good use of OOP.
2. The object may be outdated after a DB UPDATE or INSERT.

If my problem is conceptual in OOP, please let me know.

Answer 1

This response is dependent on the current query structure, where there are no conditionals

class CategoriaDAO extends PDOConnectionFactory
{
    /*DB Connection, static member since you only need one connection*/
    private static $dbConnection;

    /*Sql result set, static since there is not conditonal and only a single table used*/
    private static $resultSet;

    private static function getConnection()
    {
            /*Connect to mysql db, set CategoriaDAO::dbConnection; */
    }

    private static function populateResultSet()
    {
            /*Run query and populate resultSet - either as sql result or parse to array - your call*/
    }
    /**
     *
     * @var PDO $conn 
     */
    private $conn;

    public function __construct()
    {
                /*Get sql connection if one hasn't already been established*/
                if(!CategoriaDAO::dbConnection)
                        $this->conn = PDOConnectionFactory::getConnection();
    }
}

The thought process behind this is that since the results will always be the same (ignore, update, insert, delete for now) there's no requirement to keep a copy of the results in each object.

As you pointed out table updates will knock the stored result set out of sync with the object; this is where I'd like to back track a bit and say that if the result set for a given object only has to be up to date at the time of creation then use normal object members.

Also worth considering both independently and in conjunction with the previous comment is whether or not the query will change and if it will does it require object members to be generated. If the query doesn't change then there's nothing to worry about - except the previous point. If it does change your options are more or less covered in the following examples.

class Foo{
    private $someMember;

    /*
        $params = Associative array of fields and values
    */
    private static buildAndRunQuery($params)
    {
        /*Build sql query based on the given params Array()*/
    }
    public __construct($someMemebrValue)
    {
        $this->someMember = $someMemberValue;
        Foo::buildAndRunQuery(Array("fieldName" => $this->someMember));
    }
}

In this example you're still using a static method to generate the query but you're passing non-static members for the process/ At this point (see comment on objects being up to date at time of creation) you can either store the results within the static member or pass them back to the __construct() function and store int he object instance.

Then there's the potential that the query your using is a bit more involved than simply requesting certain fields such that creating a multidimensional array to pass to the static function would be more hassle than it's worth. In which case you might split the buildAndRunQuery() into buildQuery() - instance method and runQuery() static method such as.

class Foo{

    private $someMember;

    /*
        $params = Associative array of fields and values
    */
    private static runQuery($query)
    {
        /*Build sql query based on the given params Array()*/
    }

    private function buildQuery()
    {
        /*Construct your query here and either return calling method or store in instance member*/
         /*Either*/
            return <Constructed query>;
        /*Or*/
           $this->query = <Constructed query>;
    }

    public __construct($someMemebrValue)
    {
        $this->someMember = $someMemberValue;
        /*As per buildQuery() comment either:*/
            Foo::runQuery($this->buildQuery());
        /*Or*/
            Foo::runQuery($this->query);
    }
}

In this case there are a couple of options for handling the generated query prior to calling Foo::runQuery().

Of course there's always the possibility that you don't want to create and run the query in a synchronous manner or indeed in the constructor.

In conclusion I personally feel that for methods that interact with services independent of the object itself such as Sql or perhaps focused DOMDocument, or similar, object interactions it is best to use Static methods where they are both relevant and not ultimately cutting off your nose to spite your face (needlessly complex etc). Of course all of this needs to be considered on a per Class basis.

Answer 2

Your DAO sounds like a really bad idea from the standpoint of efficiency. Personally, I don't find Data Access Objects to provide any real value. When would you pull a category name without also pulling its ID? You'd be better of querying for entire rows at a time and writing a few specialized queries to perform your other functions.