So the query I pasted earlier was correct? And the object inversedBy and mappedBy. And this is how it should look in DQL? So how do I add the r.status = 'active'? Basically, I want all the accounts that do not have a role record, and only the accounts that have an active role record back.
On Monday, March 10, 2014 2:10:20 PM UTC-4, Herman Peeren wrote: > > *In DQL there is no ON*: the ON clause defines over what column the > association (relation) is mapped. That information is put in the mapper. > When you define a OneToOne, ManyToOne, OneToMany and ManyToMany relation, > you give information about targetEntity, mappedBy, referencedColumnName > etc. > > On Monday, 10 March 2014 18:19:02 UTC+1, Levi wrote: >> >> Actually, now that I look at it, I would also like to have the r.status >> in the ON clause as well. >> >> >> On Mon, Mar 10, 2014 at 1:17 PM, Levi Stanley <[email protected] >> > wrote: >> >>> I expected the DQL to look something like this: >>> >>> SELECT u, r FROM Application\Entity\User u LEFT JOIN u.userAdminRoles r >>> ON r.user=u.id WHERE (u.site = :site_id AND u.emailAddress = :username >>> AND u.password = :password AND r.status = :status) OR (u.site = :site_id >>> AND u.phoneNumber = :us >>> ername AND u.password = :password AND r.status = :status) >>> >>> >>> >>> >>> On Mon, Mar 10, 2014 at 12:13 PM, Herman Peeren <[email protected]>wrote: >>> >>>> What is wrong with the DQL? What did you expect and what did you get? >>>> >>>> Maybe you are looking for a ManyToMany-association: can a UserAdminRole >>>> be assigned to multiple Users? >>>> >>>> >>>> On Monday, 10 March 2014 15:25:51 UTC+1, Levi wrote: >>>>> >>>>> I am doing a left join, however, it doesn't seem to be working. I >>>>> have a users table, and a roles table, a user can have many roles. This >>>>> is >>>>> what my users attribute looks like: >>>>> >>>>> class User { >>>>> ... >>>>> /** >>>>> * @var \Application\Entity\UserAdminRole[] >>>>> * >>>>> * @ORM\OneToMany(targetEntity="Application\Entity\UserAdminRole", >>>>> mappedBy="user") >>>>> */ >>>>> private $userAdminRoles; >>>>> } >>>>> >>>>> >>>>> In my UserAdminRole class: >>>>> >>>>> Class UserAdminRole { >>>>> ... >>>>> /** >>>>> * @var \Application\Entity\User >>>>> * >>>>> * @ORM\ManyToOne(targetEntity="Application\Entity\User", >>>>> inversedBy="userAdminRoles") >>>>> */ >>>>> private $user; >>>>> } >>>>> >>>>> >>>>> My this is what my query looks like: >>>>> >>>>> $qb = $this->getEntityManager()->createQueryBuilder(); >>>>> $qb->select(array('u', 'r')) >>>>> ->from('Application\\Entity\\User', 'u') >>>>> ->leftJoin('u.userAdminRoles', 'r') >>>>> ->where('u.site = :site_id AND u.emailAddress = >>>>> :username AND u.password = :password AND r.status = :status') >>>>> ->orWhere('u.site = :site_id AND u.phoneNumber = >>>>> :username AND u.password = :password AND r.status = :status') >>>>> ->setParameters( >>>>> array( >>>>> 'site_id' => $siteId, >>>>> 'username' => $username, >>>>> 'password' => $password, >>>>> 'status' => 'active' >>>>> ) >>>>> )->setMaxResults(1); >>>>> >>>>> But this is the resulting DQL: >>>>> >>>>> SELECT u, r FROM Application\Entity\User u LEFT JOIN >>>>> u.userAdminRoles r WHERE (u.site = :site_id AND u.emailAddress = >>>>> :username >>>>> AND u.password = :password AND r.status = :status) OR (u.site = :site_id >>>>> AND u.phoneNumber = :us >>>>> ername AND u.password = :password AND r.status = :status) >>>>> >>>>> There are no exceptions thrown, everything looks fine, however, the >>>>> DQL isn't right. >>>>> >>>>> What am I doing wrong? >>>>> >>>> -- >>>> You received this message because you are subscribed to the Google >>>> Groups "doctrine-user" group. >>>> To unsubscribe from this group and stop receiving emails from it, send >>>> an email to [email protected]. >>>> To post to this group, send email to [email protected]. >>>> Visit this group at http://groups.google.com/group/doctrine-user. >>>> For more options, visit https://groups.google.com/d/optout. >>>> >>> >>> >> -- You received this message because you are subscribed to the Google Groups "doctrine-user" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/doctrine-user. For more options, visit https://groups.google.com/d/optout.
