[DDC-3395] matching(Criteria::create()->orderBy()) is sorting in case insensitive manner Created: 17/Nov/14  Updated: 17/Nov/14

Status: Open
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: Git Master, 2.4.6
Fix Version/s: None
Security Level: All

Type: Bug Priority: Minor
Reporter: Dona Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: case-sensitivity, collation, collection, criteria, sorting


 Description   

for example if we have two strings "IT" and "innovation" after sort by ASC , "IT" is coming before "Innovation".
I was expecting the arrayCollection to do a natural sort.



 Comments   
Comment by Marco Pivetta [ 17/Nov/14 ]

Dona this behavior may change depending on server-side collation anyway.





[DDC-3234] Empty properties when filtering collections Created: 30/Jul/14  Updated: 19/Oct/14  Resolved: 19/Oct/14

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: 2.5, 2.4.2
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Diogo Domanski de Souza Assignee: Marco Pivetta
Resolution: Cannot Reproduce Votes: 0
Labels: Collection, Criteria, orm
Environment:

PHP 5.5.9, Nginx 1.4.6, PHP FPM, Zend Framework 2.3.1, Linux Ubuntu 14.04



 Description   

I'm facing some troubles when filtering an entity association (ArrayCollection) by using Criteria.

The scenario is the following: I have 3 entities:

User.php
<?php

namespace Entity;

use Doctrine\ORM\Mapping as ORM;
use \Doctrine\Common\Collections\Criteria;
use Zend\Stdlib\Hydrator;

/**
 * User
 *
 * @ORM\Table(name="user")
 * @ORM\Entity(repositoryClass="Entity\UserRepository")
 * @ORM\HasLifecycleCallbacks
 * @author domanski
 */
class User implements \Serializable {
	/**
	 *
	 * @ORM\Id
	 * @ORM\Column(name="id", type="integer", nullable=false)
	 * @ORM\GeneratedValue(strategy="AUTO")
	 * @var integer
	 */
	private $id;

	/**
	 * @ORM\Column(name="delete_date", type="datetime")
	 * @var \DateTime
	 */
	private $deleteDate;
	
	public function __construct(array $options = array()) {
		if (!empty($options))
			$this->hydrate($options);
	}

	public function hydrate(array $options = array(), \Doctrine\ORM\EntityManager $em = null) {
		$hydrator = new Hydrator\ClassMethods();
		$hydrator->hydrate($options, $this);
	}

	/**
	 * 
	 * @return int
	 */
	public function getId() {
		return $this->id;
	}

	/**
	 * 
	 * @param int $id
	 * @return \Entity\User
	 */
	public function setId($id) {
		$this->id = $id;
		return $this;
	}

	/**
	 * 
	 * @return \DateTime
	 */
	public function getDeleteDate() {
		return $this->deleteDate;
	}

	/**
	 * @param \DateTime|null $deleteDate
	 * @return \Entity\User
	 */
	public function setDeleteDate($deleteDate = null) {
		$this->deleteDate = $deleteDate;
		return $this;
	}

	/**
	 * 
	 * @return array
	 */
	public function toArray() {
		$result = array(
			'id' => $this->getId(),
			'delete_date' => $this->getDeleteDate()
		);

		return $result;
	}

}
FieldWorker.php
<?php

namespace Entity;

use Doctrine\ORM\Mapping as ORM;
use \Doctrine\Common\Collections\Criteria;

use Zend\Stdlib\Hydrator;

/**
 * Description of FieldWorker
 *
 * @ORM\Entity(repositoryClass="Entity\FieldWorkerRepository")
 * @ORM\Table(name="field_worker")
 * @ORM\HasLifecycleCallbacks
 * @author domanski
 */
class FieldWorker {
	
	/**
	 * This attribute must exist so the inverse join with any other entity can work
	 * 
	 * @ORM\Id
	 * @ORM\Column(type="integer", name="user_id")
	 * @var string
	 */
	protected $id;
	
	/**
	 * 
	 * @ORM\OneToOne(targetEntity="Entity\User")
	 * @ORM\JoinColumn(name="user_id", referencedColumnName="id")
	 * @var \Entity\User
	 */
	protected $user;
	
	public function __construct($options = array()) {		
		if(!empty($options))
			$this->hydrate($options);
	}
	
	public function hydrate(array $options = array(), \Doctrine\ORM\EntityManager $em = null) {
		if(!empty($em)) {
			// user
			if(isset($options['user']))
				$options['user'] = $em->getReference('Entity\User', $options['user']);
			else if(isset($options['user_id']))
				$options['user'] = $em->getReference('Entity\User', $options['user_id']);
						
		}
		
		$hydrator = new Hydrator\ClassMethods();
		$hydrator->hydrate($options, $this);
	}

	/**
	 * 
	 * @return \Entity\User
	 */
	public function getUser() {
		return $this->user;
	}

	/**
	 * 
	 * @param \Entity\User $user
	 * @return \Entity\FieldWorker
	 */
	public function setUser(\Entity\User $user) {
		$this->user = $user;
		$this->id = $user->getId();
		return $this;
	}
	
	/**
	 * 
	 * @return array
	 */
	public function toArray() {
		return $this->getUser()->toArray();
	}
}
Stage.php
<?php

namespace Obra\Entity;

use Doctrine\ORM\Mapping as ORM;
use Zend\Stdlib\Hydrator;
use Doctrine\Common\Collections\Criteria;

/**
 * Description of Stage
 *
 * @ORM\Table(name="stage")
 * @ORM\Entity(repositoryClass="Entity\StageRepository")
 * @ORM\HasLifecycleCallbacks
 * @author domanski
 */
class Stage {

	/**
	 *
	 * @ORM\Id
	 * @ORM\Column(name="id", type="integer", nullable=false)
	 * @ORM\GeneratedValue(strategy="AUTO")
	 * @var integer
	 */
	private $id;

	/**
	 * @ORM\ManyToMany(targetEntity="Entity\FieldWorker")
	 * @ORM\JoinTable(name="stage_field_worker",
	 * 		joinColumns={@ORM\JoinColumn(name="stage_id", referencedColumnName="id")},
	 * 		inverseJoinColumns={@ORM\JoinColumn(name="field_worker_id", referencedColumnName="user_id")}
	 * 	)
	 * @var \Doctrine\Common\Collections\ArrayCollection
	 */
	private $fieldWorkers;

	public function __construct(array $options = array()) {
		$this->fieldWorkers = new \Doctrine\Common\Collections\ArrayCollection();

		if (!empty($options))
			$this->hydrate($options);
	}

	public function hydrate(array $options = array(), \Doctrine\ORM\EntityManager $em = null) {
		$hydrator = new Hydrator\ClassMethods();
		$hydrator->hydrate($options, $this);
	}

	/**
	 * 
	 * @return int
	 */
	public function getId() {
		return $this->id;
	}

	/**
	 * 
	 * @param int $id
	 * @return \Entity\Stage
	 */
	public function setId($id) {
		$this->id = $id;
		return $this;
	}

	/**
	 * 
	 * @return \Doctrine\Common\Collections\ArrayCollection
	 */
	public function getFieldWorkers() {
		$criteria = Criteria::create()
				->where(Criteria::expr()->isNull("user.deleteDate"))
				->orderBy(array("user.name" => Criteria::ASC));

		return $this->fieldWorkers->matching($criteria);
	}

	/**
	 * 
	 * @return \Entity\Stage
	 */
	public function clearFieldWorkers() {
		$this->fieldWorkers->clear();
		return $this;
	}

	/**
	 * 
	 * @param \Entity\FieldWorker $fieldWorker
	 * @return \Entity\Stage
	 */
	public function addFiscal(\Entity\FieldWorker $fieldWorker) {
		$this->fieldWorkers->add($fieldWorker);
		return $this;
	}

	/**
	 * 
	 * @return array
	 */
	public function toArray() {
		$result = array(
			"id" => $this->getId(),
			"field_workers" => array()
		);

		foreach ($this->getFieldWorkers() as $fieldWorker) {
			$result['field_workers'][] = $fieldWorker->toArray();
		}

		return $result;
	}
}

The problem is that whenever the Stage::getFieldWorkers() method is invoked, the list of associated field workers is returned correctly, however if I try to retrieve the respective user of any field worker, it is NULL.

For example:

$stageEntity = $em->getRerefence('Entity\Stage', 1);
$fieldWorkers = $stageEntity->getFieldWorkers();

foreach($fieldWorkers as $fieldWorker) {
   $userId = $fieldWorker->getUser()->getId(); // This throws an error message saying that the result of getUser() is NULL
}

Another example would be if I try to call $stageEntity->toArray() (because it does the same thing as show above).

The database tables are as following:

Database tables creation
CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `delete_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `field_worker` (
  `user_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`user_id`),
  CONSTRAINT `fk_field_worker_user1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `stage` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `stage_field_worker` (
  `stage_id` int(10) unsigned NOT NULL,
  `field_worker_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`stage_id`,`field_worker_id`),
  KEY `fk_stage_field_worker_stage_idx` (`stage_id`),
  KEY `fk_stage_field_worker_field_worker_idx` (`field_worker_id`),
  CONSTRAINT `fk_stage_field_worker_field_worker` FOREIGN KEY (`field_worker_id`) REFERENCES `field_worker` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_stage_field_worker_stage` FOREIGN KEY (`stage_id`) REFERENCES `stage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `user` SET `id` = 1;
INSERT INTO `field_worker` SET `user_id` = 1;
INSERT INTO `stage` SET `id` = 1;
INSERT INTO `stage_field_worker` SET `stage_id` = 1, `field_worker_id` = 1;

After hours trying to understand what was wrong, I decided to make a test and modify the Stage::getFieldWorkers() method by removing the filtering Criteria:

public function getFieldWorkers() {
    return $this->fieldWorkers;
}

By simply doing that, the getUser() method started to return an entity of type User (as expected).

I am not a Doctrine ORM expert, but there seems to be something wrong with ArrayCollection filtering (matching() method)



 Comments   
Comment by Diogo Domanski de Souza [ 30/Jul/14 ]

The same problem occurs with QueryBuilder. For example:

StageRepository.php
<?php

namespace Entity;

use Doctrine\ORM\EntityRepository;

/**
 * Description of StageRepository
 *
 * @author domanski
 */
class StageRepository extends EntityRepository 
{

	public function findByFieldWorkerId($fieldWorkerId) {
		$qb = $this->getEntityManager()->createQueryBuilder()
			->select('s')
			->from('Entity\Stage', 's')
			->innerJoin('s.fieldWorkers', 'f')
			->innerJoin('f.user', 'u', \Doctrine\ORM\Query\Expr\Join::WITH, "u.deleteDate IS NULL AND u.id = :field_worker_id")
				->setParameter('field_worker_id', $fieldWorkerId);

		return $qb->getQuery()->getResult();
	 }
}

If I try to iterate over the result of StageRepository::findByFieldWorkerId() and call toArray() of any element, I get the same error. I've even tried to remove the inner join with User entity from query:

	public function findByFieldWorkerId($fieldWorkerId) {
		$qb = $this->getEntityManager()->createQueryBuilder()
			->select('s')
			->from('Entity\Stage', 's')
			->innerJoin('s.fieldWorkers', 'f');

		return $qb->getQuery()->getResult();
	 }
Comment by Marco Pivetta [ 30/Jul/14 ]

I suspect that something is wrong in your mappings then...

Comment by Diogo Domanski de Souza [ 31/Jul/14 ]

Hi Marco,

The mappings are shown above. The only thing I did was to omit some entities/tables properties/columns that don't have to see with the associations - except the field worker (table and Entity) and stage_field_worker table, that are fully presented.

Maybe is important to notice that the field_worker table has only one column (user_id) that is primary key and foreign key (referencing user.id).

Thanks for you support

Comment by Marco Pivetta [ 31/Jul/14 ]

Diogo Domanski de Souza we can't debug this as it is. We'd need a functional test case to be added to https://github.com/doctrine/doctrine2/tree/0650bb954f5e8d05776f99abd04c81948413299f/tests/Doctrine/Tests/ORM/Functional/Ticket first, in order to see the failure

Comment by Diogo Domanski de Souza [ 31/Jul/14 ]

Marco Pivetta is there any documentation (tutorial, instructions, guide, etc) that I can use to learn how to write the functional test cases that I need?

Comment by Marco Pivetta [ 31/Jul/14 ]

Diogo Domanski de Souza you need to look at the existing ones.

For running the test suite:

git clone git@github.com:doctrine/doctrine2.git
cd doctrine2 
curl -s https://getcomposer.org/installer | php --
./composer.phar install
./vendor/bin/phpunit
Comment by Diogo Domanski de Souza [ 20/Aug/14 ]

I solved the problem by removing the property $id from FieldWorker entity and add annotation @ORM\Id to $user property (in this same entity).

I didn't understand why the previous definition of FieldWorker entity was not working. I have another similar relationship scenario, between 3 different entities, and the error does not occur.

Thanks to all for the support





[DDC-3108] Criteria cannot reference a joined tables' fields when used with an ORM QueryBuilder Created: 30/Apr/14  Updated: 30/Apr/14

Status: Open
Project: Doctrine 2 - ORM
Component/s: DQL, ORM
Affects Version/s: 2.4.2
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Chris Rog Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: criteria, dql, orm, querybuilder


 Description   

This regression was introduced in 2.4.2 with the addition of the "rootAlias" stuff. Basically, the hard-coded addition of the rootAlias + "." prevents any Criteria object from referencing any field that isn't on the first table selected.

Example:
// Assume $repo is a valid EntityRepository and $value is some scalar value.
$qb = $repo->createQueryBuilder('T1')->join('T1.field', 'T2');

$criteria = new Comparison('T2.field2', Comparison::EQ, $value);

$qb->addCriteria($criteria);
$dql = $qb->getDQL();

$dql is now (roughly) equal to:
SELECT T1 FROM <entityclass> T1 JOIN T1.field T2 WHERE T1.T2.field2 = <value>

Evaluating this causes QueryExceptions to be thrown; usually something along the lines of "Expected Doctrine\ORM\Query\Lexer::<token>, got '.'"

There's a similar issue involving ordering by a related field for the same reason.






[DDC-2430] Incorrect results when using ->matching on PersistentCollection Created: 05/May/13  Updated: 09/May/13  Resolved: 09/May/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: 2.4
Fix Version/s: 2.4
Security Level: All

Type: Bug Priority: Major
Reporter: Stuart Carnie Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 0
Labels: criteria
Environment:

Ubuntu 12.04 LTS, PHP 5.4.14



 Description   

When using ->matching() on a PersistentCollection that is already loaded, it returns incorrect results when trying to match by id on a relationship.

// NOTE: the user property is a M:1 relationship of $entity
$c = new Criteria(Criteria::expr()->eq('user', $userId));
$res = $entity->getLikes()->matching($c);

// $res is empty, even if $userId exists



 Comments   
Comment by Benjamin Eberlei [ 09/May/13 ]

The problem is that matching a user by just the id doesn't work for in memory here.
You should use Criteria::expr()->eq('user', $user) instead. The ORM shouldnt allow $userId matching, but this is generic functionality we are reusing here, pretty hard to enforce this. I can take a look.

Comment by Benjamin Eberlei [ 09/May/13 ]

Fixed and introduced a BC break for this.

See https://github.com/doctrine/doctrine2/commit/30f90a6f49d46d2f367ac774aa77e0c7ce1a573f#L0R31 for information.





[DDC-2394] QueryExpressionVisitor has no implementation of Comparison::CONTAINS Created: 08/Apr/13  Updated: 03/Jun/14  Resolved: 14/Apr/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: Git Master
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Boris Guéry Assignee: Benjamin Eberlei
Resolution: Invalid Votes: 0
Labels: criteria, expression, orm, query
Environment:

n/a



 Description   
Use case
$criteria = Criteria::create();
$criteria
    ->andWhere(
        $criteria->expr()->contains('r.body', 'foo')
    )
;

$entities = $repo->createQueryBuilder()->addCriteria($criteria)->getQuery()->getResult();

Throws the following exception:

RuntimeException: Unknown comparison operator: CONTAINS

I except it to properly handle the CONTAINS comparison and result in a LIKE operator.

-------

I added a failing test case & a fix there: https://github.com/borisguery/doctrine2/tree/DDC-2394



 Comments   
Comment by Benjamin Eberlei [ 14/Apr/13 ]

This was added in 2.4, you are probably using Collections 1.1 with ORM 2.3, where this occurs.

Comment by Matthieu Napoli [ 16/Sep/13 ]

Apparently it wasn't, I'm on 2.4 and still get the exception.

I submitted a PR: https://github.com/doctrine/doctrine2/pull/791

Comment by Doctrine Bot [ 11/Oct/13 ]

A related Github Pull-Request [GH-791] was closed:
https://github.com/doctrine/doctrine2/pull/791

Comment by Matthias Althaus [ 27/May/14 ]

As this issue confused me, I took a look into it and the reason seems to be that there's no current release of doctrine/orm including this bugfix. It's not part of the current v2.4.2 tag:

https://github.com/doctrine/doctrine2/blob/v2.4.2/lib/Doctrine/ORM/Query/QueryExpressionVisitor.php
https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/Query/QueryExpressionVisitor.php

We'd be really happy to get a new bugfix release.

Running those lib versions:

doctrine/annotations v1.1.2 Docblock Annotations Parser
doctrine/cache v1.3.0 Caching library offering an object-oriented API for many cache b...
doctrine/collections v1.2 Collections Abstraction library
doctrine/common v2.4.2 Common Library for Doctrine projects
doctrine/data-fixtures v1.0.0 Data Fixtures for all Doctrine Object Managers
doctrine/dbal v2.4.2 Database Abstraction Layer
doctrine/doctrine-bundle v1.2.0 Symfony DoctrineBundle
doctrine/doctrine-fixtures-bundle v2.2.0 Symfony DoctrineFixturesBundle
doctrine/inflector v1.0 Common String Manipulations with regard to casing and singular/p...
doctrine/lexer v1.0 Base library for a lexer that can be used in Top-Down, Recursive...
doctrine/orm v2.4.2 Object-Relational-Mapper for PHP

Comment by Marco Pivetta [ 27/May/14 ]

Matthias Althaus this is a new feature, it won't be backported as bugfix.

Comment by Matthieu Napoli [ 27/May/14 ]

Marco Pivetta I would rather say it's a bug because the feature works with ArrayCollection. It was something missing in the implementation in the ORM. The feature is offered by the interface of the Criteria, so the ORM implementation should support it (else the Collection abstraction is useless).

Comment by Marco Pivetta [ 27/May/14 ]

Matthieu Napoli the criteria expressions support depends on the visitor implementation.
They are not interfaced, and custom expression types are also accepted.

Comment by Matthieu Napoli [ 27/May/14 ]

Beyond code interface, I rather mean "contract" between the library and the user.

The criteria's purpose is a query API that works both on persistent and non-persistent collections: it abstracts the persistence away. By having differences in implementation, the main purpose of the criteria is lost, which IMO is a bug.

Comment by Marco Pivetta [ 27/May/14 ]

Matthieu Napoli there are actually various implementations that just don't support many of the operations (wrote some cache-based adapters and some Zend\Db ones as well). It's just an unsupported case in this version, it is not a bug.

Comment by Matthias Althaus [ 03/Jun/14 ]

I can understand both sides, but it's a pity that the QueryExpressionVisitor has such issues with the Criteria API (which is basically quite nice). We've just build some complex filter management around the Criteria API and now it seems more and more like a bad decision.





[DDC-2378] Efficient count using Selectable Created: 28/Mar/13  Updated: 16/May/14  Resolved: 28/Mar/13

Status: Closed
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: 2.3.2
Fix Version/s: None

Type: Improvement Priority: Major
Reporter: Michaël Gallego Assignee: Marco Pivetta
Resolution: Duplicate Votes: 0
Labels: criteria, selectable


 Description   

Hi,

I'm currently using intensively the Criteria and Selectable interfaces to provide a generic REST library.

However, I've found a problem when I want to paginate data:

count
return count($this->selectable->matching(new Criteria()));

The problem is that EntityRepository returns an ArrayCollection and, hence, load the whole collection which is inefficient. It would be nice if it could return a PersistentCollection instead with lazy-load and perform an optimized count.

Thanks



 Comments   
Comment by Christophe Coevoet [ 28/Mar/13 ]

duplicate of DDC-2217

Comment by Doctrine Bot [ 16/May/14 ]

A related Github Pull-Request [GH-882] was closed:
https://github.com/doctrine/doctrine2/pull/882





[DDC-2061] Matching Criteria on a PersistentCollection only works on OneToMany associations Created: 08/Oct/12  Updated: 16/Apr/14  Resolved: 16/Apr/14

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: Git Master
Fix Version/s: 2.5
Security Level: All

Type: Improvement Priority: Major
Reporter: Terje Bråten Assignee: Guilherme Blanco
Resolution: Fixed Votes: 4
Labels: criteria, matching


 Description   

What is needed to make it also work for ManyToMany associations?

May be a better fallback would be do an ArrayCollection->matching() instead of just giving a runtime exception?

Is this something that is difficult to implement?



 Comments   
Comment by Guilherme Blanco [ 16/Apr/14 ]

This is already possible on 2.5 branch (dev-master for now).
Tracked commit history and found this commit hash as the one that allowed this:

https://github.com/doctrine/doctrine2/commit/160b07d1e3107b9a8210fc73d456305b03146c39





Generated at Sat Dec 20 19:32:39 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.