Mit Propel über LEFT JOIN nicht vorhandene Einträge via IS NULL finden, so gehts

Manchmal braucht man aus der Datenbank die Information darüber, welche Angaben bzw. Beziehungen nicht existieren. In der Propel ORM Dokumentation wird mit dem Beispiel eines Buchladens mit Autoren und Büchern gearbeitet. Daher nehme ich hier gerne auch einmal das Beispiel auf:

Wir wollen also einmal ermitteln welche Autoren keine Bücher haben.

Über reines SQL würde das so aussehen:

SELECT
    author.first_name,
    author.last_name
FROM
    author
LEFT JOIN
    book
    ON(
        author.id = book.author_id
    )
WHERE
    book.author_id IS NULL

Über Propel ist diese SQL ganz einfach zu realisieren:

// use Criteria (needed since propel 2.x)
use Propel\Runtime\ActiveQuery\Criteria;

// build relation
$authors = AuthorQuery::create()
    ->useBookQuery(null, Criteria::LEFT_JOIN)
        ->filterByAuthorId(null, Criteria::ISNULL)
    ->endUse()
    ->find()
;

// output example
foreach ($authors as $author) {
    printf("%d: %s %s\n", $author->getId(), $author->getFirstName(), $author->getLastName());
}

Dieses Beispiel funktioniert natürlich nur, wenn über das Schema über foreign-keys eine Beziehung (relation) zwischen den beiden Tabellen hergestellt wurde, stellt dann aber die einfachste Art der Abfrage dar und ist zudem noch sehr schön und einfach zu lesen.

Natürlich kann man ein LEFT JOIN aber auch „zu Fuß“ darstellen. Diese Variante funktioniert auch wenn keine direkten Beziehungen der Tabellen über das Schema definiert wurden:

use Propel\Runtime\ActiveQuery\Criteria;
$authors = AuthorQuery::create()
    ->leftJoinBook()
    ->where('Book.AuthorId ' . Criteria::ISNULL)
    ->find()
;

Wichtiger Hinweis: Ich rate (derzeit) von der Nutzung des „Alias“ Parameters in den leftJoinXxx() Methoden ab, da dieser beim Bauen der SQL Abfrage teilweise ein CROSS JOIN in die Abfrage einbaut (in MySQL ein Alias für INNER JOIN) und genau dieser Join-Typ soll es ja eben nicht sein. Im Zweifel immer einmal die Abfrage debuggen. Das ist in Propel über die toString() Methode sehr einfach. Wichtig ist jedoch, dass Ihr die Abfrage über die abschließenden Methoden wie find(), findOne(), etc. nicht schon ausgeführt habt. Hier ein Beispiel:

use Propel\Runtime\ActiveQuery\Criteria;
$authors = AuthorQuery::create()
    ->leftJoinBook('b')
    ->where('b.AuthorId ' . Criteria::ISNULL)
;

// debug query that will be built
print "<pre>";
print_r($authors->toString() . "\n");
print "</pre>";

// execute query (after debugging!)
$authors->find();

Noch ein Tipp zum Schluss: Manchmal sind die Abfragen natürlich ein wenig komplexer. Man kann über Propel so ziemlich jede SQL Abfrage nachbauen, so kann man beispielsweise die Join-Bedingungen natürlich auch ganz einfach erweitern:

use Propel\Runtime\ActiveQuery\Criteria;

// version using "useXxxQuery"
$authors = AuthorQuery::create()
    ->useBookQuery(null, Criteria::LEFT_JOIN)
        ->filterByAuthorId(null, Criteria::ISNULL)
    ->endUse()
    ->addJoinCondition('Book', 'Book.AuthorId > ?', 0) // doesn't make sense, just an example ;)
    ->find()
;

// version using "leftJoinXxx"
$authors = AuthorQuery::create()
    ->leftJoinBook()
    ->addJoinCondition('Book', 'Book.AuthorId > ?', 0) // doesn't make sense, just an example ;)
    ->where('Book.AuthorId ' . Criteria::ISNULL)
    ->find()
;

Als Schema Vorlage habe ich das altuelle Original Beispiel-Schema direkt von der Propel Builttime Seite genommen. Für den Fall, dass sich dieses einmal ändern sollte, füge ich es diesem Beitrag einmal an:

<?xml version="1.0" encoding="UTF-8"?>
<database name="test" defaultIdMethod="native">
    <table name="book" phpName="Book">
        <column name="id" type="integer" required="true" primaryKey="true" autoIncrement="true"/>
        <column name="title" type="varchar" size="255" required="true" />
        <column name="isbn" type="varchar" size="24" required="true" phpName="ISBN"/>
        <column name="author_id" type="integer" required="true"/>
        <foreign-key foreignTable="author">
            <reference local="author_id" foreign="id"/>
        </foreign-key>
    </table>
    <table name="author" phpName="Author">
        <column name="id" type="integer" required="true" primaryKey="true" autoIncrement="true"/>
        <column name="first_name" type="varchar" size="128" required="true"/>
        <column name="last_name" type="varchar" size="128" required="true"/>
    </table>
</database>

Ich hoffe dieser Beitrag ist für Euch hilfreich. Wenn ja oder Ihr es anders handhabt, schreibt mir natürlich gerne!