$query = new Doctrine_Query();
$query->select('e.entity_name,e.entity_id,s.id,s.parent_id,e.ffc_entity_id,c.country_id,c.country_name')
//$query->select('e.entity_name,e.entity_id,s.id,s.parent_id,e.ffc_entity_id,ea.Country')
->from('Entities e')
->leftJoin('e.EntityAddresses ea ON ea.entity_id = e.entity_id AND ea.address_type ="M"')
->leftJoin('ea.Country c ON ea.country = c.country_id')
->leftJoin('e.ActiveFactories s')
->where('e.status=1');
if(!empty($alpha))

$query->andWhere("s.company_id=".$parentId)
->andWhere("e.entity_type=2")
->andWhere('s.status=1')
->groupBy('e.entity_id');

doing a
doctrine:build --all --and-load
with a schema.yml of

Foo:
tableName: foo
options:
charset: utf8
columns:
bar:
type: tinyint(1)
default: 0

results in a blank value generated for the default keyword, and the following error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 ENGINE = INNODB' at line 1. Failing Query: "CREATE TABLE foo (id BIGINT AUTO_INCREMENT, bar tinyint(1) DEFAULT , PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 ENGINE = INNODB". Failing Query: CREATE TABLE foo (id BIGINT AUTO_INCREMENT, bar tinyint(1) DEFAULT , PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 ENGINE = INNODB

I've also tried combinations of tinyint, tinyint(4), single-quoting the default value, and different default values.

Changing the type to int makes the issue disappear

$query = new Doctrine_Query();
$query->select('e.entity_name,e.entity_id,s.id,s.parent_id,e.ffc_entity_id,c.country_id,c.country_name')
//$query->select('e.entity_name,e.entity_id,s.id,s.parent_id,e.ffc_entity_id,ea.Country')
->from('Entities e')
->leftJoin('e.EntityAddresses ea ON ea.entity_id = e.entity_id AND ea.address_type ="M"')
->leftJoin('ea.Country c ON ea.country = c.country_id')
->leftJoin('e.ActiveFactories s')
->where('e.status=1');
if(!empty($alpha))
{
$query->andWhere("e.entity_name like '".$alpha."%'");
}
$query->andWhere("s.company_id=".$parentId)
->andWhere("e.entity_type=2")
->andWhere('s.status=1')
->groupBy('e.entity_id');

this is the my table creation .

CREATE TABLE billboard(id BIGINT AUTO_INCREMENT,title VARCHAR (255),country_id BIGINT,zone_id BIGINT,place VARCHAR(255),occassion VARCHAR(255),itinerary VARCHAR(255),created_at DATETIME NOT NULL,description TEXT NOT NULL,PRIMARY KEY(id)) ENGINE=INNODB;

public static function getInstance()

this is the my Doctrine Table

// public function getBillboardsForAUser($userId, $limit, $offset=0)
public function getBillboardsForAUser($userId,$limit,$offset=0)
{
$query = $this->createQuery('b')
->where('b.title=?',$title);
// ->where('b.owner_id = ?', $userId);
$followings = Doctrine::getTable('Follow')->getAllFollowing($userId);
foreach($followings as $following)

$query->orderBy('b.created_at DESC')
->limit($limit)
->offset($offset);
return $query->execute();

}

plz help me what is the problem.

Apparently, when setting Doctrine::MODEL_LOADING_CONSERVATIVE things like Doctrine_Core::createTablesFromModels() don't work. Have to enable aggressive model loading. This has been confirmed on the doctrine-user mailing list.

I took a brief look at the code: I may be missing something but it appears there may be a logic difference in the conservative vs. aggressive model loading method. Aggressive loading uses require_once on all files in the model directory and tries to determine the name of the class inside the file (by doing a difference on the loaded classes). This logic makes sense since the class and file name do not have to match.

However, conservative loading just tries to calculate the class name and store it in an array for later loading by the autoloader. This means there is a huge logic difference in behaviour between these two loading methods since the model class files actually must differ depending on case (specifically, the model class file name must follow a specific format to match the actual class name) otherwise the autoload array will never know the correct name of the class.

I would also like to comment that overall, require_once is a poor choice for performance reasons, it's vastly slower than include_once, but overall the logic should better be adjusted to use plain include (if possible), for performance reasons.

I have found a problem during testing example model on page http://www.doctrine-project.org/documentation/cookbook/1_1/en/code-igniter-and-doctrine . A use YAML model with object name UserGroup and call method Doctrine_Core::createTablesFromModels('models'); process faling with error:

Fatal error: Class 'BaseUserGroup' not found in /sourcepath/models/UserGroup.php on line 13

Generate model file included abstract class BaseUserGroup exist and is OK. If I include BaseUserGroup manuly or rename object "UserGroup" on "UserGroups" everithing work fine. I thing here is a problem with autoloding base class with specifik name.

Here is my code example:

Doctrine_Core::dropDatabases();
Doctrine_Core::createDatabases();
Doctrine_Core::generateModelsFromYaml('sourcepath/yaml/model.yml', 'models');
Doctrine_Core::createTablesFromModels('models');

Trouble shotting YAML model is:
—
UserGroup:
columns:
user_id:
type: integer(4)
primary: true
group_id:
type: integer(4)
primary: true

I have a table in oracle that i was using but I had to change it so i remove some fields and add others when i run the task buils schema it generates the file schema.yml it created the new fields added but continued to bringing those field who had been eliminated and no longer existed in the database, it generates an error because the file schema.yml are those field but the database does not ...

Setting:
$conn->setAttribute(Doctrine_Core::ATTR_PORTABILITY, Doctrine_Core::PORTABILITY_FIX_CASE)
$conn->setAttribute(Doctrine_Core::ATTR_FIELD_CASE, CASE_UPPER)

I expect column names in built queries to be uppercase, but they remain to be lowercase.

In revision 7691 something has happened. Ever since I updated my Doctrine to that revision all my queries having " ... fieldname AS aliasname ... " go crazy and make the PHP to throw an exception, like this:

'Doctrine_Connection_Mysql_Exception' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 't.`id`' in 'field list'. Failing Query:
"SELECT `t`.`id` AS `t_id`, `t`.```id``` AS `t0`, `t`.```name``` AS `t1`, `t`.`id` AS `t0`, `t`.`name` AS `t_1` FROM `territoryCombined` `t` ORDER BY `t`.`name` asc"'
in ...path here.../doctrine/lib/Doctrine/Connection.php:1082

The problem is that the DQL parser somewhere along the process encapsualtes aliases in ticks, but then it does it again in lib/Doctrine/Formatter.php : quoteIdentifier() , which is called in lib/Doctrine/Connection : quoteIdentifier() , which is called in lib/Doctrine/Query.php : processPendingFields() @ between lines 485 and 512. The problem is that by the time the alias name gets to line 507 it is already encapsualted in ticks, but it does it again. At the end we end up with ```alias``` , which is not good.

It only happens to aliases. If I say select('*') or select("t.id, t.name") then it executes properly. Only the aliases couse problems.

A test query:

$vTerritories = Doctrine_Query::create()
->select("t.id as territory_id, t.name as territory_name")
->from('TerritoryCombined t')
->orderBy('t.name asc')
->fetchArray();

MY PROPOSED PATCH:

If I change the Formatter::quoteIdentifier() to this:

public function quoteIdentifier($str, $checkOption = true)

then it works correctly. Please note I only tested that in MySQL, as we use MySQL in all our projects.

After upgrading to Doctrine 1.2.2 queries with named arguments doesn't work anymore.
Whenever querying like
$query = Doctrine_Query::create();
$customer = $query->from('Customers')
->where("id = :id"
, array('id'=>$user['customer_id']))
->fetchOne(array(), Doctrine::HYDRATE_ARRAY);

I get the error
SQLSTATE[HY000]: General error: 10007 Incorrect syntax near ':'. [10007] (severity 5)

If changing the query to
$query = Doctrine_Query::create();
$customer = $query->from('Customers')
->where("id = ?"
, array($user['customer_id']))
->fetchOne(array(), Doctrine::HYDRATE_ARRAY);
it works fine.
Testing with MySQL works fine, so it seems contained to MSSQL

Looking at the attachment to see the little schema.
Tree table : items, items_children and children.

The link-table items_children is here to create a many-to-many relationships between Items and Children.

So, after configure the YAML and generated Models.
We can do something like that :

$query = Doctrine_Query::create ()
->select ("items.title, children.title")
->from ("Item items")
->leftJoin ("item.children children");
$items = $query->execute ();

$items //An array of items
$items[0]->title //A string
$items[0]->children //An array of children

The SQL Output of the query is :

SELECT `i`.`id_item` AS `i_id_item`, `i`.`title` AS `ititle`, `c`.`id_child` AS `cid_child`, `c`.`title` AS `c_title` FROM `items` `i` LEFT JOIN `items_children` `i2` ON (`i`.`id_item` = `i2`.`id_item`) LEFT JOIN `children` `c` ON `i2`.`id_child` = `c`.`id_child`

All array are order by something, in this case it's a natural order which come from the items_children order into database.

Now, I want to apply a condition, or a filter on the items_children table.
If I translate my demand to SQL Query :

SELECT `i`.`id_item` AS `i_id_item`, `i`.`title` AS `ititle`, `c`.`id_child` AS `cid_child`, `c`.`title` AS `c_title` FROM `items` `i` LEFT JOIN `items_children` `i2` ON (`i`.`id_item` = `i2`.`id_item`) LEFT JOIN `children` `c` ON `i2`.`id_child` = `c`.`id_child` ORDER BY `c1`.`index`

Or something crazy :

SELECT `i`.`id_item` AS `i_id_item`, `i`.`title` AS `ititle`, `c`.`id_child` AS `cid_child`, `c`.`title` AS `c_title` FROM `items` `i` LEFT JOIN `items_children` `i2` ON (`i`.`id_item` = `i2`.`id_item`) LEFT JOIN `children` `c` ON `i2`.`id_child` = `c`.`id_child` WHERE `c1`.`index` > 2

But how can I do that with DQL ? Because I have no access to the Link-table.

Link-Table are use to create many-to-many, that could be OOP array. So we have to be allowed to add "index" to order the result. Also, we have to be allowed to add condition on this tables, like "datePromote" or simply "enabled".

The solution should be on the following DQL Query :

$query = Doctrine_Query::create ()
->select ("items.title, children.title")
->from ("Item items")
->leftJoin ("item.children children")
->leftJoin ("ItemsChildren link_table")
->orderBy ("link_table.index")
$items = $query->execute ();

But the SQL Output is :

SELECT `i`.`id_item` AS `i_id_item`, `i`.`title` AS `ititle`, `c`.`id_child` AS `cid_child`, `c`.`title` AS `c_title` FROM `items` `i` LEFT JOIN `items_children` `i2` ON (`i`.`id_item` = `i2`.`id_item`) LEFT JOIN `children` `c` ON `i2`.`id_child` = `c`.`id_child`, `items_children` `i3` ORDER BY `i3`.`index`

To conclude, it's a blocker problem. Because I can't use a standard DB schema.

Regards,
Armetiz.

When I want to do a simple update like this :

$batches = ExamResultsBatchTable::getInstance()->retrieveByExamBatchStatus(ExamResultsBatch::valid_status_code);
foreach($batches as $batch)
{
$batch->setExamBatchStatusId($batchStatusId);
$batch->setStatusDate(date('Y-m-d'));
$batch->save();
}
Only exam_batch_status_id and status_date should be updated (see screenshot before), but columns exam_batch_status_id ,status_date AND exam_subject_id are updated,with the same value (23) (screenshot after_with_save).

If I run this:
$toto = Doctrine_Query::create()
->update('ExamResultsBatch erb')
->set('erb.exam_batch_status_id', 23)
->set('erb.status_date', date('Y-m-d'))
>where('erb.id = ?' , $batch>getId())
->execute();
Everything is correctly done.

here is the simpliest case.

The same problems are signaled on other tables in the database, but different tables can be impacted by one save() (the execute() query still works fine).

Example : 2 foreign tables will be updated , even if the save() action should only concern the main table, and one field (which is not a foreign key).
The corresponding foreign key fields in the 2 foreign tables, will be updated with the value given (here 23).

Because save() is used in a lot of different places in our app, I need to find a solution to fix save(), or if not possible to override it to run a execute()like query.

Thanks for your help.
Don't hesitate to ask if you want more details.

Yan

If the alias in a from-query is equal to the model name, I get an exception when executing the query after adding a "pendingJoinCondition".

See attached testCase.

DB schema:

Category:
actAs:
NestedSet:
hasManyRoots: true
rootColumnName: root_id
columns:
id:
type: integer(4)
primary: true
autoincrement: true
name:
type: string(64)
notnull: true
image: string(64)
indexes:
tree:
fields: [lft, rgt, root_id]

Sample data:

id: '1'
name: 'Przykładowa kategoria 1'
image: null
root_id: '1'
lft: '1'
rgt: '6'
level: '0'

• id: '2'
  name: 'Przykładowa kategoria 2'
  image: null
  root_id: '2'
  lft: '1'
  rgt: '6'
  level: '0'
  -
  id: '3'
  name: 'Przykładowa podkategoria 1'
  image: null
  root_id: '2'
  lft: '2'
  rgt: '5'
  level: '1'
  -
  id: '4'
  name: 'Przykładowa podkategoria 2'
  image: null
  root_id: '2'
  lft: '3'
  rgt: '4'
  level: '2'
  -
  id: '5'
  name: teset1
  image: null
  root_id: '1'
  lft: '2'
  rgt: '5'
  level: '1'
  -
  id: '6'
  name: test2
  image: null
  root_id: '1'
  lft: '3'
  rgt: '4'
  level: '2'

When using HYDRATE_RECORD_HIERARCHY, the first top-level category is empty. Everything is assigned to the other one. Only single-root trees work properly.

It works except subqueries. Main table's order is added to subquery and vice versa. SQL query looks like this:

{{
SELECT t.id AS t_id FROM ticket_d_c9999_record t WHERE (t.id = (SELECT MAX(t2.id) AS t20 FROM ticket_d_c9999__record t2 ORDER BY t.id ASC, t2.id ASC)) ORDER BY t.id ASC, t2.id ASC
}}

Query fails because there is >>no such column "t.id"<<.

I include a testcase when it is all visible.

BTW. there is nothing about that feature (and relation orderBy) in docs. It is only in UPGRADE file.

There is a problem in the following code in Doctrine/Query.php (lines 1257-1279) inside the buildSqlQuery() method:

            $subquery = $this->getLimitSubquery();

            // what about composite keys?
            $idColumnName = $table->getColumnName($table->getIdentifier());

            switch (strtolower($this->_conn->getDriverName())) {
                case 'mysql':
[...]
                case 'pgsql':
                    $subqueryAlias = $this->_conn->quoteIdentifier('doctrine_subquery_alias');

                    // pgsql needs special nested LIMIT subquery
                    $subquery = 'SELECT ' . $subqueryAlias . '.' . $this->_conn->quoteIdentifier($idColumnName)
                            . ' FROM (' . $subquery . ') AS ' . $subqueryAlias;

                    break;
            }

The above code is executed when a query consist of DISTINCT and LIMIT clauses. The most common situation is using pager.
The problem is in the subquery variable. The $idColumnName variable often has value "id". In such a situation in case of $subquery consist of some JOINs and some tables have column named "id", we have:

SELECT doctrine_subquery_alias.id FROM ((SELECT DISTINCT d1.id, d2.id FROM ...)) AS doctrine_subquery_alias

It, of course, causes the "ambiguous column name" pgsql exception.

under certain circumstances, Doctrine will only return one result out of a bunch of results, for example:

$ symfony doctrine:dql "from Tafel t, t.Reservering r where t.restaurant_id=4 select date(t.tijd), count(t.id) tafels, count(r.id) reserveringen group by date(t.tijd)"
>> doctrine executing dql query
DQL: from Tafel t, t.Reservering r where t.restaurant_id=4 select date(t.tijd), count(t.id) tafels, count(r.id) reserveringen group by date(t.tijd)
found 2 results
-
date: '2010-11-14'
tafels: '1'
reserveringen: '1'

Expected outcome:
found 2 results
-
date: '2010-11-14'
tafels: '1'
reserveringen: '1'
-
date: '2010-11-16'
tafels: '1'
reserveringen: '0'

The query works fine without the left join:
$ symfony doctrine:dql "from Tafel t where t.restaurant_id=4 select date(t.tijd), count(t.id) tafels group by date(t.tijd)"
>> doctrine executing dql query
DQL: from Tafel t where t.restaurant_id=4 select date(t.tijd), count(t.id) tafels, group by date(t.tijd)
found 2 results
-
date: '2010-11-14'
tafels: '1'
-
date: '2010-11-16'
tafels: '1'

I recently discovered that I could greatly optimize some of the queries that were being run through our system by adding a STRAIGHT_JOIN keyword to the front of the select

I added a feature to doctrine which allows me to inject sql into the query in the right place to enable features such as "STRAIGHT_JOIN" but I can't post the patch because my patches are starting to run together – the syntax with in the generated patch would also contain parts of other patches I have posted to jira but have not yet been included in the doctrine svn.

I still wanted to make this post because it will give me a ticket number to base my test cases around.

Will Ferrer

Query fetchOne method now retrieves and hydrates all collection, which can be time consumable. I suggest to add limit 1 in fetchOne method.

I found some problem when I tried to define custom query for results counting. Defined custom COUNT query is totally ignored and executed default one. I will give you full description of problem bellow.

We have following source code:

 
$q_select = Doctrine_Query::create ()
->select ( 'DISTINCT p.product_name AS product_name' )
->from ( 'Product p' )
->where( 'p.product_name LIKE ?', '%motorola%');
				
$q_count = Doctrine_Query::create ()
->select ( 'COUNT (DISTINCT p.product_name) num_results' )
->from ( 'Product p' )
->where( 'p.product_name LIKE ?', '%motorola%');
												
$pager = new Doctrine_Pager( $q_select, 1, 25 );										
$pager->setCountQuery($q_count);

Let's check custom query before calling $pager->execute() method:

 
echo $pager->getCountQuery(); 

Output:

 
SELECT COUNT (DISTINCT p.product_name) num_results FROM Product p WHERE p.product_name LIKE ?

Looks like until now is everything is correct. Let's call $pager->execute() method:

 
$products = $pager->execute(); 

Let's check executed queries using Symfony SQL queries log panel:

SELECT COUNT(*) AS num_results FROM product p WHERE p.product_name LIKE '%motorola%'
7.27s, "doctrine" connection

SELECT DISTINCT p.product_name AS p__0 FROM product p WHERE (p.product_name LIKE '%motorola%') LIMIT 25
3.25s, "doctrine" connection

Executed COUNT query is not same we set using $pager->setCountQuery($q_count). Our defined custom COUNT query is totally ignored and executed default one:

INSTEAD OF THIS CUSTOM COUNT QUERY:

SELECT COUNT (DISTINCT p.product_name) num_results FROM Product p WHERE p.product_name LIKE '%motorola%'

EXECUTED DEFAULT COUNT QUERY:

SELECT COUNT(*) AS num_results FROM product p WHERE p.product_name LIKE '%motorola%'

When subqueries are used in the on part of a join clause the Doctrine_Query_JoinCondition class does not always create the proper sql. For instance when there are 2 subqueries used in a between doctrine tries to parse the statement as 1 subquery rather 2 subqueries with an "and".

I will post my patch that fixes this issue after I make some test cases for it. I also fixed an issue where "(SQL:" syntax was breaking the join as well.

Will

Our model configuration includes several hasMany statements, for example:

$this->hasMany('Subcategory as Subcategories', array(
'refClass' => 'SubcategoryTone',
'local' => 'tone_id',
'foreign' => 'subcategory_id',
'cascade' => array('delete'),
'orderBy' => 'order_id',
));

We noticed that the ORDER BY directive worked just fine with a normal query, but the order by was being ignored when we fed it into the Pager.

For example:
$aa = $t->execute(array(), Doctrine_Core::HYDRATE_ARRAY);
var_dump($aa[4]);

$pager = new Doctrine_Pager($t, $currentPage, $resultsPerPage);
$bb = $pager->execute(array(), Doctrine_Core::HYDRATE_ARRAY);

var_dump($bb[4]);

These two var_dumps would give different results because the ORDER BY is ignored by the limit subquery in the pager.

The difference between the two code samples below is that there is a comma after 'lo.postal'_code in the second example:

This code DOES NOT RANDOMIZE But also DOES NOT PRODUCE A PARSER ERROR
$q = Doctrine_Query::create()
->select("li.id,
ap.name,
act.title, act.title_short, act.family_friendly,
o.unix_ts_begin, o.unix_ts_end,
act.description,
act.cost_min, act.tags, act.cost_min, act.cost_notes, act.organization,
lo.thoroughfare, lo.address_extra, lo.locality, lo.administrative_area, lo.country_name_code, lo.postal_code
RANDOM() AS rand")
->from(blah, blah)
->where(primary key equality statements for joining)
->orderBy('rand');

This DOES
$q = Doctrine_Query::create()
->select("li.id,
ap.name,
act.title, act.title_short, act.family_friendly,
o.unix_ts_begin, o.unix_ts_end,
act.description,
act.cost_min, act.tags, act.cost_min, act.cost_notes, act.organization,
lo.thoroughfare, lo.address_extra, lo.locality, lo.administrative_area, lo.country_name_code, lo.postal_code,
RANDOM() AS rand")
->from(blah, blah)
->where(primary key equality statements for joining)
->orderBy('rand');

You could find the ticket's test case in the attachments.

While I believe it's not so extraordinary to have parameters in a SELECT clause, Doctrine_Query::select() does not allow to pass parameters, next to the SELECT clause. You can still pass any parameters to execute(), but I do believe it would be nice to be able to pass the parameter values right away to select() as you can with where() etc.

Example:

Doctrine_Query::create()>select('f.*, (f.id = ?) AS selected'))>from('Foo f')->execute(array($selected_id));

This principle would apply to any select-field that has a calculated value that comes from a parameter.

i try to get all preferences with an IdUser specified.

UserHasPreference:
  connection: hopscore
  actAs:
    I18n:
      fields: [value]
    Timestampable:
      created:
        name: created_at
        type: timestamp
        format: Y-m-d H:i:s
  columns:
    idUserHasPreference:
      name: idUserHasPreference as id
      type: integer(4)
      unsigned: true
      primary: true
      autoincrement: true
    UsersIdUser:
      name: UsersIdUser as idUser
      type: integer(4)
      unsigned: true
      notnull: true
    PreferencesIdPreference:
      name: PreferencesIdPreference as idPreference
      type: integer(4)
      unsigned: true
      notnull: true
    MatchLinksMatchLinkId:
      name: MatchLinksMatchLinkId as MatchLinkId
      type: integer(4)
      unsigned: true
      notnull: true
    value:
      type: string(100)
  relations:
    Users:
      class: User
      local: idUser
      foreign: id
      type: one
    Preferences:
      class: Preference
      local: idPreference
      foreign: id
      type: one
    MatchLinks:
      class: MatchLink
      local: MatchLinkId
      foreign: id
      type: one
  options:
    collate: utf8_unicode_ci
    charset: utf8

$preferences = Doctrine_Query::create()
		->select('uhp.idPreference as idPref')
		->addSelect('uhpt.value as value')
		->addSelect('uhp.MatchLinkId as idItem')
		->from('UserHasPreference uhp')
		->innerJoin('uhp.Translation uhpt')
		->where('uhp.idUser = ?', intval($idUser))
		->execute(array(), Doctrine::HYDRATE_ARRAY);
	    return $preferences;

With $preferences->getSqlQuery(); and getParams(); and report this SQL query in phpmydadmin.
With SQL i have 13 elements. But in my object $preference i have one element (Doctrine::HYDRATE_ARRAY)
With "Doctrine::HYDRATE_NONE", i have 13 elements as SQL query.

I have been trying to update numerous records with the one doctrine_query object (which could be my problem) and passing the query execute params to persist to the dbase. eg

$q = Doctrine_Query::create()
->update('TdPackageType pt')
->set('pt.name', '?')
->set('pt.group_type_id', '?')
->where('pt.id = ?')
;

Then iterating over an array of values, passing the required values to the execute method eg
foreach($foobars as $foobar) {
$q->execute(array($foobar[0], $foobar[1], $foobar[2]));
}

I thought this the best way by creating only the one query instance and then assigning the vars as required. Trouble being, the data did not persist? I had no errors returned from Doctrine - eg I had the correct number of matched params - but the update would not update. To move on I ended up instantiating a new query object each time I iterated over my array of data values. eg

foreach($foobars as $foobar) {

$q = Doctrine_Query::create()
->update('TdPackageType pt')
->set('pt.name', '?', $foo[0])
->set('pt.group_type_id', '?', $foo[1])
->where('pt.id = ?', $foo[2])
;

$q->execute();
}

The values did then persist correctly to the dbase? Am I missing something really fundamental here? eg I would have thought the first code struct was a much better design to re-use the one query object. Or is it as silly as me not adding a hydration method to the execute method?

Any all help appreciated.
Thanks
Kyle

I have fixed this in my own version of doctrine but unfortunately I am to far diverged from the trunk to offer a patch.

here is a test case:

public function testSubqueryInWhereWithJoinAndLimit()
    {
        $q = new Doctrine_Query();
        $q->select('u.id');
        $q->from('User u');
        $q->where('u.id NOT IN (SELECT a.id FROM User u2 LEFT JOIN u2.Album a LIMIT 1)');
        $this->assertEqual($q->getSqlQuery(), 'SELECT e.id AS e__id FROM entity e WHERE (e.id NOT IN (SELECT a.id AS a__id FROM entity e2 LEFT JOIN album a ON e2.id = a.user_id WHERE (e2.type = 0) LIMIT 1) AND (e.type = 0))');
    }

To fix the issue I changed this line in Doctrine_Query as follows:

if ( ( ! empty($this->_sqlParts['limit']) || ! empty($this->_sqlParts['offset'])) && $needsSubQuery && $limitSubquery) {

=

if ( ( ! empty($this->_sqlParts['limit']) || ! empty($this->_sqlParts['offset'])) && $needsSubQuery && $limitSubquery && !$this->isSubquery()) {

Hope that helps.

Sincerely

Will Ferrer

in bindParam method there is:
switch ($type) {
case Doctrine_Core::PARAM_STR:
$oci_type = SQLT_CHR;
break;
}
I think there should be other oci_types too. I had to add:
case Doctrine_Core::PARAM_INT:
$oci_type = SQLT_INT;
because I got ORA-06502: PL/SQL: numeric or value error: character string buffer too small. while executing
$stmt->bindParam(":result", $result, Doctrine_Core::PARAM_INT);

After adding SQLT_INT everything is ok

With Doctrine::ATTR_QUOTE_IDENTIFIER enabled, when you launch a query with a complex having clause, Doctrine_Query_Having class does not handle it correctly.

By example, when you track the having clause interpretation:

$query->addHaving( 'SUM( IF( s.id = ? , 1 , 0 ) ) = 0' , 7 );

At some point, Doctrine_Query_Having at line 70 return something like "`s10`.`id = ?`" instead of "`s10`.`id` = ?".

I just fix it using:

return $this->query->parseClause($func);

instead of:

return $this->_parseAliases($func);

Now, the parseAliases function is not used anymore...

See patch attached...

Loops

In brief:
Doing $result1 = Doctrine_Query::create()>... followed by $result2 = Doctrine_Query::create()>... can lead to a situation where the content of $result1 has become the value in $result2.

In detail:
The attached models.yml defines two simple tables with a One-to-Many relationship; we have people and names and each person can have multiple names. The DB can be propagated along the lines of:

INSERT INTO `tblname` VALUES (1,1,'alpha'),(2,2,'beta'),(3,3,'gamma'),(4,4,'delta'),(5,5,'epsilon'),(6,1,'aleph');
INSERT INTO `tblperson` VALUES (1),(2),(3),(4),(5);

Applying the query:

$results1 = Doctrine_Query::create()
->from('Person ppa')
->innerJoin('ppa.Name n')
->where('ppa.id = ?', 1)
->andWhere('n.text = ?', 'alpha')
->execute()
->getFirst()
->Name;

and then producing output though

print 'Results (1): '.count($results1)."\n";
foreach ($results1 as $result) print $result['text'] . "\n";
print "\n\n";

produces the expected:

Results (1): 1
alpha

Doing a similarly query to a new variable:

$results2 = Doctrine_Query::create()
->from('Person ppa')
->innerJoin('ppa.Name n')
->where('ppa.id = ?', 1)
->andWhere('n.text = ?', 'aleph')
->execute()
->getFirst()
->Name;

and printing with

print 'Results (2): '.count($results2)."\n";
foreach ($results2 as $result) print $result['text'] . "\n";
print "\n\n";

produces the expected:

Results (2): 1
aleph

but printing out the first result object again at this point gives:

Results (1): 1
aleph

which is unexpected - "aleph" rather than "alpha".

If, the second query was altered to

->where('ppa.id = ?', 2)
->andWhere('n.text = ?', 'beta')

then all three output results are as expected.

test.zip contains corresponding test files.

When creating an UPDATE query, the table names are not aliased like in a SELECT statement. This causes ambiguous column names when JOINING in an UPDATE.

E.g.
$q = $this->createQuery('st')
->update('SomeTable st')
->set('st.position','st.position + 1')
->leftJoin('st.SomeOtherTable sot ON st.some_id = sot.id')
->where('st.id <> ?', $someId)
->andWhere('sot.some_column = ?', $someValue)

The generated SQL for this is :
UPDATE some_table
LEFT JOIN some_other_table sot ON st.some_id = sot.id
SET position = position + 1, updated_at = 2011-04-13 11:01:03, updated_at = 2011-04-13 11:01:03
WHERE (id <> 4 AND some_column = 7)

Clearly here "updated_at" and "id" are ambiguous columns. Why the tables are not automatically aliased with unique aliases like in a SELECT statement, and the aliases written before the column name ?

Thanks.

Statements that return no result are not correctly closed in Doctrine_Hydrator_Graph::hydrateResultSet().

Oracle has limited number of opened cursors, and this bug prevents unsing doctrine in batch task like indexing models with sfSolrPlugin.

Oracle throws an error `ORA-01000: maximum open cursors exceeded : ` in my case after indexing only 100 records.

I'll send a pull request via github for this issue.

They are some strange problems with hydration to array combined with aliases of columns and JOINS. Let's see this example:

$q = Doctrine_Query::create ()
->select('c.id AS id, c.path AS path, c.name AS name, cbc.product_count')
->from('Category c')
->leftJoin('c.CategoryBrowserCache cbc');

$categories = $q->fetchArray();

This example will throw exception: "The root class of the query (alias lc) must have at least one field selected."

OK. Let's change code a little bit. Let's add alias for cbc.product_count column too:

$q = Doctrine_Query::create ()
->select('c.id AS id, c.path AS path, c.name AS name, cbc.product_count AS product_count')
->from('Category c')
->leftJoin('c.CategoryBrowserCache cbc');

$categories = $q->fetchArray();
print_r($categories);

Now code executed without exception, BUT $q->fetchArray() returned only ONE (first) record hydrated to array. Other results were ignored.

Let's change code one more time:

$q = Doctrine_Query::create ()
->select('c.id, c.path, c.name, cbc.product_count AS product_count')
->from('Category c')
->leftJoin('c.CategoryBrowserCache cbc');
$categories = $q->fetchArray();
print_r($categories);

Like you see I just removed all aliases for columns for Category. Now code will be executed without exceptions, All results will be hydrated into array as expected to be. Actually the same result can be reached by removing at least one alias for any Category column.

'Pending join conditions' are used by listeners to inject extra SQL conditions into a query. They are often used to add basic constraints on every query. An example is the bundled SoftDelete template. Its listener adds extra constraints such as s.deleted_at IS NULL to a query, to make sure that deleted rows are never retrieved on a query.

However, in the emitted SQL, Doctrine_Query does not use parentheses to group normal SQL conditions together. The pending join condition is simply added to the string without encapsulating existing expressions. This makes it possible to bypass the pending join conditions entirely by using the OR operator.

Example

For instance, the following query exhibits this problem:

$query = Doctrine_Query::create()
->from("SoftDeleteTest")
->where("name=?", "faulty")
->orWhere("name=?", "faulty");

This query emits the following SQL:

SELECT s.name AS s_name, s.deleted_at AS s_deleted_at FROM soft_delete_test s WHERE (s.name = 'faulty' OR s.name = 'faulty' AND (s.deleted_at IS NULL))

which returns also a deleted row.

Expected behavior

One would expect the pending join conditions always to hold, and to have precedence over regularly added SQL conditions. This could be accomplished in the most simple fashion by:

SELECT s.name AS s_name, s.deleted_at AS s_deleted_at FROM soft_delete_test s WHERE ( ( s.name = 'faulty' OR s.name = 'faulty' ) AND (s.deleted_at IS NULL));

As the existing expressions are now encapsulated by parentheses, it is no longer possible to bypass the pending join conditions injected by the query listener.

Full test case details:

init.sql

create database softdelete;
grant all privileges on softdelete.* to softdelete@localhost identified by 'uahwqeruwer';

use softdelete;
CREATE TABLE soft_delete_test (name VARCHAR(255), 
    deleted_at DATETIME DEFAULT NULL, 
    PRIMARY KEY(name)) ENGINE = INNODB;

insert into soft_delete_test values ('fine', null);
insert into soft_delete_test values ('faulty', now());

run.php

<?php

require "./1.2.3/lib/Doctrine.php";

spl_autoload_register(array('Doctrine', 'autoload'));

require "SoftDeleteTest.php";

$conn = Doctrine_Manager::connection("mysql://softdelete:uahwqeruwer@localhost/softdelete");
$conn->setAttribute(Doctrine::ATTR_USE_DQL_CALLBACKS, true);

$query = Doctrine_Query::create()
    ->from("SoftDeleteTest")
    ->where("name=?", "faulty")
    ->orWhere("name=?", "faulty");

$found = $query->execute();
foreach ($found as $f) {
    echo "ERROR! Found a deleted row: $f->name\n";
}
echo "Done.\n";

SoftDeleteTest.php (copied from Doctrine manual)

<?php

class SoftDeleteTest extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('name', 'string', null, array(
                'primary' => true
            )
        );
    }

    public function setUp()
    {
        $this->actAs('SoftDelete');
    }
}

When you call a stored procedure from a schema other than that of the current connection: [schema_name].[stored_procedure_name]([stored_procedure_arguments]) doctrine miss understands the string and throws a "Couldn't get short alias for" exception.

I fixed this by adding some more regex to the getExpressionOwner method of the Query Class.

I will post the patch shortly.

Will Ferrer

Using ->limit() in conjunction with many-to-many relationships with mysql leads to strange SQL generated. The condition id IS NULL is added in such case which is not correct at all.

Here's example schema

User:
  columns:
    username: { type: string(255) }
  relations:
    Operators:
      foreignAlias: Users
      class:        Operator
      refClass:     OperatorUser

Operator:
  columns:
    username: { type: string(255) }
    type:     { type: integer }


OperatorUser:
  columns:
    user_id:      { type: integer }
    operator_id:  { type: integer }
  relations:
    Operator:
      foreignAlias: OperatorUser
    User:
      foreignAlias: OperatorUser

And here's query which generates wrong SQL

Doctrine_Core::getTable('User')
  ->createQuery('User')
  ->leftJoin('User.Operators Operator')
  ->addWhere('Operator.type = ?', 1)
  ->limit(10)
  ->offset(0)
  ->execute()
;

Expected SQL generated:

SELECT u.id AS u__id, u.username AS u__username, o.id AS o__id, o.username AS o__username, o.type AS o__type
FROM user u
LEFT JOIN operator_user o2  ON (u.id = o2.user_id)
LEFT JOIN operator o        ON o.id = o2.operator_id
WHERE (o.type = '1')
LIMIT 10

Actual SQL generated:

SELECT u.id AS u__id, u.username AS u__username, o.id AS o__id, o.username AS o__username, o.type AS o__type
FROM user u
LEFT JOIN operator_user o2  ON (u.id = o2.user_id)
LEFT JOIN operator o        ON o.id = o2.operator_id
WHERE
  u.id IS NULL # is not expected
  AND (o.type = '1')
# there's no LIMIT clause

Seems like here's code which causes the bug https://github.com/doctrine/doctrine1/blob/master/lib/Doctrine/Query.php#L1307

In the attached Squema run this query against postgreSQL.
(it runs ok In mysql)

$lang = 'en';
$session = 1;
$q = Doctrine_Query::create()
->from('Sys_Trace t')
->leftJoin('t.Sys_Session s')
->leftJoin('t.Translation tr WITH tr.lang = ?', array($lang))
->leftJoin('t.Sys_Oper so')
->leftJoin('so.Translation tr2 WITH tr2.lang = ?', array($lang))
>where('t.session_id = ?', array($session));

SQLSTATE[22P02]: Invalid text representation: 7 ERROR: invalid input syntax for integer: "en"

I think it would be very helpful if the preDql events also worked on connection and manager level. If it is anyway possible.

attach a record listener to a connection object, and it should also call the preDql events when doing DQL queries.

Continuing with same setup as in http://www.doctrine-project.org/jira/browse/DC-417
I discovered another issue

$result = Doctrine_Query::create()
    ->select('u.*, m.*, COUNT(m.id) > 0 AS match_check')
    ->from('User u')
    ->leftJoin('u.Matches m')
    ->groupBy('u.id')
    ->orderBy('match_check DESC, RAND()')
    ->execute(array(), Doctrine::HYDRATE_ARRAY);

match_check will actually be count of matches not 0 or 1 as i expected.
is it bug or feature?

If all selected fields from the root class are aliased,
Doctrine_Query->processPendingFields() won't recognize them as fields of the table and the following exception will be thrown:
"The root class of the query (alias c) must have at least one field selected."

In my real world example, this could only be fixed, using the primary key of the root class/table in select

following will lead to an error:

->leftJoin('alias.RelatedTable alias2 WITH .....')

The problem is the additional whitespace behind alias2.
On line 1682 in Doctrine_Query class file:

$tmp = explode(' ', $path);

will create an array of 3 elements where the last one is empty but will be used as empty alias mapping after foreach from line 1698.

Apache and Php segfault with no error information in the logs and a white screen of death when I use Doctrine 1.2.1 in a Zend Framework controller.

I've traced the problem to line 126 of Zend/Loader/Autoloader.php which only seemed to occur when it autoloaded Doctrine_Hydrator_ArrayDriver during a call to save() in the controller.

I debugged with xdebug and have attached a simple test controller with no other code. The ZF page works fine when all Doctrine is commented out and a Doctrine test page with the same code but outside of ZF worked fine via Apache as did a CLI script.

Workaround is to not push Doctrine to Zend Loader in the bootstrap.

Here is the debug output just before a segfault, the stack and the variable values:

Remote Launch (stepping)
Library/Zend/Loader/Autoloader.php.Zend_Loader_Autoloader::autoload : lineno 126
Library/Zend/Loader/Autoloader.php.is_subclass_of : lineno 0
Library/Doctrine/Query.php.Doctrine_Query->processPendingFields : lineno 477
Library/Doctrine/Query.php.Doctrine_Query->buildSqlQuery : lineno 1213
Library/Doctrine/Query.php.Doctrine_Query->getSqlQuery : lineno 1122
Library/Doctrine/Query/Abstract.php.Doctrine_Query_Abstract->_getDqlCallbackComponents : lineno 1137
Library/Doctrine/Query/Abstract.php.Doctrine_Query_Abstract->_preQuery : lineno 1106
Library/Doctrine/Query/Abstract.php.Doctrine_Query_Abstract->execute : lineno 1001
Library/Doctrine/Template/Listener/Sluggable.php.Doctrine_Template_Listener_Sluggable->getUniqueSlug : lineno 207
Library/Doctrine/Template/Listener/Sluggable.php.Doctrine_Template_Listener_Sluggable->buildSlugFromFields : lineno 120
Library/Doctrine/Template/Listener/Sluggable.php.Doctrine_Template_Listener_Sluggable->preInsert : lineno 65
Library/Doctrine/Record/Listener/Chain.php.Doctrine_Record_Listener_Chain->preInsert : lineno 342
Library/Doctrine/Record.php.Doctrine_Record->invokeSaveHooks : lineno 355
Library/Doctrine/Connection/UnitOfWork.php.Doctrine_Connection_UnitOfWork->insert : lineno 551
Library/Doctrine/Connection/UnitOfWork.php.Doctrine_Connection_UnitOfWork->saveGraph : lineno 81
Library/Doctrine/Record.php.Doctrine_Record->save : lineno 1691
Application/controllers/TestingController.php.TestingController->indexAction : lineno 20
Library/Zend/Controller/Action.php.Zend_Controller_Action->dispatch : lineno 513
Library/Zend/Controller/Dispatcher/Standard.php.Zend_Controller_Dispatcher_Standard->dispatch : lineno 289
Library/Zend/Controller/Front.php.Zend_Controller_Front->dispatch : lineno 946
Library/Zend/Application/Bootstrap/Bootstrap.php.Zend_Application_Bootstrap_Bootstrap->run : lineno 77
Application/Bootstrap.php.Bootstrap->run : lineno 52
Library/Zend/Application.php.Zend_Application->run : lineno 346
Public/index.php.

: lineno 26

=====-=

$class Doctrine_Hydrator_ArrayDriver
$object Zend_Loader_Autoloader
_autoloaders Array [1]
0 Array [2]
0 Doctrine
1 autoload
_defaultAutoloader Array [2]
0 Zend_Loader
1 loadClass
_fallbackAutoloader false
_internalAutoloader Array [2]
0 Zend_Loader_Autoloader
1 _autoload
_namespaces Array [5]
Zend_ true
ZendX_ true
Lisantra_ true
Persistence_ true
Doctrine_ true
_namespaceAutoloaders Array [1]
Array [1]
0 Array [2]
0 Doctrine
1 autoload
_suppressNotFoundWarnings false
$self Zend_Loader_Autoloader
_autoloaders Array [1]
0 Array [2]
0 Doctrine
1 autoload
_defaultAutoloader Array [2]
0 Zend_Loader
1 loadClass
_fallbackAutoloader false
_internalAutoloader Array [2]
0 Zend_Loader_Autoloader
1 _autoload
_namespaces Array [5]
Zend_ true
ZendX_ true
Lisantra_ true
Persistence_ true
Doctrine_ true
_namespaceAutoloaders Array [1]
Array [1]
0 Array [2]
0 Doctrine
1 autoload
_suppressNotFoundWarnings false
$method _autoload
$autoloader Array [0]

Doctrine_Core::getTable('Project')->find($id)
this kind of expression produces query like "SELECT TOP 1 [inner_tbl].[id] AS [p__id] FROM (SELECT TOP 1 [p].[id] AS [p__id], [p].[name] AS [p__name], [p].[description] AS [p__description] FROM [project] [p] WHERE ([p].[id] = ?)) AS [inner_tbl]"
which fails because there is no [inner_tbl].[id]

If i have the following schema:

Medidor:
  tableName: TBLMEDIDOR
  columns:
    CODIGO:
      name: NRCODIGO as codigo
      type: integer(10)
      primary: true
      sequence: SEQMEDCODIGO
    DESCRICAO:
      name: TXDESCRICAO as descricao
      type: string(1000)
      notnull: true

AnaliseMedidor:
  tableName: TBLANALISE_MEDIDOR
  columns:
    CODIGO:
      name: NRCODIGO as codigo
      type: integer(10)
      primary: true
      sequence: SEQANMCODIGO
    DATA:
      name: DTDATA as dataAnalise
      type: date
      notnull: true
    MEDIDOR:
      name: FKMEDCODIGO as medidor_codigo
      type: integer(10)
      notnull: true
    OBSERVACAO:
      name: TXOBSERVACAO as observacao
      type: string(1000)
  relations:
    Medidor: { local: medidor_codigo, foreign: codigo, foreignAlias: Analises }

and execute the following query:

$query = Doctrine::getTable('Medidor')->createQuery()
    ->innerJoin('m.Analises a')
    ->where('a.dataAnalise = (SELECT MAX(a2.dataAnalise) FROM '
        . 'AnaliseMedidor a2 WHERE a2.medidor_codigo = a.medidor_codigo)');

the "a.medidor_codigo" in the subquery don't get correctly mapped. So Doctrine tells me:

{sfDoctrineLogger} executeQuery : SELECT t.nr80102codigo AS t__nr80102codigo, t.tx80102descricao AS t__tx80102descricao FROM TBL80102MEDIDOR t INNER JOIN TBL80103ANALISE_MEDIDOR t2 ON t.nr80102codigo = t2.fk80102codigo WHERE t2.dt80103data = (SELECT MAX(t3.dt80103data) AS t3__0 FROM TBL80103ANALISE_MEDIDOR t3 WHERE t3.fk80102codigo = t2.medidor_codigo)
{Doctrine_Connection_Oracle_Exception} SQLSTATE[HY000]: General error: 904 OCIStmtExecute: ORA-00904: "T2"."MEDIDOR_CODIGO": invalid identifier
 (/root/PDO_OCI-1.0/oci_statement.c:142)

If i put the real name column it runs ok:

$query = Doctrine::getTable('Medidor')->createQuery()
    ->innerJoin('m.Analises a')
    ->where('a.dataAnalise = (SELECT MAX(a2.dataAnalise) FROM '
        . 'AnaliseMedidor a2 WHERE a2.medidor_codigo = a.FKMEDCODIGO)');

But, this should not be done, right?

In Doctrine 1.2 it works fine. But in the related versions don't.

Unfortunatelly oracle has max identifier length for 30 characters. When using longer model names eg. misCompanyContributor with column names the generated aliases for selected columns mix tableized modelname with colum name joined by two __, and sometimes the generated alias should exceed the allowed 30 characters long identifiers, and oracle complains with:

{{
ORA-00972: identifier is too long
}}

An example of large select with alias:
{{
SELECT
...
mis_contributor.contributor_id AS mis_contributor__contributor_id
..
FROM mis_contributor ...
}}

(1) pgsql connection
(2) timestamp with time zone column
(3) $doctrine_expression = new Doctrine_Expression('NOW()');
echo $doctrine_expression->getSQL();
gives:
LOCALTIMESTAMP(0)

What I'd like to get access to using that Doctrine construct is the
functionality of 'CURRENT_TIMESTAMP', (has time zone,
stores time in TIMESTAMP WITH TIME ZONE columns in UTC time)

Fortunately, all the databases that I want to use implement
'CURRENT_TIMESTAMP', but not all PDO/Doctrine databases
support that construct, even though it's ANSI SQL standard,
(which ANSI I don't know.)

When I use the same procedure without Doctrine_Pager everything works fine, with Doctrine_Pager the behavior of later queries is strange. Example:
----------------WORKING:
1. I fetch list
Doctrine_Query::create()>select('z.,s.,o.*,u.jmeno as Vložil,k.nazev_cs as Kategorie')>from('ShopZbozi z,z.Obrazek o,z.Soubor s,z.Uzivatel u,z.ShopKategorie k')>orderby('z.nazev_cs ASC')>fetchArray();

2. I fetch a row to edit independently on the list above
$object = Doctrine::getTable('ShopZbozi')->find(123);

3. edit properties of the object...

4. $object->replace();
the object is fetched with no related objects like [Uzivatel] or [Soubor], so when I change for example $object->iduzivatel (related Uzivatel object id) and save it is OK

----------------BROKEN:
1. I fetch list in Pager
new Doctrine_Pager(Doctrine_Query::create()>select('z.,s.,o.*,u.jmeno as Vložil,k.nazev_cs as Kategorie')>from('ShopZbozi z,z.Obrazek o,z.Soubor s,z.Uzivatel u,z.ShopKategorie k')>orderby('z.nazev_cs ASC'),$this>page,$this->perpage);

2. I fetch a row to edit independently on the list above (in contrast to the first working example this $object is fetched with EMPTY RELATION OBJECTS, which causes inserting empty new rows when calling ->replace())
$object = Doctrine::getTable('ShopZbozi')->find(123);

3. edit properties of the object...

4. $object->replace();
-> the $object has all the related object fetched as it was defined in the list in Pager - like it was used as a template for getTable and when ->replace() - new empty related objects are inserted in DB.

I've been using Doctrine for I guess 2 years now and I believe this bug/strange behavior has always been there.
Maybe I'm wrong and it is not a bug, but the behavior is strange and it took me really long to find where the problem is and I still don't know how to use pager without influencing the later queries.
Thanx John Mathew

Pager breaks when HAVING clause references any existing table column.

Query used:

$q = Doctrine_Query::create()
->select("u.id, u.first_name, u.last_name, u.is_active")
->addSelect('COUNT(u.id) as rolecount')
->from('User u')
->leftJoin('u.Roles r')
->groupBy('u.id')
->having("rolecount > 2 and u.is_active = 1");

Execution of query by itself works fine (eg via execute(), fetch...)

When putting this query in a Doctrine_Pager, error outputs:

$pager = new Doctrine_Pager(
$q,
$currentPage,
$resultsPerPage
);

$pager->execute();

Error Received:
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'u.is_active' in 'having clause'

Trying to add limit() to complex query with many where's and some joins. In pgsql it causes nested limit subquery and in some cases parameter order (when limit subquery exists params array just merges with itself, i. e. (1,2) becomes (1,2,1,2)) makes result query wrong, i. e. params array order doesn't correspond placeholders order in query.

With named query parameters all ok.

Testcase attached.

Hi All

I can not figure out how to make a subquery work in doctrine. I get the subquery to look fine in DQL but when doctrine converts the DQL to SQL the subquery is automatically removed.

Here is an example:
This PHP:

$q = Doctrine_Query::create(); 
$q->from('Customer Customer'); 
$q->addWhere(' Customer.id in (SELECT Customer.id as customer_id FROM Customer Customer)'); 
$q->addSelect('Customer.id'); 
$q->addSelect('Customer.id as customer_id');
$q->limit(20); 

Creates this DQL:

SELECT Customer.id, Customer.id as customer_id FROM Customer Customer WHERE Customer.id in (SELECT Customer.id as customer_id FROM Customer Customer) LIMIT 20

Which creates this broken SQL:

SELECT p.id AS p__id, p.id AS p__0 FROM product_customers p WHERE (p.id in ()) LIMIT 20

Notice that the subquery has been replaced with just "()".

Is there something wrong with my execution, does doctrine just not support subqueries, or is there a bug here?

Thanks much in advance.

Sincerely

Will Ferrer

Calling HYDRATE_ARRAY on a query with

a) No values selected
b) select('*')
c) select('Table.*')

causes the entire query object to be loaded, rather than just returning an array of all values. If a limit(1) is used, the correct result is returned. But as soon as it becomes a collection, the entire query object is returned.

Code to produce problem..

$query = Doctrine_Query::create()
        ->from('Order ord')
        ->limit(2)
        ->execute(array(), Doctrine_Core::HYDRATE_ARRAY);
        
        print_r($query);

When using the Doctrine_Record::option('orderBy', ...) feature on a table definition, where that table is the target of a many-to-many join, the specified orderBy columns are applied to the relation table's alias. So for example, given the following definitions:

class User extends Doctrine_Record {
  public function setTableDefinition() {
    $this->hasColumn('uid', 'integer', null, array('primary' => true));
    $this->option('orderBy', 'uid');
  }

  public function setUp() {
    $this->hasMany('Group as groups', array('refClass' => 'UserGroup', 'local' => 'user_uid', 'foreign' => 'group_id'));
  }
}

class Group extends Doctrine_Record {
  public function setTableDefinition() {
    $this->hasColumn('gid', 'integer', null, array('primary' => true));
  }

  public function setUp() {
    $this->hasMany('User as users', array('refClass' => 'UserGroup', 'local' => 'group_gid', 'foreign' => 'user_id'));
  }
}

class UserGroup extends Doctrine_Record {
  public function setTableDefinition() {
    $this->hasColumn('user_uid', 'integer', null, array('primary' => true));
    $this->hasColumn('group_gid', 'integer', null, array('primary' => true));
  }

  public function setUp() {
    $this->hasOne('User as user', array('local' => 'user_uid', 'foreign' => 'uid'));
    $this->hasOne('Group as group', array('local' => 'group_gid', 'foreign' => 'gid'));
  }
}

the following queries:

$query = Doctrine_Query::create()
  ->select('u.*')
  ->from('User u')
  ->leftJoin('u.groups g WITH g.gid=?', 1);
echo $query->getSqlQuery() . "\n";

$query = Doctrine_Query::create()
  ->select('g.*')
  ->from('Group g')
  ->leftJoin('g.users u WITH u.uid=?', 1);
echo $query->getSqlQuery() . "\n";

will output the following:

SELECT u.uid AS u__uid FROM user u LEFT JOIN user_group u2 ON (u.uid = u2.user_uid) LEFT JOIN group g ON g.gid = u2.group_id AND (g.gid = ?) ORDER BY u.uid
SELECT g.gid AS g__gid FROM group g LEFT JOIN user_group u2 ON (g.gid = u2.group_gid) LEFT JOIN user u ON u.uid = u2.user_id AND (u.uid = ?) ORDER BY u.uid, u2.uid

The orderBy option() call is applied to the User definition. The SQL for the first query is correct (where User is on the left side of the join). The SQL for the second query (where User is on the right-most side of the join), however, is obviously incorrect (UserGroup doesn't even have a uid column). Basically, User's orderBy option is being applied to both the User table and its respective reference table, UserGroup, when it is the target of a join.

After digging through the source for a while, I believe I've come up with a patch for this issue (which should be checked by someone more knowledgeable of Doctrine's internals). Basically, in the Doctrine_Query::buildSqlQuery() function, a call is made to Doctrine_Relation::getOrderByStatement() with the reference table (UserGroup)'s alias (u2), which in turn makes a call to Doctrine_Table::getOrderByStatement() on the referenced table (User), filling in the ORDER BY clause with User columns using UserGroup's alias. My solution was to reorder the logic so that the test for a reference class is made before the initial call to getOrderByStatement() is made. It seems to work against my test case and the test cases in the repository. I'll post my patch momentarily.

This bug was first mentioned in the comments in DC-313, but the original ticket comes across as more of a feature request for the hasMany() orderBy feature.

When trying to use a refclass property - i.e. normalization table column - in a query, an unnecessary join is added to the query created, which is then used to "access" the property.

This issue has already been discussed back in May 2008 but still seems to persist.
Please see the google group discussion http://groups.google.com/group/doctrine-user/browse_thread/thread/c655832a450ccd1a/9cb6eca6ae1c3dee for example + comments.

Unfortunately it is hardly possible to write a test case for this, as working SQL is generated, but the query is just not right (test case basically would have to assert the query string, wich does not make much sense).
Another way to test if it does "what it should", could be to assert contents of $q->getTableAliasMap() - but does not seem ideal either.

John Wage commented on this issue
"Can you create a ticket for this with a failing test case? I believe this
should be possible and we should investigate a fix for it.
Thanks, Jon "
http://groups.google.com/group/doctrine-user/browse_thread/thread/c655832a450ccd1a/9cb6eca6ae1c3dee#msg_c73934a38515cadb

I have a custom hydrator that extends Doctrine_Hydrator_ScalarDriver, for the sole purpose of simplifying the column keys (by passing false as the 3rd parameter on _gatherRowData):

class Doctrine_Hydrator_AssocDriver extends Doctrine_Hydrator_ScalarDriver
{
public function hydrateResultSet($stmt)
{
$cache = array();
$result = array();

while ($data = $stmt->fetch(Doctrine_Core::FETCH_ASSOC))

return $result;
}
}

The processPendingFields method in Doctrine_Query is unnecessarily throwing a Doctrine_Query_Exception at line 465. This can be resolved by skipping hydration methods that extend None, Scalar and SingleScalar.

A diff of the changes is attached.

$query = $query->select('sf_guard_permission.id,
cms_section_translation.slug||\' \'||SUBSTRING("name", POSITION(\'@\' in "name") + 1) as name,
sf_guard_permission.description,
sf_guard_permission.created_at, sf_guard_permission.updated_at');
$query = $query->from('sfGuardPermission sf_guard_permission');
$query = $query->addfrom('CmsSection cms_section');
$query = $query->innerJoin('cms_section.WithCmsSectionTranslation cms_section_translation');
$query = $query->where('cast(SUBSTRING("name", 1, POSITION(\'@\' in "name") - 1) as integer) = cms_section.id');
$query = limit(20);
$query = offset(1);

Generated:

SELECT s.id AS s_id, s.description AS sdescription, s.created_at AS screated_at, s.updated_at AS supdated_at, c2.slug||' '||SUBSTR("name", POSITION('@' in "name") + 1) AS c2_0 FROM sf_guard_permission s, cms_section c INNER JOIN cms_section_translation c2 ON c.id = c2.sec_id WHERE s.id IN (SELECT doctrine_subquery_alias.id FROM (SELECT DISTINCT s2.id, c3.deleted_at FROM sf_guard_permission s2 cms_section c3 INNER JOIN cms_section_translation c4 ON c3.id = c4.sec_id WHERE cast(SUBSTR("name", 1, POSITION('@' in "name") - 1) as integer) = c3.id ORDER BY c3.deleted_at DESC LIMIT 20) AS doctrine_subquery_alias) AND (cast(SUBSTR("name", 1, POSITION('@' in "name") - 1) as integer) = c.id) ORDER BY c.deleted_at DESC

Notice:
SELECT DISTINCT s2.id, c3.deleted_at FROM sf_guard_permission s2 comma that is missing cms_section c3

The following code fails

Doctrine_Query::create()
	->select('Table t1')
	->innerJoin('f1.Foreign2 f2 WITH f1.field = 2')
	->innerJoin('t1.Foreign1 f1')

Doctrine seems only to be happy when we change the order of the inner joins

Doctrine_Query::create()
	->select('Table t1')
	->innerJoin('t1.Foreign1 f1')
	->innerJoin('f1.Foreign2 f2 WITH f1.field = 2')

My schema:

---
Actuality:
  connection:         web
  tableName:          actuality
  actAs:
    softDelete:       ~
    Timestampable:    ~
    Sluggable:
      fields:         [name]
      builder:        [tool, slugify]
      indexName:      actuality_sluggable
      canUpdate:      true
  columns:
    id:
      type:           integer(4)
      primary:        true
      unsigned:       true
      sequence:       actuality
    created_at:
      type:           timestamp
    updated_at:
      type:           timestamp
    published_at:
      type:           timestamp
      notnull:        true
    unpublished_at:
      type:           timestamp
    name:
      type:           string(255)
      notnull:        true
    description_short:
      type:           string(1000)
      notnull:        true
    description:
      type:           clob
      notnull:        true
    is_professional:
      type:           boolean
      default:        true
  relations:
    Categories:
      class: ActualityCategory
      local: actuality_id
      foreign: actuality_category_id
      refClass: ActualityActualityCategory
      foreignAlias: Actualities

And my query:

public function retrieveCloud($is_professional, $limit = 20)
  {
    return $this->createQuery('ac')
    ->leftJoin('ac.Actualities a2')
    ->where('ac.id IN (SELECT aac.actuality_category_id FROM Actuality a LEFTJOIN a.ActualityActualityCategory aac WHERE a.published_at < ? AND a.is_professional = ?)', array(date('Y-m-d H:i:s'), $is_professional))
    ->orderBy('ac.name')
    ->limit($limit)
    ->execute();
  }

I have this error: Doctrine_Connection_Oracle_Exception

ORA-01008: not all variables bound : SELECT a.id AS a__id, a.created_at AS a__created_at, a.updated_at AS a__updated_at, a.name AS a__name, a.deleted_at AS a__deleted_at, a.slug AS a__slug, a2.id AS a2__id, a2.created_at AS a2__created_at, a2.updated_at AS a2__updated_at, a2.published_at AS a2__published_at, a2.unpublished_at AS a2__unpublished_at, a2.name AS a2__name, a2.description_short AS a2__description_short, a2.description AS a2__description, a2.is_professional AS a2__is_professional, a2.deleted_at AS a2__deleted_at, a2.slug AS a2__slug FROM actuality_category a LEFT JOIN actuality_actuality_category a3 ON (a.id = a3.actuality_category_id) LEFT JOIN actuality a2 ON a2.id = a3.actuality_id AND (a2.deleted_at IS NULL) WHERE a.id IN (SELECT a4.id FROM ( SELECT DISTINCT a4.id FROM actuality_category a4 LEFT JOIN actuality_actuality_category a6 ON (a4.id = a6.actuality_category_id) LEFT JOIN actuality a5 ON a5.id = a6.actuality_id AND (a5.deleted_at IS NULL) WHERE a4.id IN (SELECT a8.actuality_category_id AS a5__actuality_category_id FROM actuality a7 LEFT JOIN actuality_actuality_category a8 ON a7.id = a8.actuality_id WHERE (a7.published_at < :oci_b_var_1 AND a7.is_professional = :oci_b_var_2)) AND (a4.deleted_at IS NULL) ORDER BY a4.name ) a4 WHERE ROWNUM <= 20) AND (a.id IN (SELECT a5.actuality_category_id AS a5__actuality_category_id FROM actuality a4 LEFT JOIN actuality_actuality_category a5 ON a4.id = a5.actuality_id WHERE (a4.published_at < :oci_b_var_3 AND a4.is_professional = :oci_b_var_4)) AND (a.deleted_at IS NULL)) ORDER BY a.name

Hi All
I am running into a strange problem using Doctrine-1.2.2-- I have a multi row result set but only the first row of it is returned in the hyrdated array that is generated.
I have tracked this down in the code to get a basic idea of whats going on – it seems that the $id variable in the hydrateResultSet function in Doctrine_Hydrator_Graph isn't being populated properly with data about the fields that are returning from my query. The _gatherRowData function seems to never detect that one of my columns is an identifier ("if ($cache[$key]['isIdentifier'])

$q = Doctrine_Query::create();
$q->from('Customer Customer');
$q->leftJoin('Customer.Zip Zip');
$q->addSelect('Customer.firstname as first_name');
$q->addSelect('Customer.postalcode as postalcode');
$q->setHydrationMode(Doctrine::HYDRATE_ARRAY);

Generates this DQL: 

SELECT Customer.firstname as first_name, Customer.postalcode as postalcode FROM Customer Customer LEFT JOIN Customer.Zip Zip

And this SQL:

SELECT c.firstname AS c_0, c.postalcode AS c_1 FROM customers c LEFT JOIN zips z ON c.postalcode = z.postalcode

Which results in this return after hyrdration: 

array('0'=>array('first_name'=>'Armando', 'postalcode'=>'00659'))

However the following code hydrates just fine: 

$q = Doctrine_Query::create();
$q->from('Customer Customer');
$q->addSelect('Customer.firstname as first_name');
$q->setHydrationMode(Doctrine::HYDRATE_ARRAY);

As does this code: 

$q = Doctrine_Query::create();
$q->from('Customer Customer');
$q->leftJoin('Customer.Zip Zip');
$q->addSelect('Customer.firstname');
$q->addSelect('Customer.postalcode as postalcode');
$q->setHydrationMode(Doctrine::HYDRATE_ARRAY);

Here is the yaml for the sample data I am testing on: 

detect_relations: false
package: Example
options:
type: INNODB
charset: utf8
Customer:
tableName: customers
columns:
customer_id:
type: integer(4)
primary: true
notnull: true
autoincrement: true
firstname:
type: string(45)
lastname:
type: string(45)
streetaddress:
type: string(45)
city:
type: string(45)
state:
type: string(45)
postalcode:
type: string(45)
relations:
Order:
type: many
local: customer_id
foreign: customer_id
Zip:
type: one
local: postalcode
foreign: postalcode
options:
type: InnoDB
Zip:
connection: default_schema
tableName: zips
columns:
postalcode:
type: varchar(30)
primary: true
latitude: 'float(10,6)'
longitude: 'float(10,6)'
city: string(50)
state: string(50)
country: string(50)
type: string(50)
relations:
Customer:
type: many
local: postalcode
foreign: postalcode

Perhaps there is something simple I am overlooking. To get around this I am just always selecting the primary key from my main table in every query.

Thanks in advance for any advice.
Will Ferrer

Following the documentation, I'm using an IN condition in a where clause on a Doctrine_Query with Query cache enabled (APC). Syntax:

$values = array(1, 2, 3, 4);

$q = Doctrine_Query::create()>from('SomeModel s')>where('s.column IN ?', array($values));

$results = $q->execute();

The first time this query is run, all is fine, and any subsequent runs will be fine as well, UNLESS the values array changes length, like so:

$values = array(1, 2, 3, 4, 5);

$q = Doctrine_Query::create()>from('SomeModel s')>where('s.column IN ?', array($values));

$results = $q->execute();

With the query cache enabled, this will throw a PDO "Invalid Parameter Count" exception, because it appears that Doctrine uses the cached query which only has 4 placeholders, but the passed parameters now has a count of 5.

A change in the length of the parameters array in this situation should cause Doctrine to insert a new entry into the query cache for this query.

Let me know if you need more information regarding this, if I find time I can try to put together a Test case.

Thanks!

Wrong array hydration results.

Running toArray() on non hydrated collection
produces different results than using array hydrator.

Case1.
Sorting ASC (works)

$reputationHydrated = Doctrine_Query::create()
->from('User_Model_UserReputation ur')
->leftJoin('ur.User u')
->leftJoin('ur.Thread t')
->leftJoin('t.ForumEntry fe')
->where('u.id = ?', 1)
->orderBy('ur.event ASC)
->execute(null, Doctrine_Core::HYDRATE_ARRAY);

$reputation = Doctrine_Query::create()
->from('User_Model_UserReputation ur')
->leftJoin('ur.User u')
->leftJoin('ur.Thread t')
->leftJoin('t.ForumEntry fe')
->where('u.id = ?', 1)
->orderBy('ur.event ASC)
->execute();

$reputationToArray = $reputation->toArray();

die($reputationToArray === $reputationHydrated); // true, as expected

Case 2.
Sorting DESC (does not work)

$reputationHydrated = Doctrine_Query::create()
->from('User_Model_UserReputation ur')
->leftJoin('ur.User u')
->leftJoin('ur.Thread t')
->leftJoin('t.ForumEntry fe')
->where('u.id = ?', 1)
->orderBy('ur.event DESC)
->execute(null, Doctrine_Core::HYDRATE_ARRAY);

$reputation = Doctrine_Query::create()
->from('User_Model_UserReputation ur')
->leftJoin('ur.User u')
->leftJoin('ur.Thread t')
->leftJoin('t.ForumEntry fe')
->where('u.id = ?', 1)
->orderBy('ur.event DESC)
->execute();

$reputationToArray = $reputation->toArray();

die($reputationToArray === $reputationHydrated); // false, should be true

Other hydrators do hydrate as expected.

Seems that something is wrong in Doctrine_Hydrator_Graph.

I use this schema:

---
Actuality:
  connection:         web
  tableName:          actuality
  actAs:
    softDelete:       ~
    Timestampable:    ~
    Sluggable:
      fields:         [name]
      indexName:      actuality_sluggable
      canUpdate:      true
  columns:
    id:
      type:           integer(4)
      primary:        true
      unsigned:       true
      sequence:       actuality
    created_at:
      type:           timestamp
    updated_at:
      type:           timestamp
    published_at:
      type:           timestamp
      notnull:        true
    unpublished_at:
      type:           timestamp
    name:
      type:           string(255)
      notnull:        true
    description_short:
      type:           string(1000)
      notnull:        true
    description:
      type:           clob
      notnull:        true
    is_professional:
      type:           boolean
      default:        true
  relations:
    Categories:
      class: ActualityCategory
      local: actuality_id
      foreign: actuality_category_id
      refClass: ActualityActualityCategory
      foreignAlias: Actualities


ActualityActualityCategory:
  connection:         web
  tableName:          actuality_actuality_category
  options:
    symfony:
      form:   false
      filter: false
  columns:
    actuality_id:
      type:           integer(4)
      primary:        true
      unsigned:       true
    actuality_category_id:
      type:           integer(2)
      primary:        true
      unsigned:       true
  relations:
    Actuality:
      onDelete:       CASCADE
    

ActualityCategory:
  connection:         web
  tableName:          actuality_category
  actAs:
    softDelete:       ~
    Timestampable:    ~
    Sluggable:
      fields:         [name]
      indexName:      actualityc_sluggable
      canUpdate:      true
  columns:
    id:
      type:           integer(2)
      primary:        true
      unsigned:       true
      sequence:       actualitycategory
    created_at:
      type:           timestamp
    updated_at:
      type:           timestamp
    name:
      type:           string(60)
      notnull:        true

Function in my model Actuality

public function retrieveActive($is_authenticated = false)
{
  $q = $this->createQuery('a')
  ->leftJoin('a.Categories c')
  ->orderBy('a.published_at DESC');
  
  if (!$is_authenticated)
  {
    $q->where('a.is_professional = ?', false);
  }
  
  return $q;
}

with that, i receive the message "Doctrine_Connection_Oracle_Exception" because Oracle is strict and in the subselect, the field published_at is missing in select. This is the query generate by doctrine:

SELECT a.id AS a__id, a.published_at AS a__published_at, a.name AS a__name, a.description_short AS a__description_short, a.slug AS a__slug, a2.id AS a2__id, a2.name AS a2__name FROM actuality a LEFT JOIN actuality_actuality_category a3 ON (a.id = a3.actuality_id) LEFT JOIN actuality_category a2 ON a2.id = a3.actuality_category_id WHERE a.id IN (SELECT a4.id FROM ( SELECT DISTINCT a4.id FROM actuality a4 LEFT JOIN actuality_actuality_category a6 ON (a4.id = a6.actuality_id) LEFT JOIN actuality_category a5 ON a5.id = a6.actuality_category_id WHERE a4.is_professional = 0 ORDER BY a4.published_at DESC ) a4 WHERE ROWNUM <= 5) AND (a.is_professional = 0) ORDER BY a.published_at DESC

If i execute this query and add the field published_at in subquery, this is work.

SELECT a.id AS a__id, a.published_at AS a__published_at, a.name AS a__name, a.description_short AS a__description_short, a.slug AS a__slug, a2.id AS a2__id, a2.name AS a2__name FROM actuality a LEFT JOIN actuality_actuality_category a3 ON (a.id = a3.actuality_id) LEFT JOIN actuality_category a2 ON a2.id = a3.actuality_category_id WHERE a.id IN (SELECT a4.id FROM ( SELECT DISTINCT a4.id, a4.published_at  FROM actuality a4 LEFT JOIN actuality_actuality_category a6 ON (a4.id = a6.actuality_id) LEFT JOIN actuality_category a5 ON a5.id = a6.actuality_category_id WHERE a4.is_professional = 0 ORDER BY a4.published_at DESC ) a4 WHERE ROWNUM <= 5) AND (a.is_professional = 0) ORDER BY a.published_at DESC

Thanks for your help.