PHP sql::in方法代码示例

 public function testInRepeated()
 {
     $params = array();
     $sql = sql::in(array(1, 1, 2, 2), $params);
     $this->assertEquals('IN (?, ?)', $sql);
     $this->assertEquals(array(1, 2), $params);
 }

 /**
  * Открепление файлов от ноды.
  */
 public static function on_post_detach(Context $ctx)
 {
     if (is_array($ids = $ctx->post('remove'))) {
         $ctx->db->beginTransaction();
         $params = array();
         Node::load($ctx->get('id'), $ctx->db)->touch(ACL::UPDATE)->onSave('DELETE FROM `node__rel` WHERE `tid` = %ID% AND `key` IS NULL AND `nid` ' . sql::in($ids, $params), $params)->save();
         $ctx->db->commit();
     }
     return $ctx->getRedirect();
 }

 /**
  * Обновляет XML при изменении объекта.
  * @mcms_message ru.molinos.cms.hook.node
  */
 public static function on_node_change(Context $ctx, Node $node, $op)
 {
     if ($parents = Node::getNodeParentIds($node->getDB(), $node->id)) {
         $params = array();
         $node->getDB()->exec($sql = "UPDATE `node` SET `xmltree` = NULL WHERE `id` " . sql::in($parents, $params));
         $upd = $node->getDB()->prepare("UPDATE `node` SET `xmltree` = ? WHERE `id` = ?");
         while ($id = array_pop($parents)) {
             $upd->execute(array(Node::load($id, $node->getDB())->getTreeXML(false), $id));
         }
     }
 }

 /**
  * Вызов api/ads/get.xml
  *
  * Возвращает случайные баннеры.
  *
  * @route GET//api/ads/get.xml
  */
 public static function on_get_banners(Context $ctx)
 {
     $limit = intval($ctx->get('limit', 1));
     $sql = "SELECT `node`.`id`, `xml` FROM `node` " . "INNER JOIN `node__banners` ON `node__banners`.`id` = `node`.`id` " . "WHERE `class` = 'banner' AND `deleted` = 0 AND `published` = 1 " . "AND (`time_limit` IS NULL OR `time_limit` < ?) " . "AND (`display_limit` IS NULL OR `display_count` IS NULL OR `display_count` < `display_limit`) " . "ORDER BY RAND() LIMIT " . $limit;
     $data = $ctx->db->getResultsKV("id", "xml", $sql, array(mcms::now()));
     $params = array();
     $ctx->db->beginTransaction();
     $ctx->db->exec("UPDATE `node__banners` SET `display_count` = 0 WHERE `display_count` IS NULL");
     $ctx->db->exec("UPDATE `node__banners` SET `display_count` = `display_count` + 1 WHERE `id` " . sql::in(array_keys($data), $params), $params);
     $ctx->db->commit();
     return new Response(html::em('nodes', implode('', $data)), 'text/xml');
 }

 private static function write_root(Context $ctx, $f)
 {
     $types = $ctx->db->getResultsV("name", "SELECT `name` FROM `node` WHERE `class` = 'type' AND `deleted` = 0 AND `published` = 1");
     $params = array();
     $max = $ctx->db->fetch("SELECT MAX(updated) FROM `node` WHERE `published` = 1 AND `deleted` = 0 AND `class` " . sql::in($types, $params), $params);
     $line = "<!-- root -->\n<url>" . "<loc>http://" . MCMS_HOST_NAME . "/</loc>";
     $date = gmdate('c', strtotime($max));
     $line .= '<changefreq>hourly</changefreq>';
     $line .= "<lastmod>{$date}</lastmod>";
     $line .= "</url>\n";
     fwrite($f, $line);
 }

 /**
  * Возвращает количество комментариев.
  * @route GET//api/comment/count.xml
  */
 public static function on_count_comments(Context $ctx)
 {
     $result = '';
     if ($ids = explode(',', $ctx->get('node'))) {
         $params = array();
         if ($data = $ctx->db->getResults($sql = "SELECT n.id AS `id`, COUNT(*) AS `count` FROM node n INNER JOIN node__rel l ON l.tid = n.id INNER JOIN node c ON c.id = l.nid WHERE n.deleted = 0 AND n.published = 1 AND c.deleted = 0 AND c.published = 1 AND c.class = 'comment' AND `n`.`id` " . sql::in($ids, $params) . " GROUP BY n.id", $params)) {
             foreach ($data as $row) {
                 $result .= html::em('node', $row);
             }
         }
     }
     return new Response(html::em('counts', $result), 'text/xml');
 }

 /**
  * Изменяет список разделов, разрешённых для ноды.
  */
 public static function on_post_setup(Context $ctx)
 {
     if (!($node = $ctx->get('node'))) {
         throw new BadRequestException();
     }
     $ctx->db->beginTransaction();
     $ctx->db->exec("DELETE FROM `node__rel` WHERE `nid` = ? AND `tid` IN (SELECT `id` FROM `node` WHERE `class` = 'tag')", array($node));
     $params = array($node);
     $sql = "INSERT INTO `node__rel` (`tid`, `nid`) SELECT `id`, ? FROM `node` WHERE `id` " . sql::in($ctx->post('selected'), $params);
     $ctx->db->exec($sql, $params);
     $ctx->db->commit();
     return $ctx->getRedirect();
 }

 private static function get_ids(Node $node)
 {
     $ids = array($node->id);
     while (true) {
         $params = array();
         $sql = "SELECT DISTINCT `id` FROM `node` WHERE `deleted` = 0 AND `id` IN " . "(SELECT `tid` FROM `node__rel` WHERE `nid` " . sql::in($ids, $params) . ")" . " AND `id` " . sql::notIn($ids, $params);
         $rows = $node->getDB()->getResultsV("id", $sql, $params);
         if (empty($rows)) {
             break;
         }
         foreach ($rows as $id) {
             $ids[] = $id;
         }
     }
     $params = array();
     $ids = (array) $node->getDB()->getResultsV("id", "SELECT `id` FROM `node` WHERE `class` NOT IN ('tag', 'label') AND `id` " . sql::in($ids, $params), $params);
     return array_reverse($ids);
 }

 /**
  * Формирует таблицу для предварительного просмотра.
  */
 public function getPreviewXML(Context $ctx)
 {
     $xml = parent::getPreviewXML($ctx);
     if (is_array($this->orderdetails)) {
         $params = array();
         $names = $this->getDB()->getResultsKV("id", "name", "SELECT `id`, `name` FROM `node` WHERE `id` " . sql::in(array_keys($this->orderdetails), $params), $params);
         $table = '';
         foreach ($this->orderdetails as $k => $v) {
             $name = isset($names[$k]) ? $names[$k] : '???';
             $row = html::em('td', html::em('a', array('href' => "admin/node/{$k}?destination=CURRENT"), html::plain($name)));
             $row .= html::em('td', html::cdata('× ' . $v));
             $table .= html::em('tr', $row);
         }
         if ($value = html::wrap('table', $table, array('class' => 'classic'))) {
             $xml .= html::em('field', array('title' => t('Содержимое заказа')), html::em('value', array('html' => true), html::cdata($value)));
         }
     }
     return $xml;
 }

 /**
  * Формирует обратное облако (используемых объектов). Применяется, например,
  * для оценки редакторской активности.
  */
 public static function on_get_cloud_rev_xml(Context $ctx)
 {
     list($st, $tt, $limit, $cache) = self::get_params($ctx);
     if ($cache) {
         $ckey = 'cloud/rev';
         if ($ctx->get('extended')) {
             $ckey .= '/ext';
         }
         $ttl = floor(time() / $cache);
         $ckey = sprintf($ckey . '/%s/%s/%u/%u', $st, $tt, $limit, floor(time() / $cache));
         if ($cached = cache::getInstance()->{$ckey}) {
             return new Response($cached, 'text/xml');
         }
     }
     $params = array();
     $sql1 = sql::in(explode(' ', $st), $params);
     $sql2 = sql::in(explode(' ', $tt), $params);
     $data = $ctx->db->getResults($sql = 'SELECT n.id AS id, n.name AS name, ' . 'COUNT(*) AS `cnt` ' . 'FROM node n ' . 'INNER JOIN node__rel r ON r.nid = n.id ' . 'WHERE n.class ' . $sql1 . ' ' . 'AND n.published = 1 ' . 'AND n.deleted = 0 ' . 'AND r.tid IN (SELECT id FROM node WHERE published = 1 AND deleted = 0 AND class ' . $sql2 . ') ' . 'GROUP BY n.id, n.name ' . 'ORDER BY cnt DESC LIMIT ' . $limit, $params);
     // Идентификаторы объектов, для получения расширенной информации
     $nids = array();
     // Считаем общее количество объектов.
     $count = 0;
     foreach ($data as $item) {
         $count += $item['cnt'];
         $nids[] = $item['id'];
     }
     $percent = $count / 100;
     $nodes = $ctx->get('extended') ? Node::find(array('id' => $nids), $ctx->db) : array();
     $result = '';
     $keys = array();
     foreach ($data as $item) {
         $p = round($item['cnt'] / $percent);
         $name = isset($nodes[$item['id']]) ? $nodes[$item['id']]->getName() : $item['name'];
         $result .= html::em('item', array('id' => $item['id'], 'name' => trim($name), 'count' => $item['cnt'], 'percent' => $p, 'weight' => round($p / 10) + 1));
         $keys[] = $item['id'];
     }
     $xml = html::em('cloud', array('total' => $count), $result);
     if ($cache) {
         cache::getInstance()->{$ckey} = $xml;
     }
     return new Response($xml, 'text/xml');
 }

 public function set($value, &$node)
 {
     $this->validate($value);
     $fieldName = $this->value . '*';
     $node->onSave("DELETE FROM `node__rel` WHERE `nid` = %ID% AND `key` = ?", array($fieldName));
     if (empty($value)) {
         unset($node->{$this->value});
     } else {
         $result = array();
         $labels = preg_split('/,\\s*/', $value, -1, PREG_SPLIT_NO_EMPTY);
         foreach ($labels as $label) {
             try {
                 $label = trim($label);
                 $tmp = Node::load($f = array('class' => 'label', 'name' => $label, 'deleted' => 0), $node->getDB());
             } catch (ObjectNotFoundException $e) {
                 $tmp = Node::create(array('class' => 'label', 'name' => $label, 'published' => 1), $node->getDB())->save();
             }
             $result[$tmp->id] = $tmp->name;
         }
         $params = array($fieldName);
         $node->onSave($sql = "INSERT INTO `node__rel` (`nid`, `tid`, `key`) SELECT %ID%, `id`, ? FROM `node` WHERE `class` = 'label' AND `id` " . sql::in(array_keys($result), $params), $params);
         $node->{$this->value} = $result;
     }
 }

 private function getTagsFilter($id)
 {
     if (is_string($id) and '+' == substr($id, -1)) {
         $sql = "IN (SELECT `n`.`id` FROM `node` `n`, `node` `t` WHERE `n`.`class` = 'tag' AND `n`.`left` >= `t`.`left` AND `n`.`right` <= `t`.`right` AND `t`.`id` = ? AND `n`.`deleted` = 0 AND `n`.`published` = 1)";
         $this->params[] = intval($id);
     } else {
         $sql = sql::in($id, $this->params);
     }
     return $sql;
 }

 public static function on_post_sendto(Context $ctx)
 {
     if ($pick = $ctx->post('selected')) {
         if (false === strpos($ctx->post('sendto'), '.')) {
             list($nid, $fieldName) = array($ctx->post('sendto'), null);
         } else {
             list($nid, $fieldName) = explode('.', $ctx->post('sendto'));
         }
         $params = array($fieldName);
         $sql = "REPLACE INTO `node__rel` (`tid`, `nid`, `key`) SELECT %ID%, `id`, ? FROM `node` WHERE `deleted` = 0 AND `id` " . sql::in($pick, $params);
         $ctx->db->beginTransaction();
         $node = Node::load($nid)->knock(ACL::UPDATE);
         if (isset($node->{$fieldName})) {
             unset($node->{$fieldName});
         }
         $node->onSave("DELETE FROM `node__rel` WHERE `tid` = %ID% AND `key` = ?", array($fieldName))->onSave($sql, $params)->save();
         $ctx->db->commit();
         // destiantion сейчас указывает на список, а нам надо
         // вернуться на уровень выше.
         $url = new url($ctx->get('destination'));
         if ($next = $url->arg('destination')) {
             $ctx->redirect($next);
         }
     }
     return $ctx->getRedirect();
 }

 /**
  * Сбрасывает права для указанных объектов.
  */
 public static function resetNode(array $nodeIds)
 {
     $params = array();
     Context::last()->db->exec("DELETE FROM `node__access` WHERE `nid` " . sql::in($nodeIds, $params), $params);
     self::flush();
 }

 /**
  * Привязывает объекты к текущему.
  */
 public function link(array $ids, $replace = true, $field = null)
 {
     if ($replace) {
         $this->onSave("DELETE FROM {node__rel} WHERE `tid` = %ID% AND `key` = ?", array($field));
     }
     $params = array($field);
     $this->onSave("INSERT INTO `node__rel` (`tid`, `nid`, `key`) SELECT %ID%, `id`, ? FROM `node` WHERE `id` " . sql::in($ids, $params), $params);
     return $this;
 }