How to use join table (ManyToMany relation) in queryBuilder

Question

I have a bidirectional ManyToMany relation : Team <=> Championship with a championships_teams join table.

I would like getting all Teams and order by Teams who are in a specific championship first.

A simple sql query to get this can be : (in Mysql null come first)

select * 
from team left join championships_teams
            on id=team_id
            and championship_id=:specific_id
order by championship_id desc

I have try to do it by using query builder without success.
Can i do this type of query by using query builder without mapping championships_teams as an entity ?

Seems not to be possible [link](http://stackoverflow.com/q/9890540/3497667). I can't use native query because I want to use query in a form in 'query_builder' option. I had to find a dql query who order my query. — label55, Apr 14 '14 at 22:30

score 0 · Answer 1 · edited May 23 '17 at 12:09

0

Take a look at my answer to this question - symfony2 doctrine findBy id in arrayCollection

It seems to be a similar issue. You need to use 'MEMBER OF' modifier.

edited May 23 '17 at 12:09

Community

1
1

answered Apr 14 '14 at 21:01

dmnptr

4,258
1
20
19

score 0 · Accepted Answer · edited May 23 '17 at 12:31

I finally manage my trouble : order by a query with null value to use the result in a form.

We can't access to relation table through DQL : How do you join two tables in Doctrine2 using Query Builder if the table relationships are not setup?. But i can't use native query because i want to get a QueryBuilder object to use it in an entity form.

Making an sql query who be in compliance with DQL.

SELECT t.*,IFNULL(c.year,0) as year
FROM Team t LEFT JOIN championships_teams ct 
        ON t.id = ct.team_id 
    LEFT JOIN Championship c 
        ON c.id = ct.championship_id 
        AND c.id = 76 
WHERE e.league_id = 1
ORDRE BY year;

Passing through the error Expected argument of type "object or array", "string" given by using HIDDEN word : How can I order by NULL in DQL?. To finally have my DQL query :

$qb = $this->createQueryBuilder('t')        
            ->leftJoin('t.championships', 'c',Expr\Join::WITH,'c.id = :champ_id' )
            ->setParameter('champ_id', $champ->getId())
            ->addSelect('IFNULL(-c.year,0) as HIDDEN year')
            ->where('t.league=:league_id')      
            ->setParameter('league_id', $champ->getLeague())
            ->addorderBy('year')
            ->addorderBy('t.nom');
return $qb;

I 'm figuring that my question was not well formulated. Hope this can help somebody.

How to use join table (ManyToMany relation) in queryBuilder

2 Answers2