FAQ Database Discussion Community


Doctrine DQL - how to select only unique records from the dataset

symfony2,doctrine2,dql
I was tasked with selecting a brand and a customer with the highest order value for that brand (together with the order value) from the database. I have come up with this query: $ordersQuery = $em->createQuery( "SELECT b.name AS brand, c.name, MAX(DISTINCT o.value) AS total FROM AppBundle:VOrder o LEFT JOIN...

OneToMany count attribute

php,symfony2,doctrine,dql
I have two entities, post and like, post has a OneToMany to like. Post Class /** * Post * * @ORM\Table() * @ORM\Entity(repositoryClass="Mag\MyBundle\Entity\PostRepository") */ class Post { //... /** *@ORM\OneToMany(targetEntity="Mag\MyBundle\Entity\LikePost",mappedBy="post") * */ private $likes; private $countLikes; //... } Like Class /** * LikePost * * @ORM\Table() * @ORM\Entity */ class...

Query a ManyToMany relation and display the good result in Symfony with Doctrine

php,symfony2,doctrine2,many-to-many,dql
in order to know how it really works, there is an unanswered question from Stack website, and notice that I have the similar problem. In my SQl database, I have two tables: Adverts and Categories Indeed, the Adverts table can contain MANY Categories, and of course a Category can be...

Symfony Entity query builder that checks for free spots

php,mysql,symfony2,doctrine2,dql
First off all: I know the title isn't helping, but it's the best I can think of. So I've got two entities in Symfony one is called Team and the other one Slot the Slot entity has a name, a start and end date and all that stuff. Besides that...

How to build a Docterine Inner Join Query in Symfony

php,mysql,symfony2,dql
Im working on a Symfony project and I want to create Doctrine Query for this SQL. USER table : columns - NICK_NAME REVIEWS table: columns - USER_ID , REVIEW, CREATED_AT Thank you SELECT `USER`.NICK_NAME, REVIEWS.REVIEW, REVIEWS.CREATED_AT FROM REVIEWS INNER JOIN `USER` ON REVIEWS.USER_ID = `USER`.ID WHERE REVIEWS.MOVIE_ID = 625 GROUP...

Symfony2 and Doctrine: Many-to-many relation using query builder

php,symfony2,doctrine2,dql
I have 2 entities: User and Archive. The user entity has, among others, two properties: /** * @ORM\OneToMany(targetEntity="My\ApplicationBundle\Entity\Archive", mappedBy="user") **/ protected $archives; /** * @ORM\ManyToMany(targetEntity="My\ApplicationBundle\Entity\Archive", inversedBy="users") * @ORM\JoinTable(name="collection") **/ private $collection; and the Archive entity: /** * @ORM\ManyToOne(targetEntity="My\UserBundle\Entity\User", inversedBy="archives") * @ORM\JoinColumn(name="user_id", referencedColumnName="id") **/ protected...

Symfony2 & Doctrine: Optimizing Query (Fetching EAGER)

php,symfony2,doctrine,dql
Currently my query looks like this: $customers = $em->createQueryBuilder() ->select('c') ->from('AppBundle:Customer', 'c') ->orderBy('c.id', 'ASC') ->setFirstResult($offset) ->setMaxResults($max) ->getQuery() ->getResult(); This returns me all data of the Customer entity. However, in my Twig template I have to access the @ManyToMany relation that the Customer entity has with PhoneNumber. That causes an extra...

doctrine dql where clause interpretation with associated entity

php,sql,doctrine2,dql
Given this entity class SystemRecord { /** * @ORM\Id * @ORM\Column(type="integer", name="ID") * @ORM\GeneratedValue * @var int */ private $id; /** * @ORM\ManyToOne(targetEntity="Application\Entity\User") * @ORM\JoinColumn(name="USER_SERIAL", referencedColumnName="SERIAL", nullable=false) * @var User */ private $user; /** * @ORM\Column(type="utcdatetime", name="DATE_DATA_WAS_FETCHED", nullable=false) * @var DateTimeInterface */ private $dateDataWasFetched; } ...and this dql $dql =...

Doctrine - How to create inverse relation?

php,symfony2,doctrine2,dql
In symfony I created two entities from a database already created. I used the following commands from the console of symfony: php app/console doctrine:mapping:import --force IDFrontendBundle xml php app/console doctrine:mapping:convert annotation ./src php app/console doctrine:generate:entities IDFrontendBundle Two of the entities that generate me and where I have the problem are...

DQL query produces error Expected Literal, got 'BY'

doctrine2,zend-framework2,dql
I am having a problem with a DQL query to retrieve users and roles from a MySql database. I am using Zend Framework 2 and Doctrine 2. The query is as follows. public function getUsers() { $builder = $this->getEntityManager()->createQueryBuilder(); $builder->select('u, r') ->from('Application\Entity\Users', 'u') ->leftJoin('Application\Entity\UserRoles', 'r') ->orderBy("u.emailAddress", "ASC"); InfoLogger::vardump($builder->getDQL()); return $builder->getQuery()->getResult(Query::HYDRATE_OBJECT);...

Join between ManyToMany properties at Doctrine2 entities

php,symfony2,doctrine2,dql,symfony-2.6
I have this tables relationship: This is how my entities for messages and emails looks like (I've just added the important info): class Message { /** * @var Brand * * @ORM\ManyToOne(targetEntity="Brand") * @ORM\JoinColumn(name="brands_id", referencedColumnName="id") */ protected $brand; ... } class Email { /** * @ORM\ManyToMany(targetEntity="Message", inversedBy="messageXrefMail", cascade={"persist", "remove"}) *...

Symfony DQL - transform the array query result

sql,symfony2,dql
I'm trying to transform the array of my query result. First I did this query: public function findAllTraduction() { return $this->createQueryBuilder('c') ->select('c.key, c.content, c.id, f.locale') ->leftJoin('c.TraductionFile', 'f') ->groupBy('c.key') ->getQuery() ->getArrayResult(); } The result is like that: array 0 => array 'key' => string 'FORMAT1' 'content' => string 'login' 'id' =>...

Symfony/Doctrine - Twig_Error_Runtime: Catchable Fatal Error: Object of class Doctrine\ORM\PersistentCollection could not be converted to string

symfony2,doctrine2,many-to-many,twig,dql
I have this entity in my symfony project: /** * Batiments * * @ORM\Table(name="batiments") * @ORM\Entity * @ORM\Entity(repositoryClass="MySpace\DatabaseBundle\Repository\BatimentsRepository") */ class Batiments { /** * @var integer * * @ORM\Column(name="id", type="integer", nullable=false) * @ORM\Id * @ORM\GeneratedValue(strategy="IDENTITY") */ private $id; /** * @var string * * @ORM\Column(name="nom", type="string", length=150, nullable=true) */ private...

Fill my form with the data that I pick up from a table - Symfony2

forms,symfony2,entity,dql
My problem is this : I have an entity that contains user data , such as: age name surname address phone ... My goal is to make a form where using a query can bring all this data and fill my form with the data , so that the user...

Doctrine fetching indirectly associated objects

database,symfony2,doctrine,one-to-many,dql
I am trying to fetch associated objects through 3 tables (user, client, account). User has a one-to-many relationship to client, client has a one-to-many relationship to account. I easily fetch all the clients for one specific user using this simple code: $user = $this->getUser(); $id = $user->getId(); $user = $this->getDoctrine()->getRepository('AcmeUserBundle:User')->find($id);...

Mysql : Selecting a monthly total occurence count

mysql,dql
In MySQL, I got a table similar to : id user_id date 1 1 2014-09-27 2 1 2014-11-05 3 1 2014-11-14 4 2 2014-12-03 5 1 2014-12-23 I would like to select the total monthly amount of people. ExpectedOutput : 4 2014-09 = 1 user 2014-10 = 0 user 2014-11...

Symfony Form for ManyToMany Entity using a custom query builder

php,mysql,symfony2,symfony-forms,dql
The error message looks like this: [Semantical Error] line 0, col 58 near 'events = :eventId': Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected. Which is right, since it's a ManyToMany join, with a join table. But how do I have to load those entities? The problem is in the activity...