public function findTagsBy($params = array()) { if (isset($params['user'])) { $user = $params['user']; } $db = $this->getDb(); $tagTable = $db->getTable('Tag'); if ($user) { //roles that get private tags don't leave a row in records_tags, so separate the queries $privateRoles = unserialize(get_option('user-tags-private-roles')); if (is_null($privateRoles)) { $privateRoles = array(); } if (in_array($user->role, $privateRoles)) { $select = new Omeka_Db_Select(); $db = $this->getDb(); $select->from(array('tags' => $db->Tag), array('tags.*', 'tagCount' => 'COUNT(tags.id)'))->joinInner(array('user_records_tags' => $db->UserRecordsTags), 'user_records_tags.tag_id = tags.id', array())->group('tags.id')->where('user_records_tags.owner_id = ?', $user->id); } else { $select = $tagTable->getSelectForFindBy($params); $select->joinInner(array('user_records_tags' => $db->UserRecordsTags), 'user_records_tags.tag_id = tags.id', array()); $select->where('user_records_tags.owner_id = ?', $user->id); } if (isset($params['record'])) { $record = $params['record']; $select->where('user_records_tags.record_type = ?', get_class($record)); $select->where('user_records_tags.record_id = ?', $record->id); } return $tagTable->fetchObjects($select); } //get just the tags that are associated with any user $select = new Omeka_Db_Select(); $db = $this->getDb(); $select->from(array('tags' => $db->Tag), array('tags.*', 'tagCount' => 'COUNT(tags.id)'))->joinInner(array('user_records_tags' => $db->UserRecordsTags), 'user_records_tags.tag_id = tags.id', array())->group('tags.id'); return $tagTable->fetchObjects($select); }
public function getSelectForExternalIds() { $select = new Omeka_Db_Select($this->getDb()->getAdapter()); $alias = $this->getTableAlias(); $select->from(array($alias => $this->getTableName()), "{$alias}.external_id"); return $select; }
public function findPairsForSelectForm(array $options = array()) { $db = $this->getDb(); $subquery = new Omeka_Db_Select(); $subquery->from(array('element_texts' => $db->ElementText), 'id'); $subquery->joinInner(array('elements' => $db->Element), 'elements.id = element_texts.element_id', array()); $subquery->joinInner(array('element_sets' => $db->ElementSet), 'element_sets.id = elements.element_set_id', array()); $subquery->where("element_sets.name = 'Dublin Core'"); $subquery->where("elements.name = 'Title'"); $subquery->where("element_texts.record_type = 'Collection'"); $subquery->where('element_texts.record_id = collections.id'); $subquery->limit(1); $select = $this->getSelectForFindBy($options); $select->joinLeft(array('element_texts' => $db->ElementText), "element_texts.id = ({$subquery})", array()); $select->reset(Zend_Db_Select::COLUMNS); $select->from(array(), array('collections.id', 'element_texts.text')); $select->order('element_texts.text'); $pairs = $db->fetchPairs($select); foreach ($pairs as $collectionId => &$name) { if ($name === null || $name == '') { $name = __('[Untitled] #%s', $collectionId); } else { $name = strip_formatting($name); } } return $pairs; }
public function feedItemCount($feedId) { $db = get_db(); $select = new Omeka_Db_Select(); $select->from(array('fiii' => $db->FeedImporterImportedItem), "COUNT(DISTINCT(fiii.id))"); $select->join(array('fii' => $db->FeedImporterImport), 'fii.id = fiii.import_id', array()); $select->join(array('fif' => $db->FeedImporterFeed), 'fif.id = fii.feed_id', array()); $select->where('fif.id = ?'); //echo $select; $feedItemCount = $db->fetchOne($select, array($feedId)); return $feedItemCount; }
/** * * @param unknown_type $params * newspaperIds * states * */ public function getStats($params = array()) { $select = new Omeka_Db_Select($this->getDb()->getAdapter()); $db = $this->_db; $select->from($db->NewspapersNewspaper, '*'); $select->join($db->NewspapersIssue, "{$db->NewspapersIssue}.newspaper_id = {$db->NewspapersNewspaper}.id", array()); $select->join($db->NewspapersFrontPage, "{$db->NewspapersFrontPage}.issue_id = {$db->NewspapersIssue}.id", array(new Zend_Db_Expr("std({$db->NewspapersFrontPage}.columns) as stdColumns"), new Zend_Db_Expr("avg({$db->NewspapersFrontPage}.columns) as avgColumns"), new Zend_Db_Expr("min({$db->NewspapersFrontPage}.columns) as minColumns"), new Zend_Db_Expr("max({$db->NewspapersFrontPage}.columns) as maxColumns"), new Zend_Db_Expr("max({$db->NewspapersFrontPage}.page_height) as maxPageHeight"), new Zend_Db_Expr("min({$db->NewspapersFrontPage}.page_height) as minPageHeight"), new Zend_Db_Expr("avg({$db->NewspapersFrontPage}.page_height) as avgPageHeight"), new Zend_Db_Expr("std({$db->NewspapersFrontPage}.page_height) as stdPageHeight"), new Zend_Db_Expr("max({$db->NewspapersFrontPage}.page_width) as maxPageWidth"), new Zend_Db_Expr("min({$db->NewspapersFrontPage}.page_width) as minPageWidth"), new Zend_Db_Expr("avg({$db->NewspapersFrontPage}.page_width) as avgPageWidth"), new Zend_Db_Expr("std({$db->NewspapersFrontPage}.page_width) as stdPageWidth"))); if (isset($params['states'])) { $select->where("{$db->NewspapersNewspaper}.state IN (?)", $params['states']); } if (isset($params['newspaperIds'])) { $select->where("{$db->NewspapersNewspaper}.id IN (?)", $params['newspaperIds']); } if (isset($params['columns'])) { $select->where("{$db->NewspapersFrontPage}.columns = ?", $params['columns']); } if (isset($params['columns_greater_than'])) { $select->where("{$db->NewspapersFrontPage}.columns > ", $params['columns']); } if (isset($params['columns_less_than'])) { $select->where("{$db->NewspapersFrontPage}.columns < ", $params['columns']); } //precision is iffy, so include a range if (isset($params['width'])) { $floor = $params['width'] - 500; $ceil = $params['width'] + 500; $select->where("{$db->NewspapersFrontPage}.page_width BETWEEN {$floor} AND {$ceil}"); } if (isset($params['height'])) { $floor = $params['height'] - 500; $ceil = $params['height'] + 500; $select->where("{$db->NewspapersFrontPage}.page_height BETWEEN {$floor} AND {$ceil}"); } $result = $this->_db->fetchAll($select); return $result[0]; }
/** * @internal SELECT statements should always pull a count of how many times * the tag occurs as a tagCount field in the Tag object. * * @return Omeka_Db_Select */ public function getSelect() { $select = new Omeka_Db_Select(); $db = $this->getDb(); $select->from(array('tags' => $db->Tag), array('tags.*', 'tagCount' => 'COUNT(tags.id)'))->joinInner(array('records_tags' => $db->RecordsTags), 'records_tags.tag_id = tags.id', array())->group('tags.id'); return $select; }
/** * Filter SELECT statement based on items that are not tagged with a specific * set of tags * * @param Zend_Db_Select * @param array|string Set of tag names (either array or comma-delimited string) * @return void */ public function filterByExcludedTags($select, $tags) { $db = $this->getDb(); if (!is_array($tags)) { $tags = explode(get_option('tag_delimiter'), $tags); } $subSelect = new Omeka_Db_Select(); $subSelect->from(array('items' => $db->Item), 'items.id')->joinInner(array('records_tags' => $db->RecordsTags), 'records_tags.record_id = items.id AND records_tags.record_type = "Item"', array())->joinInner(array('tags' => $db->Tag), 'records_tags.tag_id = tags.id', array()); foreach ($tags as $key => $tag) { $subSelect->where('tags.name LIKE ?', $tag); } $select->where('items.id NOT IN (' . $subSelect->__toString() . ')'); }
public function hookItemsBrowseSql($args) { $select = $args['select']; $params = $args['params']; $user = current_user(); if (!$user) { return; } if (isset($params['my-tag'])) { $tagName = $params['my-tag']; $db = $this->_db; $subSelect = new Omeka_Db_Select(); $subSelect->from(array('user_records_tags' => $db->UserRecordsTags), array('items.id' => 'user_records_tags.record_id'))->joinInner(array('tags' => $db->Tag), 'tags.id = user_records_tags.tag_id', array())->where('tags.name = ? AND user_records_tags.`record_type` = "Item"', trim($tagName)); $subSelect->where('user_records_tags.owner_id = ?', $user->id); $select->where('items.id IN (' . (string) $subSelect . ')'); } }
/** * Responds to the ListSets verb. * * Outputs setSpec and setName for all OAI-PMH sets (Omeka collections). * * @todo replace with Zend_Db_Select to allow use of limit or pageLimit */ private function listSets() { $db = get_db(); if ((bool) get_option('oaipmh_repository_expose_empty_collections')) { $collections = get_db()->getTable('Collection')->findBy(array('public' => '1')); } else { $select = new Omeka_Db_Select(); $select->from(array('collections' => $db->Collection))->joinInner(array('items' => $db->Item), 'collections.id = items.collection_id', array())->where('collections.public = 1')->where('items.public = 1')->group('collections.id'); $collections = get_db()->getTable('Collection')->fetchObjects($select); } if (count($collections) == 0) { $this->throwError(self::OAI_ERR_NO_SET_HIERARCHY); } $listSets = $this->document->createElement('ListSets'); if (!$this->error) { $this->document->documentElement->appendChild($listSets); foreach ($collections as $collection) { $name = metadata($collection, array('Dublin Core', 'Title')) ?: __('[Untitled]'); $elements = array('setSpec' => $collection->id, 'setName' => $name); $set = $listSets->appendNewElementWithChildren('set', $elements); $this->_addSetDescription($set, $collection); } } }
/** * @internal Avoid the unnecessary expense of joining if we're just counting * all the tags. */ public function getSelectForCount($params = array()) { if (!$params) { $select = new Omeka_Db_Select(); $db = $this->getDb(); $select->from(array('tags' => $db->Tag), array('COUNT(*)')); } else { $select = parent::getSelectForCount($params); } return $select; }