public function addJoinQuery(Doctrine_Query $q)
{
$rootAlias = $q->getRootAlias();
$q->leftJoin($rootAlias . '.Category c')->leftJoin($rootAlias . '.Tags t')->leftJoin($rootAlias . '.CreatedBy u');
//->leftJoin($rootAlias . '.Comments m');
$q->addSelect($rootAlias . '.*, c.*, t.*, u.*');
$q->addSelect('(SELECT COUNT(*) FROM zsBlogComment m WHERE m.article_id = ' . $rootAlias . '.id) as num_comments');
//$q->addSelect('COUNT(m.id) num_comments');
//$q->groupBy($rootAlias.'.id');
return $q;
}
/**
* Used to set SELECT values to the DQL when
* no SELECT is provided. We will just add
* ALL columns for all tables given.
*
* NOTE: Since no SELECT was provided, to access these
* from within the Bvb_Grid_Data class, you will need to
* use the table alias + "_" + column name
*
* <code>
* $grid->setGridColumns(array('co_code', 'co_name', 'co_continent', 'ci_name'));
* </code>
*
* @return void
*/
protected function _findAndSetSelect()
{
$return = array();
//Make sure we have the FROM set
$this->_setFromParts();
$fromTableModel = $this->_queryParts['from']['tableModel'];
$fromClass = Doctrine::getTable($fromTableModel);
$fromColumns = array_keys($fromClass->getColumns());
$fromAlias = $this->_queryParts['from']['alias'];
foreach ($fromColumns as $fromColumn) {
/**
* Do this check here because a DQL with no JOIN,
* does not need a table alias
*
* @var string
*/
$addColumn = !empty($fromAlias) ? $fromAlias . '.' . $fromColumn : $fromColumn;
$this->_query->addSelect($addColumn);
}
$joins = $this->_queryParts['join'];
if (!empty($joins)) {
foreach ($joins as $joinType) {
foreach ($joinType as $join) {
$joinClass = Doctrine::getTable($join['tableModel']);
$joinColumns = array_keys($joinClass->getColumns());
foreach ($joinColumns as $joinColumn) {
$this->_query->addSelect($join['alias'] . '.' . $joinColumn);
}
}
}
}
}
/**
* getSetting
* pulls the sfSetting object for a given setting
*
* @param string $setting
* @static
* @access public
* @return object sfSetting
*/
static function getSetting($setting)
{
if (!is_string($setting) || empty($setting)) {
return 0;
}
// If all the settings have been requested, there's no need to check for
// individuals. This avoids additional queries later on.
if (sfContext::getInstance()->getRequest()->hasAttribute('AllsfSettings')) {
$settings = sfContext::getInstance()->getRequest()->getAttribute('AllsfSettings');
} else {
$settings = sfContext::getInstance()->getRequest()->getAttribute('sfSettings');
}
if (isset($settings[$setting])) {
$obj = $settings[$setting];
return $obj;
} else {
// Setting was not pre-loaded via ->load() but we'll be nice and retrieve
// the setting anyhow:
$query = new Doctrine_Query();
$query->addSelect("s.*");
$query->addFrom("sfSetting s");
$query->addWhere("s.name = :name", array(":name" => $setting));
if ($obj = $query->limit(1)->execute()->getFirst()) {
// Store this setting in memory for later retrieval to avoid a second
// query for the same setting:
$settings[$obj->getName()] = $obj;
sfContext::getInstance()->getRequest()->setAttribute('sfSettings', $settings);
// return it:
return $obj;
} else {
return 0;
}
}
}
public function testTicket()
{
$q = new Doctrine_Query();
// simple query with deep relations
$q->addSelect('Resume.id, Level.id, Level.label')->from('ticket384_Resume Resume')->leftJoin('Resume.KnownLanguages KnownLanguages')->leftJoin('KnownLanguages.Level Level')->leftJoin('KnownLanguages.Language Language');
try {
// get the wrong resultset
$aResult = $q->fetchArray();
$this->fail();
} catch (Doctrine_Query_Exception $e) {
$this->pass();
}
$q->free();
// now correct
// we have to select at least KnownLanguages.id in order to get the Levels,
// which are only reachable through the KnownLanguages, hydrated properly.
$q = new Doctrine_Query();
$q->addSelect('Resume.id, Level.id, Level.label, KnownLanguages.id')->from('ticket384_Resume Resume')->leftJoin('Resume.KnownLanguages KnownLanguages')->leftJoin('KnownLanguages.Level Level')->leftJoin('KnownLanguages.Language Language');
$aResult = $q->fetchArray();
// should be setted
$bSuccess = isset($aResult[0]['KnownLanguages'][0]['Level']);
$this->assertTrue($bSuccess);
if (!$bSuccess) {
$this->fail('fetchArray doesnt hydrate nested child relations, if parent doesnt have a column selected');
}
}
/**
* Test the existence expected indexes
*/
public function testTicket()
{
$q = new Doctrine_Query();
// simple query with deep relations
$q->addSelect('Resume.id, Person.id, Person.name, KnownLanguages.id, Level.label, Language.label')->from('ticket912_Resume Resume')->leftJoin('Resume.Person Person')->leftJoin('Resume.KnownLanguages KnownLanguages')->leftJoin('KnownLanguages.Level Level')->leftJoin('KnownLanguages.Language Language');
$aResult = $q->fetchArray();
// should be setted..
$issetLevel = isset($aResult[0]['KnownLanguages'][0]['Level']);
$issetLanguage = isset($aResult[0]['KnownLanguages'][0]['Language']);
$this->assertTrue($issetLevel);
$this->assertTrue($issetLanguage);
}
public function testTicket()
{
$q1 = new Doctrine_Query();
$q1->select('p.*');
$q1->from('Testing_Product p');
$q1->where('p.id = ?', $this->p1['id']);
$q1->addSelect('a.*, ad.*');
$q1->leftJoin('p.Attributes a');
$q1->leftJoin('a.Definition ad');
$q2 = new Doctrine_Query();
$q2->select('p.*');
$q2->from('Testing_Product p');
$q2->where('p.id = ?', $this->p2['id']);
$q2->addSelect('a.*, ad.*');
$q2->leftJoin('p.Attributes a');
$q2->leftJoin('a.Definition ad');
// This query works perfect
$r1 = $q1->execute(array(), Doctrine_Core::HYDRATE_ARRAY);
//var_dump($r1);
// This query throws an exception!!!
$r2 = $q2->execute(array(), Doctrine_Core::HYDRATE_ARRAY);
//$r2 = $q2->execute();
//var_dump($r2);
}
public function testLiteralEnumValueConversionSupportsJoins()
{
$q = new Doctrine_Query($this->connection);
$q->addSelect('e.*')->addSelect('e3.*')->from('EnumTest e')->leftJoin('e.Enum3 e3')->where("e.status = 'verified'")->execute();
$this->assertEqual($q->getSqlQuery(), "SELECT e.id AS e__id, e.status AS e__status, e.text AS e__text, e2.text AS e2__text FROM enum_test e LEFT JOIN enum_test3 e2 ON e.text = e2.text WHERE (e.status = 'verified')");
}
/**
* Create a "status" column based on job state
*
* @param Doctrine_Query $q
*/
protected function add_status($q)
{
$sel = 'select case';
foreach ($this->status_queries as $code => $query) {
$sel .= " when {$query} then '{$code}'";
}
$sel .= " else 'U' end";
$q->addSelect("({$sel}) as jq_status");
}
/**
* The first time you call addSelect (assuming that you didn't do an
* explicit $q->select() query), all the joined relations will be
* auto-selected. After this point, however, your select statement will
* be explicit, so if you continue to join relations, they will not be
* automatically included in the select.
*
* @param string $select
* @return Doctrine_Query
*/
public function addSelect($select)
{
if (!$this->is_explicit_select) {
// get the "from" parts, and add them to the select
$this->getSqlQuery();
// trigger the dql parser
$aliases = array_keys($this->_queryComponents);
foreach ($aliases as $idx => $a) {
$aliases[$idx] .= '.*';
}
$this->select(implode(', ', $aliases));
}
// now add the select statement
return parent::addSelect($select);
}
/**
* @access private
*
* @author Jannis Moßhammer <*****@*****.**>
**/
protected function modifyImpl(Doctrine_Query &$o)
{
$o->setAliasDefs($this->mainAlias, $this->aliasDefs);
$o->setDefaultJoinType($this->defaultJoinType);
foreach ($this->fields as $field) {
$o->addSelect($field);
}
$o->distinct($this->isDistinct());
$o->from($this->target . " " . $this->mainAlias);
foreach ($this->staticWhereConditions as $cond) {
if (isset($cond[1]) && $cond[1] != null) {
$o->addWhere($cond[0], $cond[1]);
} else {
$o->addWhere($cond[0]);
}
}
}
protected function modifyImpl(Doctrine_Query &$o)
{
if (!$this->ignoreIds) {
$table = $o->getConnection()->getTable($this->getTarget());
$keys = $table->getIdentifierColumnNames();
$o->addSelect(implode($keys));
} else {
$o->disableAutoIdentifierFields(true);
}
foreach ($this->additionalSelects as $alias => $select) {
$o->addSelect($select . (is_numeric($alias) ? "" : " AS " . $alias));
}
$db = $this->getContext()->getDatabaseManager()->getDatabase('icinga');
// check if retained state must be respected
if (method_exists($db, "useRetained")) {
/*
* the core with idomod dumps 2 different config types
* idomod.cfg:config_output_options
* 1 = original config => config_type = 0
* 2 = retained config => config_type = 1
* 3 = both, both config_types are available
*/
if ($this->retainedAlias) {
$o->andWhere($this->retainedAlias . ".config_type= ?", $db->useRetained() ? "1" : "0");
}
}
if ($this->getTarget() == "IcingaObjects") {
$o->andWhere($this->mainAlias . ".is_active = 1");
}
foreach ($this->forceGroup as $group) {
$o->addGroupBy($group);
}
parent::modifyImpl($o);
}
/**
*
* @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;
}
/**
* sortable STATUS column (to sort by status MEANING, not char val)
*
* @param Doctrine_Query $q
*/
protected function add_status_sort($q)
{
$stat_sort = array(TankSource::$STATUS_ERROR, TankSource::$STATUS_CONFLICT, TankSource::$STATUS_LOCKED, TankSource::$STATUS_RESOLVED, TankSource::$STATUS_DONE, TankSource::$STATUS_NEW);
$sel = 'select case';
foreach ($stat_sort as $idx => $stat) {
$sel .= " when t.tsrc_status = '{$stat}' then {$idx}";
}
$sel .= ' else 99 end';
$q->addSelect("({$sel}) as status_sort");
}
/**
*
* @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;
}
/**
* Retrieves tags with the number of taggings for a given set of models, can be accessed using ->$model
*
* @param Array $models
* @param Doctrine_Query $q
* @return Doctrine_Query
*/
public function queryTagsWithCountsByModel($models, $q = null)
{
$q->leftJoin('r.Tagging tg')->addSelect('r.*');
foreach ($models as $model) {
$q->addSelect("SUM(IF(tg.taggable_model = '{$model}' , 1, 0)) AS " . $model . "Count");
}
$q->groupBy('r.id');
return $q;
}
/**
* Enter description here...
*
* @param Doctrine_Query $query
*/
public function setBaseQuery(Doctrine_Query $query)
{
$this->_baseAlias = $query->getRootAlias();
$query->addSelect($this->_baseAlias . ".lft, " . $this->_baseAlias . ".rgt, " . $this->_baseAlias . ".level");
if ($this->getAttribute('rootColumnName')) {
$query->addSelect($this->_baseAlias . "." . $this->getAttribute('rootColumnName'));
}
$this->_baseQuery = $query;
}
/**
* Add event metadata to a query, including 'evtype', 'evdir', 'evdtim',
* and 'evdesc'. These are derived from the activity data.
*
* @param Doctrine_Query $q
* @param char $alias
* @param array $fld_defs (optional, reference)
*/
public static function add_event_meta($q, $alias, &$fld_defs = null)
{
$q->addSelect("{$alias}.tact_notes as evdesc");
$q->addSelect("{$alias}.tact_dtim as evdtim");
// translate actm_id into evtype/evdir --- ack!
$casetyp = "(case {$alias}.tact_actm_id ";
$casedir = "(case {$alias}.tact_actm_id ";
foreach (self::$ACTM_MAP as $code => $actm_id) {
$casetyp .= "when {$actm_id} then \"{$code[0]}\" ";
$casedir .= "when {$actm_id} then \"{$code[1]}\" ";
}
$casetyp .= "end) as evtype";
$casedir .= "end) as evdir";
$q->addSelect($casetyp);
$q->addSelect($casedir);
// add to field defs
if (is_array($fld_defs)) {
$fld_defs[] = array('name' => 'evdesc', 'type' => 'string');
$fld_defs[] = array('name' => 'evdtim', 'type' => 'date');
$fld_defs[] = array('name' => 'evtype', 'type' => 'string');
$fld_defs[] = array('name' => 'evdir', 'type' => 'string');
}
}
