Stack Overflow
Stack Overflow

Questions tagged [dql]

Doctrine Query Language (DQL) is an Object Query Language created for helping users in complex object retrieval.

Summary

DQL stands for Doctrine Query Language and is an Object Query Language derivate that is very similar to the Hibernate Query Language (HQL) or the Java Persistence Query Language (JPQL).

In essence, DQL provides powerful querying capabilities over your object model. Imagine all your objects lying around in some storage (like an object database). When writing DQL queries, think about querying that storage to pick a certain subset of your objects.

Questions

  • How to write DQL statements to achieve a particular SQL statement?
  • Why will a DQL statement not work as expected?

More Information

To learn more about DQL visit

Doctrine 1.2
http://www.doctrine-project.org/projects/orm/1.2/docs/manual/dql-doctrine-query-language/en#dql-doctrine-query-language

Doctrine 2.0
http://www.doctrine-project.org/docs/orm/2.0/en/reference/dql-doctrine-query-language.html

1296 questions
109
votes
4 answers

Symfony2 and Doctrine - Error: Invalid PathExpression. Must be a StateFieldPathExpression

I have an entity that looks like this: /** * @Gedmo\Tree(type="nested") * @ORM\Table(name="categories") * @ORM\Entity() */ class Category extends BaseCategory { /** * @ORM\OneToMany(targetEntity="Category", mappedBy="parent") */ …
Mike
  • 12,359
  • 17
  • 65
  • 86
85
votes
2 answers

How to specify null value as filter in a Doctrine query?

I am using Doctrine 1.1 in Zend. I am trying to write a query that will return records that have a null value in a certain column. $q = Doctrine_Query::create() ->select('a.*') ->from('RuleSet a') ->where('a.vertical_id = ?',…
Mr B
  • 3,980
  • 9
  • 48
  • 74
70
votes
2 answers

How can I use now() in Doctrine 2 DQL?

$ php app/console doctrine:query:dql 'SELECT NOW()' [Doctrine\ORM\Query\QueryException] [Syntax Error] line 0, col 7: Error: Expected known function, got 'now' How can I use MySQL's NOW() function with Doctrine's DQL?
Eduardo Moniz
  • 2,095
  • 3
  • 18
  • 27
55
votes
4 answers

Symfony getting logged in user's id

I am developing an application using Symfony2 and doctrine 2. I would like to know how can I get the currently logged in user's Id.
Haritz
  • 1,702
  • 7
  • 31
  • 50
46
votes
6 answers

Doctrine 2 - How to use discriminator column in where clause

I was used discriminator column in where clause like this: //f = root entity $qb = $this->createQueryBuilder('f'); $qb->add('where', 'f.format = \'image\' OR f.format = \'text\''); I've got an error: "Message: [Semantical Error] line 0, col 73 near…
Can Aydoğan
  • 1,040
  • 2
  • 10
  • 23
45
votes
5 answers

pass array of conditions to doctrine expr()->orx() method

I need to construct DQL with a QueryBuilder like this [QUERY]... AND WHERE e.type = x OR e.type = Y OR e.type = N [...] I have types in array How can I pass this array to my query builder? $qb->andWhere($qb->expr()->orx(CONDITIONS)); List of…
Bartosz Rychlicki
  • 1,918
  • 3
  • 20
  • 41
44
votes
7 answers

Complex WHERE clauses using the PHP Doctrine ORM

I'm using the PHP Doctrine ORM to build my queries. However, I can't quite seem to figure how to write the following WHERE clause using DQL (Doctrine Query Language): WHERE name='ABC' AND (category1 = 'X' OR category2 = 'X' OR category3 = 'X') AND…
Wickethewok
  • 6,534
  • 11
  • 42
  • 40
42
votes
15 answers

How to select randomly with doctrine

Here is how I query my database for some words $query = $qb->select('w') ->from('DbEntities\Entity\Word', 'w') ->where('w.indictionary = 0 AND w.frequency > 3') ->orderBy('w.frequency', 'DESC') ->getQuery() …
Yasser1984
  • 2,401
  • 4
  • 32
  • 55
35
votes
1 answer

Limit amount of records retrieved when using Doctrine DQL in Symfony2

I have the following query: $latestcontent = $em->createQuery(' SELECT c.title, c.content, c.lastedit, a.firstname, a.surname FROM ShoutMainBundle:Content c, ShoutMainBundle:Admin a WHERE c.author = a.id …
mickburkejnr
  • 3,652
  • 12
  • 76
  • 109
33
votes
4 answers

Limiting a doctrine query with a fetch-joined collection?

I have a doctrine query that returns blog posts and their comments: SELECT b, c FROM BlogPost b LEFT JOIN b.comments c I would like to limit the results to 10 blog posts. According to the DQL documentation, setMaxResults() doesn't work correctly on…
Stephen Watkins
  • 25,047
  • 15
  • 66
  • 100
30
votes
2 answers

doctrine2 dql, use setParameter with % wildcard when doing a like comparison

I want to use the parameter place holder - e.g. ?1 - with the % wild cards. that is, something like: "u.name LIKE %?1%" (though this throws an error). The docs have the following two examples: 1. // Example - $qb->expr()->like('u.firstname',…
waigani
  • 3,570
  • 5
  • 46
  • 71
27
votes
2 answers

Symfony2 Doctrine querybuilder where IN

I losted trilion hours google this but none of the solutions were good. I have this querybuilder: $qb2=$this->createQueryBuilder('s') ->addSelect('u') ->innerJoin('s.user','u') ->where("u.id IN(:followeeIds)") …
Lukas Lukac
  • 7,766
  • 10
  • 65
  • 75
26
votes
4 answers

Doctrine 2 DQL - Select rows where a many-to-many field is empty?

I have two classes in this example - DeliveryMethod and Country. They have a many-to-many relationship with each other. What I want to do is select all DeliveryMethods that do not have any Countries mapped to them. I can do the opposite, that is…
Gnuffo1
  • 3,478
  • 11
  • 39
  • 53
25
votes
7 answers

CASTING attributes for Ordering on a Doctrine2 DQL Query

I am trying to get Doctrine2 Entities, ordered by their ID which apparently is a String even though it contains only Numbers. So what I would like to do is something like this: SELECT entity1, cast (entity1.id AS integer) AS orderId FROM…
Andresch Serj
  • 35,217
  • 15
  • 59
  • 101
24
votes
2 answers

Symfony 2 Doctrine find by ordered array of id

I'm looking for a way to use Doctrine in Symfony 2 to find items using an ordered array of id. I have a Card entity with id (primary key) and title. I have a ListCards entity with id (primary key) and a listCards (an array of ids encoded : ["16",…
Benjamin
  • 241
  • 1
  • 2
  • 4
1
2 3
…
86 87