Symfony2, JOINS with doctrine2 or sql

Question

I have a question about symfony2.

I have a project and I am using databases with it. I use for the most part Doctrine2 and entity classes. I like the entity class object database stuff, very handy etc.

My question is, is there a way to perform normal SQL in symfony? I always get an exception when I try to use standard SQL. I am having trouble with joins in doctrine2, so i would rather use normal SQL for that.

My join would look like this in SQL:

SELECT DISTINCT Document . * 
FROM Document
INNER JOIN DocumentGruppe ON Document.id = DocumentGruppe.dokId
INNER JOIN UserGruppe ON DocumentGruppe.gruppenId = UserGruppe.gruppenId
WHERE UserGruppe.userId =9

The where clause at the end is just for testing. If I use doctrine with it's DQL it always says that there is an exception: The Variable DocumentGruppe was not defined before.

Here is my DQL query:

    $test = $em->createQuery(
        'SELECT DISTINCT d
        FROM AcmeDocumentBundle:Document d
        INNER JOIN DocumentGruppe dg ON d.id = dg.dokId
        INNER JOIN UserGruppe ug ON dg.gruppenId = ug.gruppenId
        WHERE ug.userId =9
        '
    );

Does anyone know a workaround or a way to use this doctrine2 stuff to work with joins?

AlterPHP AlterPHP · Accepted Answer · 2012-06-12T13:50:24

Every JOINED tables must be declared as associations in mapping... How is your entity defined ? Show us your mapping file (Document.php if annotation, or Resources/config/doctrine/document;xml or yml if XMl or YAML).

Your request will be something like that :

$test = $em->createQuery(
    'SELECT DISTINCT d
    FROM AcmeDocumentBundle:Document d
    INNER JOIN d.documentGruppen dg
    INNER JOIN d.userGruppen ug
    WHERE ug.userId =9
    '
);

Symfony2, JOINS with doctrine2 or sql

1 Answers