CODEWITHSUNDEEP
databasesymfonyquery-builder
chris_so
chris_so

Reputation: 882

Select from 2 tables in Symfony2

I am making a query inside a form.

The idea is : Now I have a dropdown with all the users, ok?

I have a query like this:

'query_builder' => function(EntityRepository $er) {
      return $er->createQueryBuilder('u')->orderBy('u.lastName', 'ASC');
     }

This works flawless!

Instead of displaying all the users, I only need the users that are associated with a category. This definition is in the "user_data" table (user_id + category_id).

So, I need to do something like:

SELECT * FROM users 
WHERE user_id IN (SELECT user_id FROM user_data WHERE category_id='2')

I don't have any entity that looks like UserData, I only have User.php, but inside this file I found this:

/**
     *@ORM\ManyToMany(targetEntity="My\Bundle\Entity\Data", inversedBy="users")
     *@ORM\joinTable(name="user_data")
     */
    protected $datas;

[...]

So I see that a relationship is build, but I don't get it how to use it in order to build my query based on the 2 tables...

Anyone can help me with this issue? :)

Thanks!

Upvotes: 1

Views: 956

Answers (1)

Ahmed Siouani
Ahmed Siouani

Reputation: 13891

Try this,

'query_builder' => function(EntityRepository $er) {
    return $er->createQueryBuilder('u')
              ->innerJoin('u.datas', 'd')
              ->where('d.category = :category') // Check your Data entity to get the right field name
              ->setParameter('category', $yourCategoryId) // you could use options here
              ->orderBy('u.lastName', 'ASC');
     }

Also, update your question with all your entities (Data, ...)

Upvotes: 3

Related Questions