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; }
/** * Add `hasSlug` parameter to records API. * * @param Omeka_Db_Select $select The original select. * @param array $args Includes `params`, the API query parameters. * @return Omeka_Db_Select The modified select. */ public function filterNeatlineQueryRecords($select, $args) { // Filter out records without slugs. if (isset($args['params']['hasSlug'])) { $select->where('slug IS NOT NULL'); } 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; }
/** * Apply the permissions to an SQL select object. * * @param Omeka_Db_Select $select * @param string $alias Table alias to query against * @param string $ownerColumn Optional column for checking for ownership. If * falsy, the ownership check is skipped. * @param string $publicColumn Optional column for storing public status. The * column must represent "public" status as the value 1. */ public function apply(Omeka_Db_Select $select, $alias, $ownerColumn = 'owner_id', $publicColumn = 'public') { // If the current user has the 'all' permission, we don't need to do // anything. If the 'all' permission's neither true nor false, we // _shouldn't_ do anything, because the ACL isn't loaded. if ($this->_allPermission || $this->_allPermission === null) { return; } if ($ownerColumn && $this->_selfPermission) { $select->where("{$alias}.{$publicColumn} = 1 OR {$alias}.{$ownerColumn} = ?", $this->_currentUser->id); } else { $select->where("{$alias}.{$publicColumn} = 1"); } }
/** * @param Omeka_Db_Select $select * @param array $params */ public function applySearchFilters($select, $params) { $alias = $this->getTableAlias(); $paramNames = array('record_id', 'rating', 'user_id'); foreach ($paramNames as $paramName) { if (isset($params[$paramName])) { $select->where($alias . '.' . $paramName . ' = ?', array($params[$paramName])); } } if (isset($params['sort'])) { switch ($params['sort']) { case 'asc': $select->order("{$alias}.rating ASC"); break; case 'dsc': $select->order("{$alias}.rating DSC"); break; } } }
/** * Define filters for browse and findBy. * * Available filters are: "tag" or "tags", "public" and "featured". "sort" * also adds specific sorting strategies "alpha" and "recent", but the * normal sorting can also be used. * * @param Omeka_Db_Select $select * @param array $params Key-value array of search parameters * @return Omeka_Db_Select */ public function applySearchFilters($select, $params) { $db = $this->getDb(); foreach ($params as $paramName => $paramValue) { switch ($paramName) { case 'tag': case 'tags': $tags = explode(',', $paramValue); $select->joinInner(array('tg' => $db->RecordsTags), 'tg.record_id = exhibits.id', array()); $select->joinInner(array('t' => $db->Tag), "t.id = tg.tag_id", array()); foreach ($tags as $k => $tag) { $select->where('t.name = ?', trim($tag)); } $select->where("tg.record_type = ? ", array('Exhibit')); break; case 'public': $this->filterByPublic($select, $params['public']); break; case 'range': $this->filterByRange($select, $params['range']); break; case 'featured': $this->filterByFeatured($select, $params['featured']); break; } } return $select; }
/** * Can specify a range of valid Exhibit IDs or an individual ID * * @param Omeka_Db_Select $select * @param string $range Example: 1-4, 75, 89 * @return void */ public function filterByRange($select, $range) { // Comma-separated expressions should be treated individually $exprs = explode(',', $range); // Construct a SQL clause where every entry in this array is linked by 'OR' $wheres = array(); foreach ($exprs as $expr) { // If it has a '-' in it, it is a range of item IDs. Otherwise it is // a single item ID if (strpos($expr, '-') !== false) { list($start, $finish) = explode('-', $expr); // Naughty naughty koolaid, no SQL injection for you $start = (int) trim($start); $finish = (int) trim($finish); $wheres[] = "(exhibits.id BETWEEN {$start} AND {$finish})"; //It is a single item ID } else { $id = (int) trim($expr); $wheres[] = "(exhibits.id = {$id})"; } } $where = join(' OR ', $wheres); $select->where('(' . $where . ')'); }
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; }
/** * This adds the joins and where clauses to respect an addon's privacy * settings. * * @param Omeka_Db_Select $select The select object to modify. * @param SolrSearch_Addon_Addon $addon The current addon. You should * already know that this addon does have a public flag somewhere in its * hierarchy before calling this. * * @return null * @author Eric Rochester <*****@*****.**> **/ private function _addFlag($select, $addon) { if (!is_null($addon->flag)) { $table = $this->db->getTable($addon->table); $select->where("`{$table->getTableName()}`.`{$addon->flag}`=1"); } else { if (!is_null($addon->parentAddon)) { $parent = $addon->parentAddon; $table = $this->db->getTable($addon->table)->getTableName(); $ptable = $this->db->getTable($parent->table)->getTableName(); $select->join($ptable, "`{$table}`.`{$addon->parentKey}`=`{$ptable}`.`{$parent->idColumn}`", array()); $this->_addFlag($select, $parent); } } }
/** * Manipulate a Select object based on a set of criteria. * * @param Omeka_Db_Select $select * @param array $params Possible parameters include: * <ul> * <li>record_types - array - Usually one or more of the following: * All, Item, File</li> * <li>sort - string - One of the following values: alpha</li> * <li>element_set_name - string - Name of the element set to which * results should belong.</li> * </ul> */ public function applySearchFilters($select, $params) { $db = $this->getDb(); // Retrieve only elements matching a specific record type. if (array_key_exists('record_types', $params)) { $where = array(); foreach ($params['record_types'] as $recordTypeName) { if ($recordTypeName == 'All') { $where[] = 'element_sets.record_type IS NULL'; } else { $where[] = 'element_sets.record_type = ' . $db->quote($recordTypeName); } } $select->where('(' . join(' OR ', $where) . ')'); } if (array_key_exists('sort', $params)) { if ($params['sort'] == 'alpha') { $select->order('elements.name ASC'); } else { if ($params['sort'] == 'alphaBySet') { $select->order('element_sets.name ASC')->order('elements.name ASC'); } } } if (array_key_exists('element_set_name', $params)) { $select->where('element_sets.name = binary ?', (string) $params['element_set_name']); } if (array_key_exists('element_name', $params)) { $select->where('elements.name = binary ?', (string) $params['element_name']); } // Retrive results including, but not limited to, a specific item type. if (array_key_exists('item_type_id', $params)) { $select->joinLeft(array('item_types_elements' => $db->ItemTypesElements), 'item_types_elements.element_id = elements.id', array()); $select->where('item_types_elements.item_type_id = ? OR item_types_elements.item_type_id IS NULL', (int) $params['item_type_id']); } else { if (array_key_exists('exclude_item_type', $params)) { $select->where('element_sets.name != ?', ElementSet::ITEM_TYPE_NAME); } else { if (array_key_exists('item_type', $params)) { //for the API for item_types $select->joinLeft(array('item_types_elements' => $db->ItemTypesElements), 'item_types_elements.element_id = elements.id', array()); $select->where('item_types_elements.item_type_id = ? ', (int) $params['item_type']); } } } // REST API params. if (array_key_exists('name', $params)) { $select->where("elements.name = ?", $params['name']); } if (array_key_exists('element_set', $params)) { $select->where("elements.element_set_id = ?", $params['element_set']); } }
/** * 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() . ')'); }
/** * 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); } } }
/** * Apply an element filter to the select object. * * @see self::applySearchFilters() * @param Omeka_Db_Select $select * @param string|array $recordType One or multiple record types. * May be a "0" for non element change. */ public function filterByRecordType(Omeka_Db_Select $select, $recordType) { if (empty($recordType)) { return; } // Reset elements to ids. if (!is_array($recordType)) { $recordType = array($recordType); } $alias = $this->getTableAlias(); // One change. if (count($recordType) == 1) { $select->where("`{$alias}`.`record_type` = ?", reset($recordType)); } else { $select->where("`{$alias}`.`record_type` IN (?)", $recordType); } }
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 . ')'); } }
/** * * @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; }
/** * @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; }