Artikelformat

Ruby On Rails Datenbankoptimierung Teil 2

SQL Join zweiter Tabellen

[Zu Teil 1]

Verknüpfungen mit dem SQL-join

Relationale Datenbanken erlauben es, Tabellen miteinander zu verknüpfen. In SQL gibt es für diese Verknüpfung das Schlüsselwort “JOIN”. Eine solche Verknüpfung kann theoretisch sehr frei spezifiziert werden, in der Praxis wird man fast immer an der Gleichheit bestimmter ID-Spalten verknüpfen.

In unserem Beispiel eines Schiffsinformationssystems haben wir eine Tabelle”vessels” mit den Schiffsdaten und eine Tabelle “countries” mit den Daten zu einem Flaggenstaat. Typischerweise wollen wir die Schiffsdaten zusammen mit der Daten des dazugehörigen Flaggenstaates ausgeben. Dazu verknüpfen wir die Tabellen an Hand des Fremdschlüssels “legal_country_id”.

In unserem Beispiel liefert ein SQL-Join eine Tabelle, in der Schiffe gleich mit den dazugehörigen Flaggenstaaten eingetragen sind. Die Daten der Tabelle kann man dann auf einer Übersichtseite darstellen. Es ist nur eine Datenbankabfrage nötig, obwohl man Daten aus zwei Tabellen anzeigt.

Es stellt sich dann allerdings die Frage, was eigentlich mit Schiffen ohne Flaggenstaat passiert?

Um das steuern zu können gibt es verschiedene “Geschmacksrichtungen” des Joins:

INNER JOIN: Beide Datensätze müssen vorhanden sein. Wenn einem Schiff (noch) kein Flaggenstaat zugewiesen wurde, wird es auch nicht geliefert.

OUTER JOIN, LEFT JOIN: Es wird auch dann ein Ergebnis geliefert, wenn es keinen verknüpften Datensatz gibt. Es werden also auch Schiffe ohne Flaggenstaaten geliefert. Dabei werden Daten aus der ersten (in Leserichtung linken) Tabelle geliefert.

RIGHT JOIN Es wird auch dann ein Ergebnis geliefert, wenn es nur in der zweiten Tabelle einen Datensatz gibt. Ein RIGHT JOIN würde also Staaten liefern, in denen kein Schiff registriert sind. In der Praxis ist das öfter verwirrend als nützlich, so dass man besser die Reihenfolge umdreht und dann einen LEFT JOIN nimmt.

Wie nutzt man JOIN in Ruby on Rails?

Zunächst gibt es in Ruby schon eine Methode, die “join” heißt, die aber semantisch etwas ganz anderes macht. Sie verbindet die Elemente eines Array zu einem String:

[1, 2, 3, 4].join('-')
'1-2-3-4'

Zusätzlich bietet ActiveRecord bzw. ActiveRelation eine Methode “joins“. Wichtig ist das “s” am Ende. Die Methode sieht zunächst sehr vielversprechend aus.

@company = Company.find_by_name('Hapag-Lloyd')
@company.container_vessels.joins(:legal_country)

Erzeugtes SQL:

SELECT "container_vessels".*
FROM "container_vessels"
INNER JOIN "countries" ON "countries"."id" = "container_vessels"."legal_country_id"
WHERE "container_vessels"."company_id" = 3

Leider liefert die Magie von Ruby-on-Rails diesmal nicht das gewünschte. Es wird zwar ein SQL-Join erzeugt, die Spalten der anderen Tabelle werden jedoch weggeschmissen. Active-Record ist ein sogenannter Object-Relation-Mapper (ORM), und als solcher darauf optimiert, immer Objekte einer Klasse zu liefern, in unserem Fall also ContainerVessel. Wenn man später auf die verknüpften Objekte zugreift, werden diese doch wieder einzeln geholt.

ActiveRecord-Abfragen liefern Objekte der jeweiligen Klasse zurück!

Einziger Effekt des generierten INNER JOIN ist, das nur Schiffe mit existierenden Flaggenstaat geliefert werden. Das zeigt auch auf, wofür die joins() Methode trotzdem gut: Man kann mit ihr Objekte aufgrund von Eigenschaften von verknüpften Objekten auswählen. Beispielsweise kann man so alle Schiffe einer Reederei auswählen, die einen EU-Flaggenstaat haben.

@company.container_vessels.joins(:legal_country).where('countries.in_eu' => 'true')
@company.container_vessels.joins(:legal_country).where(:countries => {:in_eu => true})

Erzeugtes SQL

SELECT "container_vessels".*
FROM "container_vessels"
INNER JOIN "countries" ON "countries"."id" = "container_vessels"."legal_country_id"
WHERE "container_vessels"."company_id" = 3 AND "countries"."in_eu" = 't'

Der SQL-Join führt dazu, das die passenden Datensätze schon in der Datenbank ausgewählt werden, der Rails-Prozess sieht die Country-Datensätze noch nicht mal. Und was nicht da ist, kostet keinen Hauptspeicher und auch keine Performance.

In der Syntax muss man aufpassen: In der Spezifikation der Bedingungen bewegt man sich auf SQL-Ebene (‘countries’), während man in der joins-Methode die Rails-Association angibt(‘legal_country’).

Die Vervielfachung von Unternehmen

Nehmen wir an, wir wollen wissen, welche Reedereien auch in jüngster Zeit in ihrer Flotte investieren. Dazu können wir Beispielsweise abfragen, welche Reedereien neue Schiffe in den Dienst gestellt haben, also z.B. mit einem Baujahr nach 2009. Die Schiffe selbst interessieren uns nicht. Die Schiffsdatensätze brauchen deshalb auch nicht von der Datenbank in den Railsprozess übertragen werden. Alles deutet darauf hin, das man das Problem ähnlich wie oben lösen kann:

@companies = Company.order(:name).
        joins(:container_vessels).where(["container_vessels.build_year > ?", 2009])

Die erzeugte Datenbankabfrage sieht auch sinnvoll aus:

SELECT "companies".* 
FROM "companies" 
INNER JOIN "container_vessels" 
  ON "container_vessels"."company_id" = "companies"."id" 
WHERE (container_vessels.build_year > 2009) 
ORDER BY name

In der Ergebnisanzeige sehen wir eine Überraschung: Die Unternehmen werden vervielfacht. Was ist passiert?

Eine Reederei hat viele Schiffe. Der Datenbank-Join liefert eine Zeile für für jede passende Kombination von Schiff und Reederei. Im Grunde bilden wir eine Tabelle von Schiffen zusammen mit ihren Unternehmen. Aus dieser Tabelle werden jetzt nur die Unternehmensdaten angezeigt (SELECT companies.*). So erscheinen einige Unternehmen dann plötzlich mehr als 30 mal.
Abhilfe schafft eine explizite Eingrenzung mit DISTINCT.

@companies = Company.order(:name).select("DISTINCT companies.*").
        joins(:container_vessels).where(["container_vessels.build_year > ?", 2009])
SELECT DISTINCT companies.* 
FROM "companies" 
INNER JOIN "container_vessels" 
  ON "container_vessels"."company_id" = "companies"."id" 
WHERE (container_vessels.build_year > 2009) 
ORDER BY name

Man sieht, das die Rails-Methode joins() nur mit Vorsicht zu genießen ist: Echte verbundene Tabellen erhält man nicht, sondern immer Objekte einer Klasse. Wenn man nicht aufpasst werden weniger Objekte geliefert als man wollte, oder es werden die Objekte mehrfach geliefert.

Autor: Karsten Meier

Weil ich gerne Probleme löse bin ich Informatiker geworden. Für meine Kunden berate und konzeptioniere ich und entwickle mit fast allem, was einen Prozessor hat. Sie finden mich auch auf Twitter

Hinterlassen Sie eine Antwort

Pflichtfelder sind mit * markiert.