I am looking for a good design pattern, or best practice to achieve a situation of "either this query, or else that other query", with best performance and least overhead.
我正在寻找一个好的设计模式，或最佳实践，以实现“此查询，或其他查询”的情况，具有最佳性能和最小开销。
Business logic/program of demans says "all items since Foo", unless that returns less then three items, then "all items". I am refactoring the current code, and cannot come up with a good way to achieve this logic.
demans的商业逻辑/程序说“自Foo以来的所有项目”，除非返回少于三个项目，然后是“所有项目”。我正在重构当前的代码，并且无法想出一个实现这种逻辑的好方法。
Current pseudo-code (Drupal/PHP):
当前的伪代码（Drupal / PHP）：
<?php
$result = db_query(
'SELECT n.uid FROM og_ancestry oga ' .
'INNER JOIN node n on n.nid = oga.nid ' .
'WHERE oga.group_nid = %d AND n.created > %d GROUP BY n.uid ' .
'ORDER BY cnt DESC LIMIT %d', $group_nid, $since, $limit);
while ($row = db_fetch_array($result)) {
$uids[] = $row['uid'];
}
if (count($uids) < 3) {
$result = db_query(
'SELECT n.uid FROM og_ancestry oga ' .
'INNER JOIN node n on n.nid = oga.nid ' .
'WHERE oga.group_nid = %d GROUP BY n.uid ' .
'ORDER BY cnt DESC LIMIT %d', $group_nid, $limit);
while ($row = db_fetch_array($result)) {
$uids[] = $row['uid'];
}
}
//...do something with the result.
?>
This code feels "not right", first of all because of DRY: it contains the same query, with one minor difference. I can change that by a bit smarter query-building.
这段代码感觉“不对”，首先是因为DRY：它包含相同的查询，只有一个小的区别。我可以通过更聪明的查询构建来改变它。
But worse is the fact that I need to hammer the database (query is quite heavy) only to find out that in more then half of the cases, I need to throw away the result and query the db again.
但更糟糕的是，我需要锤击数据库（查询非常繁重）才发现在超过一半的情况下，我需要丢弃结果并再次查询数据库。
How would you approach such a case?
你会如何处理这种情况？
3 个解决方案
#1
4
If, as you say, "in more then half of the cases, I need to throw away the result and query the db again," then your best bet may be to run only the second query and then evaluate the resultant dataset locally, discarding records if appropriate. It's really more a matter of moving the complexity around than it is of reducing complexity, but at least there's just one trip to the database.
如果你说的话，“在超过一半的情况下，我需要丢弃结果并再次查询数据库”，那么你最好的选择可能是只运行第二个查询，然后在本地评估结果数据集，丢弃记录如果合适。这实际上更多的是移动复杂性而不是降低复杂性，但至少只有一次数据库之旅。
If you ORDER BY n.created DESC, the filtering could simply look at the third record, and if it's earlier than foo, you're done; otherwise, you need to find the first record before foo and discard it and subsequent records.
如果ORDER BY n.created DESC，过滤可以简单地查看第三条记录，如果它早于foo，那么你就完成了;否则，你需要在foo之前找到第一条记录并丢弃它和后续记录。
#2
2
You could use a single CASE/WHEN query to see if the first query returns enough. If so, use the THEN block. If not use the ELSE block. That would save you the second roundtrip to the database.
您可以使用单个CASE / WHEN查询来查看第一个查询是否返回足够的查询。如果是这样，请使用THEN块。如果不使用ELSE块。这将为您节省第二次往返数据库。
#3
0
Would one query with ORDER BY n.created DESC, cnt DESC LIMIT 3 work? It will get the most recently created items first, and return no more than 3 of them. It's not exactly the same as what you have above, but it's pretty close...
是否可以使用ORDER BY n.created DESC，cnt DESC LIMIT 3进行查询？它将首先获取最近创建的项目，并返回不超过3个。它与上面的内容不完全相同，但它非常接近......
#1
4
If, as you say, "in more then half of the cases, I need to throw away the result and query the db again," then your best bet may be to run only the second query and then evaluate the resultant dataset locally, discarding records if appropriate. It's really more a matter of moving the complexity around than it is of reducing complexity, but at least there's just one trip to the database.
如果你说的话，“在超过一半的情况下，我需要丢弃结果并再次查询数据库”，那么你最好的选择可能是只运行第二个查询，然后在本地评估结果数据集，丢弃记录如果合适。这实际上更多的是移动复杂性而不是降低复杂性，但至少只有一次数据库之旅。
If you ORDER BY n.created DESC, the filtering could simply look at the third record, and if it's earlier than foo, you're done; otherwise, you need to find the first record before foo and discard it and subsequent records.
如果ORDER BY n.created DESC，过滤可以简单地查看第三条记录，如果它早于foo，那么你就完成了;否则，你需要在foo之前找到第一条记录并丢弃它和后续记录。
#2
2
You could use a single CASE/WHEN query to see if the first query returns enough. If so, use the THEN block. If not use the ELSE block. That would save you the second roundtrip to the database.
您可以使用单个CASE / WHEN查询来查看第一个查询是否返回足够的查询。如果是这样，请使用THEN块。如果不使用ELSE块。这将为您节省第二次往返数据库。
#3
0
Would one query with ORDER BY n.created DESC, cnt DESC LIMIT 3 work? It will get the most recently created items first, and return no more than 3 of them. It's not exactly the same as what you have above, but it's pretty close...
是否可以使用ORDER BY n.created DESC，cnt DESC LIMIT 3进行查询？它将首先获取最近创建的项目，并返回不超过3个。它与上面的内容不完全相同，但它非常接近......