How can make a INNER JOIN in a DQL sentence?

Question

I have a problem with a DQL in a Symfony2 project. I have defined 3 entities:

• A entity called Category with two fields : id and slug
• A entity called SubCategory with two fields : id and slug
• A entity called CategorySubCategories with two fields : category and subcategory

I need obtain the subcatgories for a category (slug) given. I got it with the next DQL :

$em ->createQuery('SELECT subcat FROM SubCategory subcat WHERE subcat.id IN
                                        (SELECT IDENTITY(csc.subCategory) FROM  CategorySubCategories csc WHERE csc.category IN
(SELECT cat FROM Category cat WHERE cat.slug = :category))')
                    ->setParameter('category', $category);

Is there anyway to build this DQL with INNER JOINS ?

In MysQL is for example:

SELECT subcat.slug FROM Category cat INNER JOIN CategorySubCategories csc ON (cat.id = csc.category_id) INNER JOIN SubCategory subcat ON (csc.subcategory_id = subcat.id) WHERE cat.slug LIKE "$category"

Is there anyway to translate to DQL ?

Answer 1

In my own opinion, there is no need to have a third entity, two are enough (Category and SubCategory) and just add an association ManyToOne between SubCategory and Category, as described here: http://docs.doctrine-project.org/en/2.1/reference/association-mapping.html