1

This query enable me to get all the players given team id and when his physical state is equals to 2.

$qb = $this->createQueryBuilder('player')
    ->where('player.physicalState= 2')
    ->join('player.team', 'team')
    ->addSelect('team')
    ->where('team.id = :id')
    ->setParameter('id', $teamId);

return $qb->getQuery()->execute()

But the fact is that: i have also another table OLD_TEAM. And Player have a one to many relationship with TEAM and OLD_TEAM. So a player can be linked to a TEAM or an OLD_TEAM.

So i want to improve/complete my query to have something like

Search my players in the team with id x in the TEAM table. If this not exist, i want to search the same but in the OLD_TEAM table.

I do not know if this solution is the best solution to search that, don't hesitate to inform me if there exists some easier solution(s).

PS : OLD_TEAM and TEAM use the same sequence for the PK.

Thanks.

Poiz
  • 7,611
  • 2
  • 15
  • 17
LondonUnderground
  • 89
  • 2
  • 12
  • You could try to use different join type too. Add .leftJoin('player.team', 'team').leftJoin('player.old_team', 'old_team') and then just see which one occurs. But imho you should really think that data structure, eg. what about players that belongs to multiple teams? what about players that has been on more than just two teams? and so on... – tarlepp Jul 24 '16 at 19:34

1 Answers1

0

Not sure if that is possible with querybuilder but with pure SQL that is. One example Checking whether an item does not exist in another table that you could use.

Community
  • 1
  • 1
tarlepp
  • 258
  • 2
  • 8
  • Personally I would change data structure, so that these kind of queries are easier. Eg. You could use entities: player, team, player_in_team and that player_in_team has columns; player_id, team_id, date_start, date_end with that data structure you should get fairly easy where each players have played on certain date. – tarlepp Jul 24 '16 at 19:27