/home/edulekha/studygroup.edulekha.com/ow_system_plugins/base/bol/search_entity_dao.php
<?php
/**
* EXHIBIT A. Common Public Attribution License Version 1.0
* The contents of this file are subject to the Common Public Attribution License Version 1.0 (the “License”);
* you may not use this file except in compliance with the License. You may obtain a copy of the License at
* http://www.oxwall.org/license. The License is based on the Mozilla Public License Version 1.1
* but Sections 14 and 15 have been added to cover use of software over a computer network and provide for
* limited attribution for the Original Developer. In addition, Exhibit A has been modified to be consistent
* with Exhibit B. Software distributed under the License is distributed on an “AS IS” basis,
* WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License for the specific language
* governing rights and limitations under the License. The Original Code is Oxwall software.
* The Initial Developer of the Original Code is Oxwall Foundation (http://www.oxwall.org/foundation).
* All portions of the code written by Oxwall Foundation are Copyright (c) 2011. All Rights Reserved.
* EXHIBIT B. Attribution Information
* Attribution Copyright Notice: Copyright 2011 Oxwall Foundation. All rights reserved.
* Attribution Phrase (not exceeding 10 words): Powered by Oxwall community software
* Attribution URL: http://www.oxwall.org/
* Graphic Image as provided in the Covered Code.
* Display of Attribution Information is required in Larger Works which are defined in the CPAL as a work
* which combines Covered Code or portions thereof with code not governed by the terms of the CPAL.
*/
/**
* Data Access Object for `base_search_entity` table.
*
* @author Alex Ermashev <alexermashev@gmail.com>
* @package ow_system_plugins.base.bol
* @since 1.0
*/
class BOL_SearchEntityDao extends OW_BaseDao
{
/**
* Entity type
*/
const ENTITY_TYPE = 'entityType';
/**
* Entity id
*/
const ENTITY_ID = 'entityId';
/**
* Text
*/
const TEXT = 'text';
/**
* Status
*/
const STATUS = 'status';
/**
* Timestamp
*/
const TIMESTAMP = 'timeStamp';
/**
* Activated
*/
const ACTIVATED = 'activated';
/**
* Entity deleted status
*/
const ENTITY_STATUS_DELETED = 'deleted';
/**
* Entity active status
*/
const ENTITY_STATUS_ACTIVE = 'active';
/**
* Entity not active status
*/
const ENTITY_STATUS_NOT_ACTIVE = 'not_active';
/**
* Entity activated
*/
CONST ENTITY_ACTIVATED = 1;
/**
* Entity not activated
*/
CONST ENTITY_NOT_ACTIVATED = 0;
/**
* Sort by date
*/
CONST SORT_BY_DATE = 'date';
/**
* Sort by relevance
*/
CONST SORT_BY_RELEVANCE = 'relevance';
/**
* Singleton instance.
*
* @var BOL_SearchEntityDao
*/
private static $classInstance;
/**
* Full text search in boolean mode
* @var boolean
*/
private $fullTextSearchBooleanMode = true;
/**
* Returns an instance of class (singleton pattern implementation).
*
* @return BOL_SearchEntityDao
*/
public static function getInstance()
{
if ( self::$classInstance === null )
{
self::$classInstance = new self();
}
return self::$classInstance;
}
/**
* Constructor.
*/
protected function __construct()
{
parent::__construct();
}
/**
* @see OW_BaseDao::getDtoClassName()
*
*/
public function getDtoClassName()
{
return 'BOL_SearchEntity';
}
/**
* @see OW_BaseDao::getTableName()
*
*/
public function getTableName()
{
return OW_DB_PREFIX . 'base_search_entity';
}
/**
* Finds deleted entities
*
* @param integer $limit
* @return OW_Entity
*/
public function findDeletedEntities($limit = 1000)
{
$example = new OW_Example();
$example->andFieldEqual(self::STATUS, self::ENTITY_STATUS_DELETED);
$example->setLimitClause(0, $limit);
return $this->findListByExample($example);
}
/**
* Finds all entities
*
* @param integer $first
* @param integer $limit
* @param string $entityType
* @return array
*/
public function findAllEntities( $first, $limit, $entityType = null )
{
$params = array(
self::ENTITY_STATUS_DELETED
);
$sql = 'SELECT * FROM ' . $this->getTableName() . ' WHERE status <> ?';
if ( $entityType )
{
$sql .= ' AND `' . self::ENTITY_TYPE . '` = ? ';
$params = array_merge($params, array(
$entityType
));
}
$params = array_merge($params, array(
$first,
$limit
));
$sql .= ' LIMIT ?, ?';
return $this->dbo->queryForList($sql, $params);
}
/**
* Set entities status
*
* @param string $entityType
* @param string $status
* @param integer $entityId
* @return void
*/
public function setEntitiesStatus( $entityType = null, $status = self::ENTITY_STATUS_ACTIVE, $entityId = null )
{
$params = array(
$status,
self::ENTITY_STATUS_DELETED
);
$sql = 'UPDATE `' . $this->
getTableName() . '` SET `' . self::STATUS . '` = ? WHERE `' . self::STATUS . '` <> ?';
if ( $entityType )
{
$sql .= ' AND `' . self::ENTITY_TYPE . '` = ? ';
$params = array_merge($params, array(
$entityType
));
}
if ( $entityId )
{
$sql .= ' AND `' . self::ENTITY_ID . '` = ? ';
$params = array_merge($params, array(
$entityId
));
}
$this->dbo->query($sql, $params);
}
/**
* Change activation status
*
* @param string $entityType
* @param boolean $activate
* @return void
*/
public function changeActivationStatus( $entityType = null, $activate = true )
{
$params = array(
($activate ? self::ENTITY_ACTIVATED : self::ENTITY_NOT_ACTIVATED)
);
$sql = 'UPDATE `' . $this->
getTableName() . '` SET `' . self::ACTIVATED . '` = ? WHERE 1';
if ( $entityType )
{
$sql .= ' AND `' . self::ENTITY_TYPE . '` = ? ';
$params = array_merge($params, array(
$entityType
));
}
$this->dbo->query($sql, $params);
}
/**
* Change activation status by tags
*
* @param array $tags
* @param boolean $activate
* @return void
*/
public function changeActivationStatusByTags( array $tags, $activate = true )
{
$enityTags = BOL_SearchEntityTagDao::getInstance();
$params = array(
($activate ? self::ENTITY_ACTIVATED : self::ENTITY_NOT_ACTIVATED)
);
$query = '
UPDATE
' . $enityTags->getTableName() . ' AS a
INNER JOIN
' . $this->getTableName() . ' AS b
ON
a.' . BOL_SearchEntityTagDao::ENTITY_SEARCH_ID . ' = b.id
SET
b.' . self::ACTIVATED . ' = ?
WHERE
a.' . BOL_SearchEntityTagDao::ENTITY_TAG . ' IN (' . $this->dbo->mergeInClause($tags) . ')';
$this->dbo->query($query, $params);
}
/**
* Find entities count by text
*
* @param string $text
* @param array $tags
* @param integer $timeStart
* @param integer $timeEnd
* @return integer
*/
public function findEntitiesCountByText( $text, array $tags = array(), $timeStart = 0, $timeEnd = 0)
{
// sql params
$queryParams = array(
':search' => $text,
':status' => self::ENTITY_STATUS_ACTIVE,
':activated' => self::ENTITY_ACTIVATED
);
$subQueryTimeStampFilter = null;
// filter by timestamp
if ( $timeStart || $timeEnd )
{
if ( $timeStart )
{
$queryParams = array_merge($queryParams, array(
':timeStampStart' => $timeStart
));
$subQueryTimeStampFilter .= ' AND b.timeStamp >= :timeStampStart';
}
if ( $timeEnd )
{
$queryParams = array_merge($queryParams, array(
':timeStampEnd' => $timeEnd
));
$subQueryTimeStampFilter .= ' AND b.timeStamp <= :timeStampEnd';
}
}
// search without tags
if ( !$tags )
{
$subQuery = '
SELECT
b.' . self::ENTITY_TYPE . ',
b.' . self::ENTITY_ID . '
FROM
' . $this->getTableName() . ' b
WHERE
b.'. self::STATUS . ' = :status AND b.' . self::ACTIVATED . ' = :activated' . $subQueryTimeStampFilter . '
AND
MATCH (b.' . self::TEXT . ') AGAINST (:search ' . $this->getFullTextSearchMode() . ')';
}
else
{
$enityTags = BOL_SearchEntityTagDao::getInstance();
$subQuery = '
SELECT
b.' . self::ENTITY_TYPE . ',
b.' . self::ENTITY_ID . '
FROM
' . $enityTags->getTableName() . ' a
INNER JOIN
' . $this->getTableName() . ' b
ON
a.' . BOL_SearchEntityTagDao::ENTITY_SEARCH_ID . ' = b.id
AND
b.'. self::STATUS . ' = :status AND b.' . self::ACTIVATED . ' = :activated' . $subQueryTimeStampFilter . '
AND
MATCH (b.' . self::TEXT . ') AGAINST (:search ' . $this->getFullTextSearchMode() . ')
WHERE
a.' . BOL_SearchEntityTagDao::ENTITY_TAG . ' IN (' . $this->dbo->mergeInClause($tags) . ')';
}
// build main query
$query = '
SELECT
COUNT(*) as rowsCount
FROM (
SELECT
DISTINCT ' .
self::ENTITY_TYPE . ', ' .
self::ENTITY_ID . '
FROM
(' . $subQuery . ') result
)as rows';
$result = $this->dbo->queryForRow($query, $queryParams);
return !empty($result['rowsCount']) ? $result['rowsCount'] : 0;
}
/**
* Find entities by text
*
* @param string $text
* @param integer $first
* @param integer $limit
* @param array $tags
* @param string $sort
* @param boolean $sortDesc
* @param integer $timeStart
* @param integer $timeEnd
* @return array
*/
public function findEntitiesByText( $text, $first, $limit,
array $tags = array(), $sort = self::SORT_BY_RELEVANCE, $sortDesc = true, $timeStart = 0, $timeEnd = 0)
{
// sql params
$queryParams = array(
':search' => $text,
':first' => $first,
':limit' => $limit,
':status' => self::ENTITY_STATUS_ACTIVE,
':activated' => self::ENTITY_ACTIVATED
);
$subQueryTimeStampFilter = null;
// filter by timestamp
if ( $timeStart || $timeEnd )
{
if ( $timeStart )
{
$queryParams = array_merge($queryParams, array(
':timeStampStart' => $timeStart
));
$subQueryTimeStampFilter .= ' AND b.timeStamp >= :timeStampStart';
}
if ( $timeEnd )
{
$queryParams = array_merge($queryParams, array(
':timeStampEnd' => $timeEnd
));
$subQueryTimeStampFilter .= ' AND b.timeStamp <= :timeStampEnd';
}
}
// search without tags
if ( !$tags )
{
$subQuery = '
SELECT
b.' . self::ENTITY_TYPE . ',
b.' . self::ENTITY_ID . ',
MATCH (b.' . self::TEXT . ') AGAINST (:search ' . $this->getFullTextSearchMode() . ') as relevance
FROM
' . $this->getTableName() . ' b
WHERE
b.'. self::STATUS . ' = :status AND b.' . self::ACTIVATED . ' = :activated' . $subQueryTimeStampFilter . '
AND
MATCH (b.' . self::TEXT . ') AGAINST (:search ' . $this->getFullTextSearchMode() . ')
ORDER BY
' . ($sort == self::SORT_BY_DATE ? 'b.' . self::TIMESTAMP : 'relevance') . ($sortDesc ? ' DESC' : null);
}
else
{
$enityTags = BOL_SearchEntityTagDao::getInstance();
$subQuery = '
SELECT
b.' . self::ENTITY_TYPE . ',
b.' . self::ENTITY_ID . ',
MATCH (b.' . self::TEXT . ') AGAINST (:search ' . $this->getFullTextSearchMode() . ') as relevance
FROM
' . $enityTags->getTableName() . ' a
INNER JOIN
' . $this->getTableName() . ' b
ON
a.' . BOL_SearchEntityTagDao::ENTITY_SEARCH_ID . ' = b.id
AND
b.'. self::STATUS . ' = :status AND b.' . self::ACTIVATED . ' = :activated' . $subQueryTimeStampFilter . '
AND
MATCH (b.' . self::TEXT . ') AGAINST (:search ' . $this->getFullTextSearchMode() . ')
WHERE
a.' . BOL_SearchEntityTagDao::ENTITY_TAG . ' IN (' . $this->dbo->mergeInClause($tags) . ')
ORDER BY
' . ($sort == self::SORT_BY_DATE ? 'b.' . self::TIMESTAMP : 'relevance') . ($sortDesc ? ' DESC' : null);
}
// build main query
$query = '
SELECT
DISTINCT ' .
self::ENTITY_TYPE . ', ' .
self::ENTITY_ID . '
FROM
(' . $subQuery . ') result
LIMIT
:first,
:limit';
return $this->dbo->queryForList($query, $queryParams);
}
/**
* Find entities count by tags
*
* @param array $tags
* @param integer $timeStart
* @param integer $timeEnd
* @return integer
*/
public function findEntitiesCountByTags( array $tags, $timeStart = 0, $timeEnd = 0)
{
$enityTags = BOL_SearchEntityTagDao::getInstance();
// sql params
$queryParams = array(
':status' => self::ENTITY_STATUS_ACTIVE,
':activated' => self::ENTITY_ACTIVATED
);
$subQueryTimeStampFilter = null;
// filter by timestamp
if ( $timeStart || $timeEnd )
{
if ( $timeStart )
{
$queryParams = array_merge($queryParams, array(
':timeStampStart' => $timeStart
));
$subQueryTimeStampFilter .= ' AND b.timeStamp >= :timeStampStart';
}
if ( $timeEnd )
{
$queryParams = array_merge($queryParams, array(
':timeStampEnd' => $timeEnd
));
$subQueryTimeStampFilter .= ' AND b.timeStamp <= :timeStampEnd';
}
}
$subQuery = '
SELECT
b.' . self::ENTITY_TYPE . ',
b.' . self::ENTITY_ID . '
FROM
' . $enityTags->getTableName() . ' a
INNER JOIN
' . $this->getTableName() . ' b
ON
a.' . BOL_SearchEntityTagDao::ENTITY_SEARCH_ID . ' = b.id
AND
b.'. self::STATUS . ' = :status AND b.' . self::ACTIVATED . ' = :activated' . $subQueryTimeStampFilter . '
WHERE
a.' . BOL_SearchEntityTagDao::ENTITY_TAG . ' IN (' . $this->dbo->mergeInClause($tags) . ')';
// build the main query
$query = '
SELECT
COUNT(*) as rowsCount
FROM (
SELECT
DISTINCT ' .
self::ENTITY_TYPE . ', ' .
self::ENTITY_ID . '
FROM
(' . $subQuery . ') result
)as rows';
$result = $this->dbo->queryForRow($query, $queryParams);
return !empty($result['rowsCount']) ? $result['rowsCount'] : 0;
}
/**
* Find entities by tags
*
* @param array $tags
* @param integer $first
* @param integer $limit
* @param string $sort
* @param boolean $sortDesc
* @param integer $timeStart
* @param integer $timeEnd
* @return array
*/
public function findEntitiesByTags( array $tags, $first, $limit,
$sort = self::SORT_BY_DATE, $sortDesc = true, $timeStart = 0, $timeEnd = 0)
{
// sql params
$queryParams = array(
':first' => $first,
':limit' => $limit,
':status' => self::ENTITY_STATUS_ACTIVE,
':activated' => self::ENTITY_ACTIVATED
);
$subQueryTimeStampFilter = null;
// filter by timestamp
if ( $timeStart || $timeEnd )
{
if ( $timeStart )
{
$queryParams = array_merge($queryParams, array(
':timeStampStart' => $timeStart
));
$subQueryTimeStampFilter .= ' AND b.timeStamp >= :timeStampStart';
}
if ( $timeEnd )
{
$queryParams = array_merge($queryParams, array(
':timeStampEnd' => $timeEnd
));
$subQueryTimeStampFilter .= ' AND b.timeStamp <= :timeStampEnd';
}
}
$enityTags = BOL_SearchEntityTagDao::getInstance();
$subQuery = '
SELECT
b.' . self::ENTITY_TYPE . ',
b.' . self::ENTITY_ID . '
FROM
' . $enityTags->getTableName() . ' a
INNER JOIN
' . $this->getTableName() . ' b
ON
a.' . BOL_SearchEntityTagDao::ENTITY_SEARCH_ID . ' = b.id
AND
b.'. self::STATUS . ' = :status AND b.' . self::ACTIVATED . ' = :activated' . $subQueryTimeStampFilter . '
WHERE
a.' . BOL_SearchEntityTagDao::ENTITY_TAG . ' IN (' . $this->dbo->mergeInClause($tags) . ')
ORDER BY
b.' . self::TIMESTAMP . ($sortDesc ? ' DESC' : null);
// build main query
$query = '
SELECT
DISTINCT ' .
self::ENTITY_TYPE . ', ' .
self::ENTITY_ID . '
FROM
(' . $subQuery . ') result
LIMIT
:first,
:limit';
return $this->dbo->queryForList($query, $queryParams);
}
/**
* Set entities status by tags
*
* @param array $tags
* @param string $status
* @return void
*/
public function setEntitiesStatusByTags( array $tags, $status )
{
$enityTags = BOL_SearchEntityTagDao::getInstance();
$params = array(
':deleted_status' => self::ENTITY_STATUS_DELETED,
':status' => $status
);
$query = '
UPDATE
' . $enityTags->getTableName() . ' AS a
INNER JOIN
' . $this->getTableName() . ' AS b
ON
a.' . BOL_SearchEntityTagDao::ENTITY_SEARCH_ID . ' = b.id
AND
b.'. self::STATUS . ' <> :deleted_status
SET
b.' . self::STATUS . ' = :status
WHERE
a.' . BOL_SearchEntityTagDao::ENTITY_TAG . ' IN (' . $this->dbo->mergeInClause($tags) . ')';
$this->dbo->query($query, $params);
}
/**
* Optimize table
*
* @return void
*/
public function optimizeTable()
{
$this->dbo->query('OPTIMIZE TABLE ' . $this->getTableName());
}
/**
* Get full text search mode
*
* @return string
*/
protected function getFullTextSearchMode()
{
return $this->fullTextSearchBooleanMode ? 'IN BOOLEAN MODE' : 'IN NATURAL LANGUAGE MODE';
}
}