将大型/复杂查询存储为OOP常量变量，最佳实践？

Question

My application uses a few very large and complex SQL queries. I don't want to have these in the classes that use them as they clutter things up. So I tried something new: storing them in a Queries class and setting the various queries my app uses as const variables in that class. And then, in my other classes I call the query as Db::query(Queries::queryID, array($parameter)). This stores the clutter of the queries somewhere else, and keeps the working classes neat. This also helps keep repetition down as there are a few queries that are used by multiple classes.

Example:

abstract class Queries {

    const queryID = <<<'SQL'
SELECT t.typeID, t.typeName, ROUND(greatest(0,sum(t.quantity)) * (1 + (b.wasteFactor / 100))) * ? AS quantity
FROM
   (SELECT invTypes.typeid typeID, invTypes.typeName typeName, quantity 
    FROM invTypes, invTypeMaterials, invBlueprintTypes
    WHERE invTypeMaterials.materialTypeID = invTypes.typeID AND
          invBlueprintTypes.productTypeID = invTypeMaterials.typeID AND
          invTypeMaterials.TypeID = ?
    UNION 
    SELECT invTypes.typeid typeid, invTypes.typeName name, invTypeMaterials.quantity * r.quantity * - 1 quantity
    FROM invTypes, invTypeMaterials, ramTypeRequirements r, invBlueprintTypes bt 
    WHERE invTypeMaterials.materialTypeID=invTypes.typeID AND
          invTypeMaterials.TypeID =r.requiredTypeID AND
          r.typeID = bt.blueprintTypeID AND
          r.activityID = 1 AND 
          bt.productTypeID = ? AND 
          r.recycle = 1
   ) t
INNER JOIN invBlueprintTypes b ON (b.productTypeID = ?)
GROUP BY t.typeid, t.typeName
SQL;

...

}

This is working well for the most part, but I wanted to know of others' opinions on separating queries from the working classes like this. Is there a better method? Am I micromanaging this?

Answer 1

I used a couple of methods. One is a class that contains methods that execute queries and return the results.

Another one is a class that encapsulates a single query and contains an Execute method that returns the queried data. The advantage of the latter is that you don't have a class that grows out of control when you need more queries. It's a class per query. That also allows you to put additional pre-processing of the data in that same class. It's like a factory for your data.

If you've got a proper auto loader in place, having a folder of query classes is quite easy to maintain and use.

Per request an example:

class Query_User {

  function __construct($username)
  {
    $this->username = $username;
  }

  function execute() {
    // A piece of pseudo code to execute the query:
    $result = YourDatabase::Instance->QuerySingleObject(
      "SELECT * FROM users WHERE username = :username",
      array("username" => $this->username));

    // Example post-processing of the data before it is returned.
    $result->age = DateUtils::yearsBetween($result->birthdate, time());

    return $result;
  }
}

You can then just call it like this:

$user = new QueryUser('JohnDoe')->execute();

The advantage of this method is that the class itself is small and simple, containing only one query. But it can implement aditional modification of the data, or it can execute a couple of queries and combine the results into a single result.

You could also introduce extra functionality, like caching the results for some heavily used queries:

class Query_User {

  function __construct($username)
  {
    $this->username = $username;
  }

  function execute() {
    $key = get_class() . ',' . $this->username;
    // Assuming there is some cache class to cache on disk or into MemCache.
    $result = Cache::read($key);
    if ($result === false)
    {
      $result = $this->performQuery();
      Cache::write($key, $result);
    }
    return $result;
  }

  function performQuery() {
    // A piece of pseudo code to execute the query:
    $result = YourDatabase::Instance->QuerySingleObject(
      "SELECT * FROM users WHERE username = :username",
      array("username" => $this->username));

    // Example post-processing of the data before it is returned.
    $result->age = DateUtils::yearsBetween($result->birthdate, time());

    return $result;
  }
}

Maybe you could even isolate that caching into a base class, making it more easy to implement it in all your query classes.

I haven't explained the use of auto loaders, but that is a common subject and not strictly linked to this answer. It will make your life easier, though, since you don't have to call include or require for every query class you want to use.