当类DAO具有使用相同结果的方法时，减少查询数量的最佳方法是什么？ [英] What the best way to reduce the number of queries when the Class DAO have methods that use the same result?
问题描述
我有一个帮助类DAO（我不知道是否可以有这个类），用于从MySQL DB获取类别，结构基本上是这样的：
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)
编辑：正文方法getAllCategories（）与下面的类似，并且几乎所有此类的方法都将其称为getAllCategories（）：
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:
- 实例化类对象，调用getAllCategories（）并调用其他通过参数传递结果的方法。
- 具有getAllCategories（）结果的私有属性，在由__construct（）创建对象时填充。
但是在这些情况下，我看到了这个缺点：
But in these cases I see this drawback:
- 看起来不是OOP的好用法。
- 在进行数据库更新或插入后，该对象可能已过时。
如果我的问题在OOP中是概念性的，请让我知道。
If my problem is conceptual in OOP, please let me know.
推荐答案
此响应取决于当前的查询结构，其中没有条件
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));
}
}
在此示例中，您仍使用静态方法生成查询，但您要为流程传递非静态成员/此时（请参阅有关创建对象时最新的注释），您可以将结果存储在静态成员中，也可以将结果传递回__construct（）函数并存储对象实例。
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.
然后，与简单地请求某些字段（例如创建多维数据）相比，您使用的查询有更多的可能数组传递给静态函数将比它值得的麻烦。在这种情况下，您可以将buildAndRunQuery（）拆分为buildQuery（）-实例方法和runQuery（）静态方法，例如。
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);
}
}
在这种情况下，有两种处理方式在调用Foo :: runQuery（）之前生成的查询。
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.
总而言之，我个人认为对于与服务无关的方法，这些方法与对象本身无关，例如Sql或聚焦的DOMDocument或类似对象互动时，最好使用静态方法，只要它们既相关又不要最终割断你的鼻子来掩饰你的脸（不必要地复杂等）。当然，所有这些都需要在每个班级的基础上加以考虑。
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.
这篇关于当类DAO具有使用相同结果的方法时，减少查询数量的最佳方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
