public function retrieveFolderList(Doctrine_Query $q)
{
$alias = $q->getRootAlias();
$q->leftJoin($alias . '.Assets a');
$q->orderBy($alias . '.lft');
return $q;
}
/**
* @param $orderby
*/
protected function setOrderBy($orderby)
{
if ($this->_orderby_run) {
return;
}
$this->_query->orderBy('f.created_at DESC');
$this->_orderby_run = true;
}
public function testQueryRemoveOrderByPart()
{
$q = new Doctrine_Query();
$q->from('User u');
$q->orderBy('u.id DESC');
$q->removeDqlQueryPart('orderby');
$this->assertEqual($q->getSqlQuery(), 'SELECT e.id AS e__id, e.name AS e__name, e.loginname AS e__loginname, e.password AS e__password, e.type AS e__type, e.created AS e__created, e.updated AS e__updated, e.email_id AS e__email_id FROM entity e WHERE (e.type = 0)');
}
public function addUpcoming(Doctrine_Query $q, $limit = null)
{
$q->orderBy('start_date');
$q->addWhere('DATE(start_date) >= DATE(NOW())');
if (!is_null($limit)) {
$q->limit($limit);
}
}
public function orderBy($orderby)
{
$validCharacters = array('.', ' ', ',', '_');
$string = str_replace($validCharacters, '', $orderby);
if (!ctype_alnum($string)) {
return;
}
return parent::orderBy($orderby);
}
public function orderBy($order)
{
if (strlen(strstr($order, 'due_amount')) > 0) {
$this->addSelect("c.id, SUM(i.gross_amount-i.paid_amount) AS due_amount");
}
if (strlen(strstr($order, 'gross_amount')) > 0) {
$this->addSelect("c.id, SUM(if(i.gross_amount is null,0,i.gross_amount)) as gross_amount");
}
return parent::orderBy($order);
}
function buildQuery(Doctrine_Query $query)
{
$query->leftJoin('swBlogPost.Translation');
$query->leftJoin('swBlogPost.swBlogPostTags t');
$query->leftJoin('t.Translation tt');
if (strlen($this->getValue('tag')) > 0) {
$query->addWhere('tt.name = ? and tt.lang = ?', array($this->getValue('tag'), sfContext::getInstance()->getUser()->getCulture()));
}
if (!is_null($this->getValue('published'))) {
$query->addWhere('swBlogPost.published = ?', $this->getValue('published'));
}
$query->orderBy('created_at DESC');
return $query;
}
/**
* @param $orderby
*/
protected function setOrderBy()
{
if ($this->_orderby_run) {
return;
}
switch ($this->_order_by) {
case 'gallery_ordering':
if ($this->_gallery_added) {
$this->_query->orderBy('s.ordering ' . $this->_order_direction);
}
break;
default:
$this->_query->orderBy('f.' . $this->_order_by . ' ' . $this->_order_direction);
}
$this->_orderby_run = true;
}
/**
* @return
*/
protected function setOrderBy()
{
if ($this->_orderby_run) {
return;
}
switch ($this->_order_by) {
case 'slice_title':
case 'slice_filesize':
case 'slice_xsize':
case 'slice_ysize':
case 'slice_updated_at':
case 'slice_created_at':
$this->_query->orderBy('s.' . str_replace('slice_', '', $this->_order_by) . ' ' . $this->_order_direction);
break;
case 'file_title':
case 'file_filesize':
case 'file_xsize':
case 'file_ysize':
case 'file_updated_at':
case 'file_created_at':
$this->_query->leftJoin('s.File f');
$this->_query->orderBy('f.' . str_replace('file_', '', $this->_order_by) . ' ' . $this->_order_direction);
break;
case 'views':
$this->_query->leftJoin('s.File f');
$this->_query->leftJoin('f.Views v');
$this->_query->orderBy('v.count ' . $this->_order_direction);
break;
case 'loves':
$this->_query->leftJoin('s.File f');
$this->_query->leftJoin('f.Loves l');
$this->_query->orderBy('l.count ' . $this->_order_direction);
break;
case 'random':
$this->_query->select('s.*, RANDOM() as rand');
$this->_query->orderby('rand');
break;
case 'gallery_ordering':
$this->_query->orderBy('s.ordering');
}
$this->_orderby_run = true;
}
function buildQuery(Doctrine_Query $query)
{
$query->orderBy('swBlogComment.created_at ASC');
$query->leftJoin('swBlogComment.swBlogPost');
if ($this->getValue('moderated') == -2) {
$query->whereIn('swBlogComment.moderated', array(swBlogComment::MODERATED_NONE, swBlogComment::MODERATED_OK));
}
if ($this->getValue('moderated') == -1) {
$query->whereIn('swBlogComment.moderated', array(swBlogComment::MODERATED_NONE));
}
if ($this->getValue('moderated') == -3) {
$query->whereIn('swBlogComment.moderated', array(swBlogComment::MODERATED_KO, swBlogComment::MODERATED_OK));
}
if ($this->getValue('moderated') == 1) {
$query->whereIn('swBlogComment.moderated', array(swBlogComment::MODERATED_OK));
}
if ($this->getValue('moderated') === 0) {
$query->whereIn('swBlogComment.moderated', array(swBlogComment::MODERATED_KO));
}
if (is_numeric($this->getValue('post_id'))) {
$query->addWhere('swBlogComment.post_id = ?', $this->getValue('post_id'));
}
return $query;
}
/**
*
* @param Doctrine_Query $query
* @param FilterWidgetForm $filter
* @return Doctrine_Query
*/
public function filter(Doctrine_Query $query, $filter)
{
if (!$filter) {
return $query;
}
// print_r($filter->getValues()); die('y');
/* @var $filter policatFilter */
if ($filter->getValue(self::FILTER_CAMPAIGN)) {
$query->andWhere('w.campaign_id = ?', $filter->getValue(self::FILTER_CAMPAIGN));
}
if ($filter->getValue(self::FILTER_STATUS)) {
$query->andWhere('w.status = ?', $filter->getValue(self::FILTER_STATUS));
}
if ($filter->getValue(self::FILTER_LANGUAGE)) {
$query->leftJoin('w.PetitionText pt_for_lang')->andWhere('pt_for_lang.language_id = ?', $filter->getValue(self::FILTER_LANGUAGE));
}
$petition_joined = false;
if ($filter->getValue(self::FILTER_START)) {
if (!$petition_joined) {
$query->leftJoin('w.Petition p_filter');
$petition_joined = true;
}
$query->andWhere('p_filter.start_at > ?', $filter->getValue(self::FILTER_START));
}
if ($filter->getValue(self::FILTER_END)) {
if (!$petition_joined) {
$query->leftJoin('w.Petition p_filter');
$petition_joined = true;
}
$query->andWhere('p_filter.end_at < ?', $filter->getValue(self::FILTER_END));
}
if ($filter->getValue(self::FILTER_PETITION)) {
$query->andWhere('w.petition_id = ?', $filter->getValue(self::FILTER_PETITION));
}
if ($filter->getValue(self::FILTER_ORDER)) {
switch ($filter->getValue(self::FILTER_ORDER)) {
case self::ORDER_CAMPAIGN_ASC:
$query->leftJoin('w.Campaign c_order')->orderBy('c_order.name ASC')->addOrderBy('w.campaign_id ASC')->addOrderBy('w.id ASC');
break;
case self::ORDER_CAMPAIGN_DESC:
$query->leftJoin('w.Campaign c_order')->orderBy('c_order.name DESC')->addOrderBy('w.campaign_id DESC')->addOrderBy('w.id DESC');
break;
case self::ORDER_ACTION_ASC:
if (!$petition_joined) {
$query->leftJoin('w.Petition p_filter');
$petition_joined = true;
}
$query->orderBy('p_filter.name ASC')->addOrderBy('w.petition_id ASC')->addOrderBy('w.id ASC');
break;
case self::ORDER_ACTION_DESC:
if (!$petition_joined) {
$query->leftJoin('w.Petition p_filter');
$petition_joined = true;
}
$query->orderBy('p_filter.name DESC')->addOrderBy('w.petition_id DESC')->addOrderBy('w.id DESC');
break;
case self::ORDER_STATUS_ASC:
$query->orderBy('w.status ASC')->addOrderBy('w.id ASC');
break;
case self::ORDER_STATUS_DESC:
$query->orderBy('w.status DESC')->addOrderBy('w.id DESC');
break;
case self::ORDER_WIDGET_ASC:
$query->orderBy('w.id ASC');
break;
case self::ORDER_WIDGET_DESC:
$query->orderBy('w.id DESC');
break;
case self::ORDER_LANGUAGE_ASC:
$query->leftJoin('w.PetitionText pt_ord_lang')->leftJoin('pt_ord_lang.Language ord_lang')->orderBy('ord_lang.order_number ASC')->addOrderBy('w.id ASC');
break;
case self::ORDER_LANGUAGE_DESC:
$query->leftJoin('w.PetitionText pt_ord_lang')->leftJoin('pt_ord_lang.Language ord_lang')->orderBy('ord_lang.order_number DESC')->addOrderBy('w.id DESC');
break;
case self::ORDER_ACTIVITY_ASC:
$query->orderBy('w.activity_at ASC');
break;
case self::ORDER_ACTIVITY_DESC:
$query->orderBy('w.activity_at DESC');
break;
case self::ORDER_TRENDING:
$query->select('w.*');
$query->addSelect('(SELECT count(z.id) FROM PetitionSigning z WHERE DATE_SUB(NOW(),INTERVAL 1 DAY) <= z.created_at and z.widget_id = w.id and z.status = ' . PetitionSigning::STATUS_VERIFIED . ') as signings24');
$query->orderBy('signings24 DESC, w.activity_at DESC, w.id DESC');
break;
}
}
if ($filter->getValue(self::FILTER_MIN_SIGNINGS)) {
$query->andWhere('(SELECT count(ps.id) FROM PetitionSigning ps WHERE ps.widget_id = w.id AND ps.status = ? LIMIT ' . $filter->getValue(self::FILTER_MIN_SIGNINGS) . ') >= ?', array(PetitionSigning::STATUS_VERIFIED, $filter->getValue(self::FILTER_MIN_SIGNINGS)));
}
return $query;
}
/**
* Create the query for selecting objects, eventually along with related
* objects
*
* @param array $params an array of criterions for the selection
*/
public function addToQuery(Doctrine_Query $query, $params)
{
if (isset($sort)) {
$query->orderBy($sort);
}
if (isset($params['id'])) {
$values = explode(',', $params['id']);
if (count($values) == 1) {
$query->andWhere($this->model . '.id = ?', $values[0]);
} else {
$query->whereIn($this->model . '.id', $values);
}
unset($params['id']);
}
foreach ($params as $name => $value) {
$query->andWhere($this->model . '.' . $name . ' = ?', $value);
}
return $query;
}
/**
*
* @param Doctrine_Query $query
* @param FilterPetitionForm $filter
* @return Doctrine_Query
*/
public function filter(Doctrine_Query $query, $filter)
{
if (!$filter) {
return $query;
}
/* @var $filter policatFilter */
if ($filter->getValue(self::FILTER_CAMPAIGN)) {
$query->andWhere('p.campaign_id = ?', $filter->getValue(self::FILTER_CAMPAIGN));
}
if ($filter->getValue(self::FILTER_KIND)) {
$query->andWhere('p.kind = ?', $filter->getValue(self::FILTER_KIND));
}
if ($filter->getValue(self::FILTER_STATUS)) {
$query->andWhere('p.status = ?', $filter->getValue(self::FILTER_STATUS));
} else {
$query->andWhere('p.status != ?', Petition::STATUS_DELETED);
}
if ($filter->getValue(self::FILTER_START)) {
$query->andWhere('p.start_at > ?', $filter->getValue(self::FILTER_START));
}
if ($filter->getValue(self::FILTER_END)) {
$query->andWhere('p.end_at < ?', $filter->getValue(self::FILTER_END));
}
if ($filter->getValue(self::FILTER_ORDER)) {
switch ($filter->getValue(self::FILTER_ORDER)) {
case self::ORDER_CAMPAIGN_ASC:
$query->leftJoin('p.Campaign c_order')->orderBy('c_order.name ASC')->addOrderBy('p.campaign_id ASC')->addOrderBy('p.id ASC');
break;
case self::ORDER_CAMPAIGN_DESC:
$query->leftJoin('p.Campaign c_order')->orderBy('c_order.name DESC')->addOrderBy('p.campaign_id DESC')->addOrderBy('p.id DESC');
break;
case self::ORDER_ACTION_ASC:
$query->orderBy('p.name ASC');
break;
case self::ORDER_ACTION_DESC:
$query->orderBy('p.name DESC');
break;
case self::ORDER_STATUS_ASC:
$query->orderBy('p.status ASC')->addOrderBy('p.id ASC');
break;
case self::ORDER_STATUS_DESC:
$query->orderBy('p.status DESC')->addOrderBy('p.id DESC');
break;
case self::ORDER_ID_ASC:
$query->orderBy('p.id ASC');
break;
case self::ORDER_ID_DESC:
$query->orderBy('p.id DESC');
break;
case self::ORDER_ACTIVITY_ASC:
$query->orderBy('p.activity_at ASC');
break;
case self::ORDER_ACTIVITY_DESC:
$query->orderBy('p.activity_at DESC');
break;
case self::ORDER_TRENDING:
$query->select('p.*');
$query->addSelect('(SELECT count(z.id) FROM PetitionSigning z WHERE DATE_SUB(NOW(),INTERVAL 1 DAY) <= z.created_at and z.petition_id = p.id and z.status = ' . PetitionSigning::STATUS_VERIFIED . ') as signings24');
$query->orderBy('signings24 DESC, p.activity_at DESC, p.id DESC');
break;
}
}
if ($filter->getValue(self::FILTER_MIN_SIGNINGS)) {
$query->andWhere('(SELECT count(ps.id) FROM PetitionSigning ps WHERE ps.petition_id = p.id AND ps.status = ? LIMIT ' . $filter->getValue(self::FILTER_MIN_SIGNINGS) . ') >= ?', array(PetitionSigning::STATUS_VERIFIED, $filter->getValue(self::FILTER_MIN_SIGNINGS)));
}
return $query;
}
/**
* Typesafe call to modify
* @access private
**/
protected function modifyImpl(Doctrine_Query &$o)
{
if ($this->sortfield) {
$o->orderBy($this->sortfield . " " . $this->dir);
}
}
public function addDefaultSort(Doctrine_Query $q)
{
return $q->orderBy($q->getRootAlias() . '.lft ASC');
}
public function testHydrationSkippingRelationIfNotSetOnSiblingDepth5()
{
$q = new Doctrine_Query();
$q->from('RelA a');
$q->leftJoin('a.b ab');
$q->leftJoin('ab.c abc');
$q->leftJoin('abc.d abcd');
$q->leftJoin('abcd.e abcde');
$q->orderBy('a.id ASC');
$res = $q->execute();
//$res = $q->execute(array(), Doctrine_Core::HYDRATE_ARRAY);
//var_dump($res/*->toArray(true)*/);
$this->assertEqual('a 1', $res->getFirst()->get('name'));
$this->assertTrue($res->getFirst()->get('b')->exists());
$this->assertTrue($res->getFirst()->get('b')->get('c')->exists());
$this->assertTrue($res->getFirst()->get('b')->get('c')->get('d')->exists());
$this->assertTrue($res->getFirst()->get('b')->get('c')->get('d')->get('e')->exists());
}
protected function addSortByName(Doctrine_Query $q)
{
$root = $q->getRootAlias();
return $q->orderBy($root . '.name ASC, ' . $root . '.created_at DESC');
}
protected function tryToSortWithForeignColumn(Doctrine_Query $query, array $sort)
{
$table = $this->getDmModule()->getTable();
if ('integer' === dmArray::get($table->getColumnDefinition($sort[0]), 'type')) {
if ($table->isI18nColumn($sort[0])) {
$query->orderBy(sprintf('%s.%s %s', $query->getJoinAliasForRelationAlias($table->getComponentName(), 'Translation'), $sort[0], $sort[1]));
// Success, skip default sorting by local column
return;
} elseif ($relation = $table->getRelationHolder()->getLocalByColumnName($sort[0])) {
if ($relation instanceof Doctrine_Relation_LocalKey && ($foreignTable = $relation->getTable()) instanceof dmDoctrineTable) {
if (($foreignColumn = $foreignTable->getIdentifierColumnName()) != 'id') {
if (!($joinAlias = $query->getJoinAliasForRelationAlias($table->getComponentName(), $relation->getAlias()))) {
if ($foreignTable->isI18nColumn($foreignColumn)) {
try {
$query->withI18n();
//leftJoin(sprintf('%s.%s %s', $joinAlias, 'Translation', $joinAlias.'Translation'));
} catch (Exception $e) {
}
$joinAlias = $query->getRootAlias();
} else {
$query->leftJoin(sprintf('%s.%s %s', $query->getRootAlias(), $relation->getAlias(), $relation->getAlias()));
$joinAlias = $relation->getAlias();
}
}
if ($foreignTable->isI18nColumn($foreignColumn)) {
$query->orderBy(sprintf('%s.%s %s', $joinAlias . 'Translation', $foreignColumn, $sort[1]));
} else {
$query->orderBy(sprintf('%s.%s %s', $joinAlias, $foreignColumn, $sort[1]));
}
// Success, skip default sorting by local column
return;
}
}
}
} elseif ($table->isI18nColumn($sort[0])) {
$query->orderBy(sprintf('%s.%s %s', $query->getJoinAliasForRelationAlias($table->getComponentName(), 'Translation'), $sort[0], $sort[1]));
// Success, skip default sorting by local column
return;
}
if ($table->hasField($sort[0])) {
$query->orderBy($sort[0] . ' ' . $sort[1]);
}
}
