PHP SQLSelect::AddGroup方法代码示例

  function LoadFromDB() {
    $s = new SQLSelect();

    $this->group = PouetGroup::Spawn($this->id);
    $this->addeduser = PouetUser::Spawn($this->group->addedUser);

    // not to boast or anything, but this is fucking beautiful.

    $sub = new SQLSelect();
    $sub->AddField("max(comments.addedDate) as maxDate");
    $sub->AddField("comments.which");
    $sub->AddTable("comments");
    $sub->AddJoin("left","prods","prods.id = comments.which");
    //$sub->AddOrder("comments.addedDate desc");
    $sub->AddGroup("comments.which");
    $sub->AddWhere(sprintf_esc("(prods.group1 = %d) or (prods.group2 = %d) or (prods.group3 = %d)",$this->id,$this->id,$this->id));

    $s = new BM_Query("prods");
    $s->AddField("cmts.addedDate as lastcomment");
    $s->AddField("cmts.rating as lastcommentrating");
    $s->AddJoin("left","(select comments.addedDate,comments.who,comments.which,comments.rating from (".$sub->GetQuery().") as dummy left join comments on dummy.maxDate = comments.addedDate and dummy.which = comments.which) as cmts","cmts.which=prods.id");
    $s->attach(array("cmts"=>"who"),array("users as user"=>"id"));
    $s->AddWhere(sprintf_esc("(prods.group1 = %d) or (prods.group2 = %d) or (prods.group3 = %d)",$this->id,$this->id,$this->id));

    $r = !!$_GET["reverse"];
    switch($_GET["order"])
    {
      case "type": $s->AddOrder("prods.type ".($r?"DESC":"ASC")); break;
      case "party": $s->AddOrder("prods_party.name ".($r?"DESC":"ASC")); $s->AddOrder("prods.party_year ".($r?"DESC":"ASC")); $s->AddOrder("prods.party_place ".($r?"DESC":"ASC")); break;
      case "release": $s->AddOrder("prods.releaseDate ".($r?"ASC":"DESC")); break;
      case "thumbup": $s->AddOrder("prods.voteup ".($r?"ASC":"DESC")); break;
      case "thumbpig": $s->AddOrder("prods.votepig ".($r?"ASC":"DESC")); break;
      case "thumbdown": $s->AddOrder("prods.votedown ".($r?"ASC":"DESC")); break;
      case "avg": $s->AddOrder("prods.voteavg ".($r?"ASC":"DESC")); break;
      case "views": $s->AddOrder("prods.views ".($r?"ASC":"DESC")); break;
      case "latestcomment": $s->AddOrder("lastcomment ".($r?"ASC":"DESC")); break;
      default: $s->AddOrder("prods.name ".($r?"DESC":"ASC")); break;
    }
    $this->prods = $s->perform();
    PouetCollectPlatforms($this->prods);
    PouetCollectAwards($this->prods);

    $s = new BM_Query("affiliatedboards");
    $s->attach(array("affiliatedboards"=>"board"),array("boards as board"=>"id"));
    $s->AddWhere(sprintf_esc("affiliatedboards.group=%d",$this->id));
    $this->affil = $s->perform();
  }

<?
require_once("bootstrap.inc.php");

header("Content-type: application/json; charset=utf-8");

$sql = new SQLSelect();
$sql->AddField("party_compo");
$sql->AddTable("prods");
$sql->AddGroup("party_compo");
$sql->AddWhere(sprintf_esc("party=%d",$_GET["party"]));
$sql->AddWhere(sprintf_esc("party_year=%d",$_GET["year"]));

$r = SQLLib::selectRows( $sql->GetQuery() );

echo json_encode( array("compos"=>array_map(function($i){ return (int)$i->party_compo; },$r)) );
?>

function pouetAdmin_recacheTopDemos()
{
  global $timer;
  
  // this needs to be made faster. a LOT faster.
  $total = array();

  // list by views
  $timer["recache_views"]["start"] = microtime_float();
  $i=0;
  $query="SELECT id,name,views FROM prods ORDER BY views DESC";
  $result = SQLLib::Query($query);
  $content = "<ol>";
  while($tmp = SQLLib::Fetch($result)) {
    $total[$tmp->id]+=$i;
    $i++;
    if ($i<=5)
      $content .= "<li><b>"._html($tmp->name)."</b> - ".$tmp->views." views</li>\n";
  }
  $content .= "</ol>";
  $content .= "<h3>".$i." prod views loaded</h3>\n";
  $timer["recache_views"]["end"] = microtime_float();

  $i=0;
  // Get the list of prod IDs ordered by the sum of their comment ratings
  $sql = new SQLSelect();
  $sql->AddField("prods.id");
  $sql->AddField("prods.name");
  $sql->AddField("SUM(comments.rating) as theSum");
  $sql->AddTable("prods");
  $sql->AddJoin("","comments","prods.id = comments.which");
  $sql->AddGroup("prods.id");
  $sql->AddOrder("SUM(comments.rating) DESC");

  $timer["recache_votes"]["start"] = microtime_float();
  $result = SQLLib::Query( $sql->GetQuery() );
  $content .= "<ol>";
  while($tmp = SQLLib::Fetch($result)) {
    $total[$tmp->id]+=$i;
    $i++;
    if ($i<=5)
      $content .= "<li><b>"._html($tmp->name)."</b> - "._html($tmp->theSum)." votes</li>\n";
  }
  $content .= "</ol>";
  $content .= "<h3>".$i." vote counts loaded</h3>\n";
  $timer["recache_votes"]["end"] = microtime_float();

  $timer["recache_sort"]["start"] = microtime_float();
  asort($total);
  $timer["recache_sort"]["end"] = microtime_float();

  $timer["recache_update"]["start"] = microtime_float();
  $i=1;
  unset($tmp);
  unset($top_demos);
  $a = array();
  while ((list ($key, $val)=each($total))) 
  {
    $a[] = array(
      "id" => $key,
      "rank" => $i,
    );
    if (count($a) == 100)
    {
      SQLLib::UpdateRowMulti("prods","id",$a);
      $a = array();
    }
    $i++;
  }
  SQLLib::UpdateRowMulti("prods","id",$a);
  $content .= "<h3>".$i." prod rankings updated</h3>\n";
  $timer["recache_update"]["end"] = microtime_float();

  @unlink('cache/pouetbox_topalltime.cache');
  @unlink('cache/pouetbox_topmonth.cache');
  return $content;
}