Howto: Symfony doctrine subquery exists

In SQL Subqueries ziemlich häufig: Du braucht nur die Datensätze, bei denen eine bestimmte Bedingung zutrifft, die von der Existenz oder Nicht-Existenz von Datensätzen in einer beknüpften Tabelle abhängt.
Z.B. Du möchstest alle Warenkörbe in der Datenbank löschen, die älter als, sagen wir mal, 7 Tage sind, aber nur wenn sie leer sind, d.h. es also keine Produkte damit verknüpft sind…

In reinem SQL würden wir also sowas wie das hier schreiben:

DELETE 
FROM shopping_basket s 
WHERE s.last_updated < DATE_SUB(NOW(), INTERVAL 7 DAY) 
AND WHERE NOT EXISTS (
    SELECT * 
    FROM shopping_basket_item i 
    WHERE i.shopping_basket_id = s.id
)

Aber wie schreiben wir das in einer Symfony Repository Methode?

Nun, eigentlich ist es recht einfach, und doch ein wenig tricky.

Der Hauptteil (das DELETE) is einfach: sage wir, wir befinden uns im ShoppingBasketRepository und erstellen eine Methode „deleteAllAbandonedAndEmpty„

public function deleteAllAbandonedAndEmpty()
{
    return $this->createQueryBuilder('s')
        ->delete()
        ->andWhere('lastUpdated < :sevendaysago)
        ->setParameter('sevendaysago', new \DateTime('-7 days'))
        ->andWhere(/* hmm hier kommt das Subquery hin - aber wie? */)
        ->getQuery()
        ->getResult()
    ;
}

Nun, Du musst einen weiteren QueryBuilder für das Subquery erstellen und dann dessen DQL als Ausdruck in die zweite andWhere-Klausel einfügen.
Und hier wird es ein wenig tricky, da ein einfaches $this->createQueryBuilder('x') nicht funktioniert.

Warum? Weil dies immer die aktuelle Entity mit 'x' als Alias ebenfalls in das Subquery inkludiert.

Aber hier kommt uns der interne Entity Manager des Repositories zur Hilfe.

Denn $this->createQueryBuilder('x') ist eigentlich nur eine Kurzform für

$this->_em->createQueryBuilder()
     ->select($alias) // $alias ist das 'x' vom $this->createQueryBuilder('x')
     ->from($this->_entityName, $alias);

Aber in unserem Subquery wollen wir das from selbst setzen, also müssen wir nur einfach die Kurzform vermeiden und es selbst von Grund auf aufbauen 😉 – Aber was müssen wir eigentlich genau für das $this->_entityName einsetzen? Um ehrlich zu sein, hier kam mir PHPStorm zur Hilfe und nachdem ich angefangen hatte den Klassennamen zu tippen, schlug er mir die korrekte Syntax vor: App:ShoppingBasketItem – (was genau hängt von Deinem Namespace für die Entities ab).
So, unser Subquery-Builder würde also irgendwie so aussehen:

$sqb = $this->_em->createQueryBuilder()
     ->select('i')
     ->from('App:ShoppingBasketItem', 'i')
     ->andWhere('i.shoppingBasket = /* hmm, was genau? */)
;

Ja, die nächste große Frage, im Original-SQL haben wir einfach die shopping_basket_id von der shopping_basket_item Tabelle mit der id von der shopping_basket Tabelle verglichen, aber in doctrine QueryBuilder hantieren wir nicht mit ids direkt, sondern wir hantieren mit Objekten. Aber in diesem Fall ist es eigentlich recht einfach: wir benutzen den Alias der Haupttabelle (äh, Entity 😉 ): 's'

Also, dies ist dann die vollständige Methode. Beachte, dass wir die Zuweisung zur Variable $qb zuerst abschließen müssen, bevor wir sie wieder für die ->expr -Aufrufe verwenden können!

public function deleteAllAbandonedAndEmpty()
{
    $sqb = $this->_em->createQueryBuilder()
        ->select('i')
        ->from('App:ShoppingBasketItem', 'i')
    ;

    $qb = $this->createQueryBuilder('s')
        ->delete()
        ->andWhere('lastUpdated < :sevendaysago)
        ->setParameter('sevendaysago', new \DateTime('-7 days'))
    ;
    $qb 
        ->andWhere(
            $qb->expr()->not(
                $qb->expr()->exists(
                    $sqb->getDQL()
                )
            )
        )
   ;

   return $qb
        ->getQuery()
        ->getResult()
    ;
}

Ich hoffe das war jetzt so verständlich, dass Du es auf Deine Anforderungen anpassen kannst – und bitte starte jetzt keine Diskussion darüber, dass DATE_SUB(NOW(); INTERVALL 7 DAY) nicht genau dem new \DateTime('-7 days') entspricht – darum geht es hier ja nicht 😉

This post is also available in: Englisch

Schreibe einen Kommentar Antworten abbrechen