0

I have a Symfony 4 project and I'm using Doctrine DQL in my repositories to grab data from the database.

I have 4 tables: Initiatives, Organisations, Users and Permissions.

Initiatives table: 

id, name, description

Organisations table: 

id, name, description

Users table: 

id, first_name, last_name

Permissions table: 

@int initiative_id - foreign key for Initiatives table 
@string reference - the discriminator column. Can have 'organisation' OR 'user' as a value 
@int reference_id - the ID of the Organisation OR User

so Organisations and Users are the Entities in the discriminator column.

I want to select only the records where the discriminator column has the values organisation and together with the organisation details from the organisations table.

The select statement looks like this:

SELECT i, ip, o FROM Entity:Initiative i
JOIN i.permissions ip
JOIN ip.organisation o
WHERE ip INSTANCE OF Entity:OrganisationPermission

If I run it will show the error:

InitiativePermission has no association named organisation

InitiativePermission being the entity class for the permissions table.

JOIN ip.organisation o is the problem here....and is normal to see this error because of course we don't have an organization column in permissions table...we have a reference and reference_id which can be organisations or user.

How can I JOIN with the organization table by the reference (discriminator) column ?

Can I do this with DQL or I need another solution ?

malarzm
  • 2,831
  • 2
  • 15
  • 25
Daniel
  • 21
  • 3
  • Can't do it with DQL. The discriminator column in internal to Doctrine and not exposed to the developer. You could add a second column with the same value or you could drop down and use sql via the DBAL layer. – Cerad Jul 26 '19 at 15:42
  • I think the only solution I have here is to go with your suggestion, add a 2nd column, one for each entity. – Daniel Jul 27 '19 at 08:46
  • Did you have a look at [Working with Associations](https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/reference/working-with-associations.html#filtering-collections) in the docs? You can do what you're after with a `->matching($criteria)` statement on a Collectoin result. So, if `->getUsers(): Collection`, you can `$this->getUsers()->matching($criteria)`. Also, please don't use `discriminator` like you are, in Doctrine it has [another meaning](https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/reference/dql-doctrine-query-language.html#querying-inherited-classes) – rkeet Jul 29 '19 at 11:56

1 Answers1

0

You could create a "no relation join" with the Organisation entity:

SELECT i, ip, o FROM Entity:Initiative i
JOIN i.permissions ip
LEFT JOIN Entity:Organisation o WITH o.id = ip.referenceId
WHERE ip INSTANCE OF Entity:OrganisationPermission

This way you get only Organisation entities joined. You do not need the discriminator column in this case.

How to do left join in Doctrine?

Jannes Botis
  • 11,154
  • 3
  • 21
  • 39
  • Your solution is good but the result I get is not formatted as I need it. I will probably have to go with 2 separate columns, one for Users and one for Organisations instead of one Reference column. – Daniel Jul 27 '19 at 08:44