Query Builder / DQL does not work with INNER JOIN - syntax problem

Question

I know there is syntax here, but I can't figure it out. I try to make SELECT and INNER JOIN from 5 tables, but Symfony complains that entities in JOIN are used before they are defined.

The actual error is as follows: [Semantical Error] line 0, col 121 near 'I ON C.id = ': Error: Identification Variable MySiteBundle:Items used in join path expression but was not defined before.

Here is the PHP code.

Note. . I reduced this query to two columns, two tables, and one join to give a simple question and show my point. The actual request is much longer and causes the same error.

 $em = $this->getDoctrine()->getEntityManager(); $query = $em->createQuery( 'select C.name as CName, I.id as IId FROM MySiteBundle:Categories C INNER JOIN MySiteBundle:Items I ON C.id = I.category_id'); $result = $query->getResult(); 

---

Update

As I said, I am done with DQL code and am using Query Builder code. I get a very similar error that says 'Categories c': Error: Class 'Categories' is not defined . My QB code is below.

 $em = $this->getDoctrine()->getEntityManager(); $qb = $em->createQueryBuilder() ->select('c.name, i.id, i.image, i.name, i.description, m.id, m.quantity, m.value, m.qty_received, m.custom_image, m.custom_name, m.custom_description, u.user1fname, u.user1lname, u.user2fname, u.user2lname') ->from('Categories', 'c') ->innerJoin('Items', 'i', 'ON', 'c.id = i.category_id') ->innerJoin('MemberItems', 'm', 'ON', 'i.id = m.item_id') ->innerJoin('User', 'u', 'ON', 'm.memberinfo_id = u.id') ->where('u.id = ?', $slug) ->orderBy('c.id', 'ASC') ->getQuery(); $memberItems = $qb->getResult(); 

Any suggestions?

Answer 1

Louis laid out when I was typing. Oh good.

DQL will take care of the details of joining for you based on your associations. In general, you only need to specify the name of the FROM class. Sort of:

 'select C.name as CName, I.id as IId FROM MySiteBundle:Categories C INNER JOIN C.items'); 

And finally use the query builder.

==================================================== =============================

Here is an example of using the query builder in Symfony 2.

 public function getAccounts($params = array()) { // Build query $em = $this->getEntityManager(); $qb = $em->createQueryBuilder(); $qb->addSelect('account'); $qb->addSelect('accountPerson'); $qb->addSelect('person'); $qb->addSelect('registeredPerson'); $qb->addSelect('projectPerson'); $qb->from('ZaysoCoreBundle:Account','account'); $qb->leftJoin('account.accountPersons', 'accountPerson'); $qb->leftJoin('accountPerson.person', 'person'); $qb->leftJoin('person.registeredPersons','registeredPerson'); $qb->leftJoin('person.projects', 'projectPerson'); $qb->leftJoin('projectPerson.project', 'project'); if (isset($params['accountId'])) { $qb->andWhere($qb->expr()->in('account.id',$params['accountId'])); } if (isset($params['projectId'])) { $qb->andWhere($qb->expr()->in('project.id',$params['projectId'])); } if (isset($params['aysoid'])) { $qb->andWhere($qb->expr()->eq('registeredPerson.regKey',$qb->expr()->literal($params['aysoid']))); } $query = $qb->getQuery(); //die('DQL ' . $query->getSQL()); return $query->getResult(); } 

Answer 2

DQL does not use such joins. They are a little simplified. However, I also found them underestimated.

  $em = $this->getDoctrine()->getEntityManager(); $query = $em->createQuery( 'select C.name as CName, I.id as IId FROM MySiteBundle:Categories C INNER JOIN C.items I'); $result = $query->getResult(); 

The actual attitude depends on your model.

I usually use a query builder.

  $em = $this->getEntityManager(); $request = $em->getRepository('MySiteBundle:Categories'); $qb = $request->createQueryBuilder('C'); $query = $qb ->select('C.name, I.id') ->innerJoin('C.items', 'I') ->getQuery(); 

Answer 3

You may need to check your annotations or the yml file to make sure you have the correct settings for OneToMany, ManyToMany, and ManyToOne.

In your MemberItems controller or MemberItems repository, put this:

 $em = $this->getDoctrine()->getEntityManager(); $qb = $em->createQueryBuilder() ->select('c.name, i.id, i.image, i.name, i.description, m.id, m.quantity, m.value, m.qty_received, m.custom_image, m.custom_name, m.custom_description, u.user1fname, u.user1lname, u.user2fname, u.user2lname') ->from('m') ->innerJoin('m.memberinfo_id', 'u') ->innerJoin('m.item_id', 'i') ->innerJoin('i.category_id', 'c') ->where( ->where('u.id = ?', $slug) ->orderBy('c.id', 'ASC') ->getQuery(); $memberItems = $qb->getResult();