Geek Answers Handbook

If the db query A does not return enough results, run query B: how to optimize?

I am looking for a good design template or best practice to achieve a situation of “either this request or another request”, with maximum performance and lowest cost.

The business logic / demans program says “all elements with Foo” unless it returns less than three elements and then “all elements”. I am refactoring the current code and cannot find a good way to achieve this logic.

Current Pseudocode (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 seems "wrong", primarily because of DRY: it contains the same request with one minor difference. I can change this by building smarter queries.

, ( ), , db.

?

+5
3

, , " , db", , , . , , , , .

ORDER BY n.created DESC, , foo, ; foo .

+4

CASE/WHEN, , . , THEN. ELSE. .

+2

Will one query work with ORDER BY n.created DESC, cnt DESC LIMIT 3? First, he will receive the most recently created items and return no more than 3 of them. This is not exactly the same as yours above, but it is pretty close ...

0
source

More articles:


All Articles