4.6. Suchen und Finden mit Queries

Rails 5.1 Consulting und Schulung vom Autor:
www.wintermeyer-consulting.de/rails/

4.6. Suchen und Finden mit Queries

Die Methoden first und all sind ja schon ganz nett, aber meistens will man ja doch nach etwas ganz Bestimmten mit einem Suchbegriff suchen (Query).

Für die Beschreibung von Abfragen mit Queries erstellen wir ein neues Rails-Projekt:

MacBook:~ xyz$ rails new jukebox
[...]
MacBook:~ xyz$ cd jukebox 
MacBook:jukebox xyz$ rails generate model album name release_year:integer
[...]
MacBook:jukebox xyz$ rake db:migrate
[...]
MacBook:jukebox xyz$

Für die hier verwendeten Beispiele nehmen Sie eine db/seeds.rb mit folgendem Inhalt:

Album.create("name"=>"Sgt. Pepper's Lonely Hearts Club Band", "release_year"=>1967)
Album.create("name"=>"Pet Sounds", "release_year"=>1966)
Album.create("name"=>"Revolver", "release_year"=>1966)
Album.create("name"=>"Highway 61 Revisited", "release_year"=>1965)
Album.create("name"=>"Rubber Soul", "release_year"=>1965)
Album.create("name"=>"What's Going On", "release_year"=>1971)
Album.create("name"=>"Exile on Main St.", "release_year"=>1972)
Album.create("name"=>"London Calling", "release_year"=>1979)
Album.create("name"=>"Blonde on Blonde", "release_year"=>1966)
Album.create("name"=>"The Beatles", "release_year"=>1968)

Danach die Datenbank mit rake db:setup neu aufsetzen:

MacBook:jukebox xyz$ rake db:setup
db/development.sqlite3 already exists
-- create_table("albums", {:force=>true})
   -> 0.0308s
-- initialize_schema_migrations_table()
   -> 0.0003s
-- assume_migrated_upto_version(20120426133607, ["/Users/xyz/jukebox/db/migrate"])
   -> 0.0005s
MacBook:jukebox xyz$

find vs. where

Es gibt in ActiveRecord zwei prinzipielle Suchmethoden: find und where. Logischerweise stellt sich dem Rails-Anfänger direkt die Frage "Welche Methode soll ich eher benutzen?". Eine Schwarz/Weiß-Antwort auf diese Frage fällt mir schwer. Ich selber benutze zu 80 % where und bei den restlichen 20 % könnte ich auch where benutzen, aber find ist dann einen Tick praktischer und der Code ist besser lesbar. Probieren Sie am besten beide Methoden einmal aus und entscheiden Sie dann aus dem Bauch heraus.

Tipp

Bitte lesen Sie „where“ und „find“. Besonders empfehlen möchte ich „Lazy Loading“, um die Optimierungen und ein wichtiges Konzept von where zu verstehen.

find

Der einfachste Fall ist die Suche eines Datensatzes anhand eines Primary Key (also per Default dem id-Feld in der Datenbank-Tabelle). Wenn ich die ID eines Objektes (hier: einer Datensatz-Zeile) kenne, kann ich das einzelne Objekt oder mehrere Objekte gleichzeitig anhand der ID suchen:

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.find(2)
  Album Load (4.0ms)  SELECT "albums".* FROM "albums" WHERE "albums"."id" = ? LIMIT 1  [["id", 2]]
 => #<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04"> 
1.9.3p194 :002 > Album.find([1,3,7])
  Album Load (0.4ms)  SELECT "albums".* FROM "albums" WHERE "albums"."id" IN (1, 3, 7)
 => [#<Album id: 1, name: "Sgt. Pepper's Lonely Hearts Club Band", release_year: 1967, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04">, #<Album id: 3, name: "Revolver", release_year: 1966, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04">, #<Album id: 7, name: "Exile on Main St.", release_year: 1972, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04">] 
1.9.3p194 :003 > exit
MacBook:jukebox xyz$

Wenn man immer ein Array als Ergebnis haben will, muss man immer auch ein Array als Parameter übergeben:

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.find(5).class
  Album Load (4.0ms)  SELECT "albums".* FROM "albums" WHERE "albums"."id" = ? LIMIT 1  [["id", 5]]
 => Album(id: integer, name: string, release_year: integer, created_at: datetime, updated_at: datetime) 
1.9.3p194 :002 > Album.find([5]).class
  Album Load (0.2ms)  SELECT "albums".* FROM "albums" WHERE "albums"."id" = ? LIMIT 1  [["id", 5]]
 => Array 
1.9.3p194 :003 > Album.find([1,3,7]).class
  Album Load (0.4ms)  SELECT "albums".* FROM "albums" WHERE "albums"."id" IN (1, 3, 7)
 => Array 
1.9.3p194 :004 > exit
MacBook:jukebox xyz$

Warnung

Die Methode find generiert eine Exception, wenn die gesuchte ID keinen Datensatz in der Datenbank hat. Im Zweifelsfall kann/muss man where (siehe „where“) benutzen, find_by_* (siehe „Attribut-basierendes find_by_*, find_last_by_* und find_all_by_* (find_by_attributes)“) oder mit der Methode exists? (siehe „exists?“) vorher prüfen, ob der Datensatz existiert:^[24]

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.last
  Album Load (0.1ms)  SELECT "albums".* FROM "albums" ORDER BY "albums"."id" DESC LIMIT 1
 => #<Album id: 10, name: "The Beatles", release_year: 1968, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04"> 
1.9.3p194 :002 > Album.find(50)
  Album Load (33.6ms)  SELECT "albums".* FROM "albums" WHERE "albums"."id" = ? LIMIT 1  [["id", 50]]
ActiveRecord::RecordNotFound: Couldn't find Album with id=50
 from /Users/xyz/.rvm/gems/ruby-1.9.3-p194/gems/activerecord-3.2.3/lib/active_record/relation/finder_methods.rb:340:in `find_one'
 from /Users/xyz/.rvm/gems/ruby-1.9.3-p194/gems/activerecord-3.2.3/lib/active_record/relation/finder_methods.rb:311:in `find_with_ids'
 from /Users/xyz/.rvm/gems/ruby-1.9.3-p194/gems/activerecord-3.2.3/lib/active_record/relation/finder_methods.rb:107:in `find'
 from /Users/xyz/.rvm/gems/ruby-1.9.3-p194/gems/activerecord-3.2.3/lib/active_record/querying.rb:5:in `find'
 from (irb):2
 from /Users/xyz/.rvm/gems/ruby-1.9.3-p194/gems/railties-3.2.3/lib/rails/commands/console.rb:47:in `start'
 from /Users/xyz/.rvm/gems/ruby-1.9.3-p194/gems/railties-3.2.3/lib/rails/commands/console.rb:8:in `start'
 from /Users/xyz/.rvm/gems/ruby-1.9.3-p194/gems/railties-3.2.3/lib/rails/commands.rb:41:in `<top (required)>'
 from script/rails:6:in `require'
 from script/rails:6:in `<main>'
1.9.3p194 :003 > Album.exists?(50)
  Album Exists (0.2ms)  SELECT 1 FROM "albums" WHERE "albums"."id" = 50 LIMIT 1
 => false 
1.9.3p194 :004 > exit
MacBook:jukebox xyz$

Attribut-basierendes find_by_, find_last_by_ und find_all_by_* (find_by_attributes)

Das Attribut-basierende find_by_* ist eine „magische“ Methode, denn sie kann von Ihnen dynamisch erweitert werden (deshalb werden diese Methoden auch "dynamic finders" genannt). Die Funktionalität ist die gleiche wie die der Methode where (siehe „where“), aber die Lesbarkeit des Codes wird besser (jedenfalls bei wenigen Parametern). Man kann treffend über den Sinn oder den Unsinn streiten. Probieren Sie es einmal aus, und entscheiden Sie selber, welchen Weg Sie gehen wollen.

Ein Beispiel auf der Console sagt mehr als tausend Worte. Ich verwende erst where und dann find_by_* und find_last_by_* für gleiche Abfragen:

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.where( :release_year => 1966).first
  Album Load (0.2ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" = 1966 LIMIT 1
 => #<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04"> 
1.9.3p194 :002 > Album.find_by_release_year(1966)
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" = 1966 LIMIT 1
 => #<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04"> 
1.9.3p194 :003 > Album.where( :release_year => 1966 ).last
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" = 1966 ORDER BY "albums"."id" DESC LIMIT 1
 => #<Album id: 9, name: "Blonde on Blonde", release_year: 1966, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04"> 
1.9.3p194 :004 > Album.find_last_by_release_year(1966)
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" = 1966 ORDER BY "albums"."id" DESC LIMIT 1
 => #<Album id: 9, name: "Blonde on Blonde", release_year: 1966, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04"> 
1.9.3p194 :005 > exit
MacBook:jukebox xyz$

Wichtig

Im Gegensatz zu where gibt find_by_* entweder ein Objekt der gesuchten Klasse (hier Album) oder nil zurück. where gibt immer ein Array zurück! Wenn man auf diesen Unterschied beim Programmieren nicht achtet, bekommt man über kurz oder lang einen Fehler an dieser Stelle.

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.find_by_release_year(1972)
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" = 1972 LIMIT 1
 => #<Album id: 7, name: "Exile on Main St.", release_year: 1972, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04"> 
1.9.3p194 :002 > Album.find_by_release_year(1972).class
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" = 1972 LIMIT 1
 => Album(id: integer, name: string, release_year: integer, created_at: datetime, updated_at: datetime) 
1.9.3p194 :003 > Album.find_by_release_year(2010)
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" = 2010 LIMIT 1
 => nil 
1.9.3p194 :004 > Album.find_by_release_year(2010).class
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" = 2010 LIMIT 1
 => NilClass 
1.9.3p194 :005 > exit
MacBook:jukebox xyz$

Die Methode find_all_by_* gibt wie where immer ein Array zurück:

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.find_all_by_release_year(2020).class
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" = 2020
 => Array 
1.9.3p194 :002 > Album.find_all_by_release_year(1966).class
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" = 1966
 => Array 
1.9.3p194 :003 > exit
MacBook:jukebox xyz$

Verkettung von mehreren Attributen

Mit den Methoden find_by_*, find_last_by_* und find_all_by_* lassen sich auch mehrere Such-Attribute mit and verketten. Unsere aktuellen Datensätze lassen keine richtig guten Beispiele dafür zu. Deshalb ein paar wenig sinnvolle, aber die Methode beschreibende Abfragen, in der mit and Attribute verkettet werden:

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.find_by_id_and_release_year(1, 1967)
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."id" = 1 AND "albums"."release_year" = 1967 LIMIT 1
 => #<Album id: 1, name: "Sgt. Pepper's Lonely Hearts Club Band", release_year: 1967, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04"> 
1.9.3p194 :002 > Album.find_all_by_id_and_release_year(1, 1967)
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."id" = 1 AND "albums"."release_year" = 1967
 => [#<Album id: 1, name: "Sgt. Pepper's Lonely Hearts Club Band", release_year: 1967, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04">] 
1.9.3p194 :003 > Album.find_by_id_and_name(5, 'The Beatles')
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."id" = 5 AND "albums"."name" = 'The Beatles' LIMIT 1
 => nil 
1.9.3p194 :004 > Album.find_all_by_id_and_name(5, 'The Beatles')
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."id" = 5 AND "albums"."name" = 'The Beatles'
 => [] 
1.9.3p194 :005 > exit
MacBook:jukebox xyz$

find_or_create_by_*

Häufig hat man beim Programmieren die Aufgabenstellung, einen bestimmten Datensatz zu suchen und falls er nicht existiert, dann diesen anzulegen. Das lässt sich sehr schön mit find_or_create_by_* in einem Schritt erledigen (achten Sie auf den SQL-Code):

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > beatles = Album.find_or_create_by_name('The Beatles')
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."name" = 'The Beatles' LIMIT 1
 => #<Album id: 10, name: "The Beatles", release_year: 1968, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04"> 
1.9.3p194 :002 > ray_charles = Album.find_or_create_by_name('Crying Time')
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."name" = 'Crying Time' LIMIT 1
   (0.1ms)  begin transaction
  SQL (8.5ms)  INSERT INTO "albums" ("created_at", "name", "release_year", "updated_at") VALUES (?, ?, ?, ?)  [["created_at", Mon, 30 Apr 2012 14:17:14 UTC +00:00], ["name", "Crying Time"], ["release_year", nil], ["updated_at", Mon, 30 Apr 2012 14:17:14 UTC +00:00]]
   (1.4ms)  commit transaction
 => #<Album id: 11, name: "Crying Time", release_year: nil, created_at: "2012-04-30 14:17:14", updated_at: "2012-04-30 14:17:14"> 
1.9.3p194 :003 > exit
MacBook:jukebox xyz$

Warnung

Bei der Benutzung von find_or_create_by_* sollte man immer sicher sein, dass der eventuell neu anzulegende Datensatz in sich selber valide ist. Dies lässt sich einfach mit der Methode valid? realisieren (siehe Abschnitt 4.15, „Validierung (Validation)“). Ein nicht valider Datensatz wird von find_or_create_by_* nicht abgespeichert!

Wenn Sie zwar nach einem Albumnamen suchen, aber beim eventuellen create auch noch das release_year angeben wollen, so geht das auch mit find_or_create_by_*:

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > crying_time = Album.find_or_create_by_name('Genius Loves Company', :release_year => 2004)
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."name" = 'Genius Loves Company' LIMIT 1
   (0.1ms)  begin transaction
  SQL (5.2ms)  INSERT INTO "albums" ("created_at", "name", "release_year", "updated_at") VALUES (?, ?, ?, ?)  [["created_at", Mon, 30 Apr 2012 14:21:03 UTC +00:00], ["name", "Genius Loves Company"], ["release_year", 2004], ["updated_at", Mon, 30 Apr 2012 14:21:03 UTC +00:00]]
   (3.5ms)  commit transaction
 => #<Album id: 12, name: "Genius Loves Company", release_year: 2004, created_at: "2012-04-30 14:21:03", updated_at: "2012-04-30 14:21:03"> 
1.9.3p194 :002 > exit
MacBook:jukebox xyz$

find_or_initialize_by_*

Die Methode find_or_initialize_by_* arbeitet wie find_or_create_by_*. Allerdings gibt es einen entscheidenden Unterschied: find_or_initialize_by_* speichert einen neuen Datensatz nicht ab. Das muss später per save erfolgen. Beispiel:

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > i_got_a_woman = Album.find_or_initialize_by_name('I Got a Woman', :release_year => 1955)
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."name" = 'I Got a Woman' LIMIT 1
 => #<Album id: nil, name: "I Got a Woman", release_year: 1955, created_at: nil, updated_at: nil> 
1.9.3p194 :002 > Album.last
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" ORDER BY "albums"."id" DESC LIMIT 1
 => #<Album id: 12, name: "Genius Loves Company", release_year: 2004, created_at: "2012-04-30 14:21:03", updated_at: "2012-04-30 14:21:03"> 
1.9.3p194 :003 > i_got_a_woman.save
   (0.1ms)  begin transaction
  SQL (6.8ms)  INSERT INTO "albums" ("created_at", "name", "release_year", "updated_at") VALUES (?, ?, ?, ?)  [["created_at", Mon, 30 Apr 2012 14:23:25 UTC +00:00], ["name", "I Got a Woman"], ["release_year", 1955], ["updated_at", Mon, 30 Apr 2012 14:23:25 UTC +00:00]]
   (3.5ms)  commit transaction
 => true 
1.9.3p194 :004 > Album.last
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" ORDER BY "albums"."id" DESC LIMIT 1
 => #<Album id: 13, name: "I Got a Woman", release_year: 1955, created_at: "2012-04-30 14:23:25", updated_at: "2012-04-30 14:23:25"> 
1.9.3p194 :005 > exit
MacBook:jukebox xyz$

Performance

Auch wenn die Methoden where und find_all_by_* sich oft gleich anfühlen, so gibt es bei einigen Szenarien deutliche Performance-Unterschiede. Vereinfacht gesagt: Mit where machen Sie nie einen Fehler, da where „Lazy Loading“ benutzt. Auf Lazy Loading gehen wir in „Lazy Loading“ dediziert ein. Hier aber schon mal ein kleiner Vorgeschmack:

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.where(:release_year => 1966).count
   (0.2ms)  SELECT COUNT(*) FROM "albums" WHERE "albums"."release_year" = 1966
 => 3 
1.9.3p194 :002 > Album.find_all_by_release_year(1966).count
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" = 1966
 => 3 
1.9.3p194 :003 > exit
MacBook:jukebox xyz$

Die Kombination der Methoden where und count erstellt eine SQL-Abfrage, die das Berechnen des Ergebnisses der SQL-Datenbank überlässt. Die Kombination der Methoden find_all_by_* und count fragt erst alle Datensätze bei der Datenbank ab, speichert diese dann in einem Array und zählt anschließend die Einträge in diesem Array. Das macht bei unserer Mini-Datenbank kaum einen Unterschied (0.1 ms). Wenn Sie aber mit einer sehr großen Datenbank arbeiten, dann wollen Sie die maximale Performance der SQL-Datenbank ausnutzen und nicht erst Daten hin- und herschieben.

Wie bereits erwähnt: Diese Thematik wird in „Lazy Loading“ detailliert besprochen.

where

Mit der Methode where kann man nach bestimmten Werten in der Datenbank suchen. Suchen wir mal alle Alben aus dem Jahr 1966:

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.where( :release_year => 1966 )
  Album Load (0.2ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" = 1966
 => [#<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 3, name: "Revolver", release_year: 1966, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 9, name: "Blonde on Blonde", release_year: 1966, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">] 
1.9.3p194 :002 > Album.where( :release_year => 1966 ).count
   (0.3ms)  SELECT COUNT(*) FROM "albums" WHERE "albums"."release_year" = 1966
 => 3 
1.9.3p194 :003 > exit
MacBook:jukebox xyz$

Sie können mit where auch nach Ranges (Bereichen, siehe „Range“) suchen:

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.where( :release_year => 1960..1966 )
  Album Load (0.2ms)  SELECT "albums".* FROM "albums" WHERE ("albums"."release_year" BETWEEN 1960 AND 1966)
 => [#<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 3, name: "Revolver", release_year: 1966, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 4, name: "Highway 61 Revisited", release_year: 1965, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 5, name: "Rubber Soul", release_year: 1965, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 9, name: "Blonde on Blonde", release_year: 1966, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">] 
1.9.3p194 :002 > Album.where( :release_year => 1960..1966 ).count
   (0.3ms)  SELECT COUNT(*) FROM "albums" WHERE ("albums"."release_year" BETWEEN 1960 AND 1966)
 => 5 
1.9.3p194 :003 > exit
MacBook:jukebox xyz$

Und Sie können auch mehrere Suchfaktoren mit Komma abgetrennt gleichzeitig angeben:

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.where( :release_year => 1960..1966, :id => 1..5 )
  Album Load (0.2ms)  SELECT "albums".* FROM "albums" WHERE ("albums"."release_year" BETWEEN 1960 AND 1966) AND ("albums"."id" BETWEEN 1 AND 5)
 => [#<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 3, name: "Revolver", release_year: 1966, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 4, name: "Highway 61 Revisited", release_year: 1965, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 5, name: "Rubber Soul", release_year: 1965, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">] 
1.9.3p194 :002 > Album.where( :release_year => 1960..1966, :id => 1..5 ).count
   (0.3ms)  SELECT COUNT(*) FROM "albums" WHERE ("albums"."release_year" BETWEEN 1960 AND 1966) AND ("albums"."id" BETWEEN 1 AND 5)
 => 4 
1.9.3p194 :003 > exit
MacBook:jukebox xyz$

Oder auch ein Array von Parametern:

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.where( :release_year => [1966, 1968] )
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" IN (1966, 1968)
 => [#<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 3, name: "Revolver", release_year: 1966, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 9, name: "Blonde on Blonde", release_year: 1966, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 10, name: "The Beatles", release_year: 1968, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">] 
1.9.3p194 :002 > Album.where( :release_year => [1966, 1968] ).count
   (0.3ms)  SELECT COUNT(*) FROM "albums" WHERE "albums"."release_year" IN (1966, 1968)
 => 4 
1.9.3p194 :003 > exit
MacBook:jukebox xyz$

Wichtig

Das Ergebnis von where ist immer ein Array. Auch wenn nur ein Treffer drin ist oder auch wenn gar kein Treffer zurückgegeben wird. Wenn Sie den ersten Treffer suchen, dann müssen Sie where mit der Methode first verketten:

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.where( :release_year => [1966, 1968] ).first
  Album Load (0.2ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" IN (1966, 1968) LIMIT 1
 => #<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13"> 
1.9.3p194 :002 > Album.where( :release_year => [1966, 1968] ).first.class
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" IN (1966, 1968) LIMIT 1
 => Album(id: integer, name: string, release_year: integer, created_at: datetime, updated_at: datetime) 
1.9.3p194 :003 > exit
MacBook:jukebox xyz$

SQL-Abfragen mit where

Manchmal kommt man nicht umhin und muss tatsächlich eine SQL-Abfrage selber definieren und durchführen. Es gibt in ActiveRecord zwei verschiedene Arten, dies zu machen. Die eine „reinigt“ (sanitize) jede Abfrage vor dem Ausführen und die andere gibt die Abfrage 1 zu 1 so an die SQL-Datenbank weiter. Im Normalfall sollte man immer die sanitize-Variante benutzen, weil man sonst sehr schnell Opfer einer SQL-Einschleusungs-Attacke (SQL injection) werden kann (siehe http://de.wikipedia.org/wiki/SQL-Injection).

Falls Sie sich mit SQL nicht auskennen, dann können Sie diesen Abschnitt ruhig überspringen. Die hier verwendeten SQL-Befehle werden nicht gesondert erklärt.

sanitized (gereinigte) Abfragen

Bei dieser Variante werden alle dynamischen Suchanteile durch ein Fragezeichen als Platzhalter ersetzt und erst nach dem SQL-String als Parameter aufgelistet.

Nachfolgend suchen wir alle Alben, in deren Namen der String „on“ enthalten ist:

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.where( 'name like ?', '%on%' )
  Album Load (0.2ms)  SELECT "albums".* FROM "albums" WHERE (name like '%on%')
 => [#<Album id: 1, name: "Sgt. Pepper's Lonely Hearts Club Band", release_year: 1967, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 6, name: "What's Going On", release_year: 1971, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 7, name: "Exile on Main St.", release_year: 1972, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 8, name: "London Calling", release_year: 1979, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 9, name: "Blonde on Blonde", release_year: 1966, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">] 
1.9.3p194 :002 > Album.where( 'name like ?', '%on%' ).count
   (0.2ms)  SELECT COUNT(*) FROM "albums" WHERE (name like '%on%')
 => 5 
1.9.3p194 :003 > exit
MacBook:jukebox xyz$

Jetzt alle Alben, die ab 1965 veröffentlicht wurden:

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.where( 'release_year > ?', 1964 ).count
   (0.1ms)  SELECT COUNT(*) FROM "albums" WHERE (release_year > 1964)
 => 10 
1.9.3p194 :002 > exit
MacBook:jukebox xyz$

Alle Alben, die jünger als 1970 sind und deren Namen den String „on“ enthalten:

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.where( 'name like ? AND release_year > ?', '%on%', 1970 ).count
   (0.2ms)  SELECT COUNT(*) FROM "albums" WHERE (name like '%on%' AND release_year > 1970)
 => 3 
1.9.3p194 :002 > exit
MacBook:jukebox xyz$

Wenn in der Variable apfelmus der gesuchte String enthalten ist, dann kann folgendermaßen danach gesucht werden:

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > apfelmus = 'ing'
 => "ing" 
1.9.3p194 :002 > Album.where( 'name like ?', "%#{apfelmus}%").count
   (0.1ms)  SELECT COUNT(*) FROM "albums" WHERE (name like '%ing%')
 => 2 
1.9.3p194 :003 > exit
MacBook:jukebox xyz$

„Gefährliche“ SQL-Abfragen

Wenn Sie wirklich wissen, was Sie tun, dann können Sie natürlich auch komplett die SQL-Abfrage definieren und auf das Reinigen (sanitize) der Abfrage verzichten.

Zählen wir einmal alle Alben, in deren Namen der String „on“ enthalten ist:

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.where( "name like '%on%'" ).count
   (0.5ms)  SELECT COUNT(*) FROM "albums" WHERE (name like '%on%')
 => 5 
1.9.3p194 :002 > exit

Bitte benutzen Sie diese Variante nur, wenn Sie genau wissen, was Sie tun und nachdem Sie sich mit dem Thema SQL-Injections beschäftigt haben (siehe http://de.wikipedia.org/wiki/SQL-Injection).

Lazy Loading

Seit Rails 3.0 wird bei ActiveRecords die Methode where und damit Lazy Loading verwendet. Dies ist ein Mechanismus, der eine Datenbank-Abfrage erst dann stellt, wenn der weitere Programmablauf nicht ohne das Ergebnis dieser Abfrage realisiert werden kann. Bis dahin wird die Anfrage als ActiveRecord::Relation gespeichert. (Das Gegenteil von Lazy Loading nennt man übrigens Eager Loading.)

Schauen wir uns die Abfrage nach allen Alben aus dem Jahr 1966 an.

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.where(:release_year => 1966)
  Album Load (0.2ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" = 1966
 => [#<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04">, #<Album id: 3, name: "Revolver", release_year: 1966, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04">, #<Album id: 9, name: "Blonde on Blonde", release_year: 1966, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04">] 
1.9.3p194 :002 > Album.where(:release_year => 1966).class
 => ActiveRecord::Relation 
1.9.3p194 :003 > Album.where(:release_year => 1966).to_sql
 => "SELECT \"albums\".* FROM \"albums\"  WHERE \"albums\".\"release_year\" = 1966" 
1.9.3p194 :004 > exit
MacBook:jukebox xyz$

Wenn aber Album.where(:release_year => 1966) eine ActiveRecord::Relation ist, warum bekommen wir dann in der Console ein Array ausgegeben? Rails will uns das Entwicklerleben etwas einfacher machen und zeigt uns quasi automatisch das Ergebnis der Methode all an:

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.where(:release_year => 1966).class
 => ActiveRecord::Relation 
1.9.3p194 :002 > Album.where(:release_year => 1966).all.class
  Album Load (0.2ms)  SELECT "albums".* FROM "albums" WHERE "albums"."release_year" = 1966
 => Array 
1.9.3p194 :003 > exit
MacBook:jukebox xyz$

Prinzipiell klar, aber der Sinn noch nicht ganz? Dann bauen wir uns mal eine Abfrage zusammen, in der wir mehrere Methoden verschachteln. In dem folgenden Beispiel wird a immer weiter definiert und erst ganz zum Schluss (beim Aufruf der Methode all) würde bei einem Produktivsystem die Datenbank-Abfrage wirklich getätigt. Mit der Methode to_sql kann man sich immer das aktuelle SQL-Query ausgeben lassen.

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > a = Album.where(:release_year => 1965..1968)
  Album Load (0.2ms)  SELECT "albums".* FROM "albums" WHERE ("albums"."release_year" BETWEEN 1965 AND 1968)
 => [#<Album id: 1, name: "Sgt. Pepper's Lonely Hearts Club Band", release_year: 1967, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04">, #<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04">, #<Album id: 3, name: "Revolver", release_year: 1966, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04">, #<Album id: 4, name: "Highway 61 Revisited", release_year: 1965, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04">, #<Album id: 5, name: "Rubber Soul", release_year: 1965, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04">, #<Album id: 9, name: "Blonde on Blonde", release_year: 1966, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04">, #<Album id: 10, name: "The Beatles", release_year: 1968, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04">] 
1.9.3p194 :002 > a.class
 => ActiveRecord::Relation 
1.9.3p194 :003 > a = a.order(:release_year)
  Album Load (0.5ms)  SELECT "albums".* FROM "albums" WHERE ("albums"."release_year" BETWEEN 1965 AND 1968) ORDER BY release_year
 => [#<Album id: 4, name: "Highway 61 Revisited", release_year: 1965, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04">, #<Album id: 5, name: "Rubber Soul", release_year: 1965, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04">, #<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04">, #<Album id: 3, name: "Revolver", release_year: 1966, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04">, #<Album id: 9, name: "Blonde on Blonde", release_year: 1966, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04">, #<Album id: 1, name: "Sgt. Pepper's Lonely Hearts Club Band", release_year: 1967, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04">, #<Album id: 10, name: "The Beatles", release_year: 1968, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04">] 
1.9.3p194 :004 > a = a.limit(3)
  Album Load (0.5ms)  SELECT "albums".* FROM "albums" WHERE ("albums"."release_year" BETWEEN 1965 AND 1968) ORDER BY release_year LIMIT 3
 => [#<Album id: 4, name: "Highway 61 Revisited", release_year: 1965, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04">, #<Album id: 5, name: "Rubber Soul", release_year: 1965, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04">, #<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04">] 
1.9.3p194 :005 > exit
MacBook:jukebox xyz$

Automatische Optimierung

Einer der großen Vorteile von Lazy Loading ist die automatische Optimierung der SQL-Abfrage durch ActiveRecord.

Ziehen wir mal die Summe aller Veröffentlichungsjahre der Alben, die in den 70ern herausgebracht wurden. Und danach sortieren wir die Alben nach Namen und ziehen anschließend die Summe.

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.where(:release_year => 1970..1979).sum(:release_year)
   (3.1ms)  SELECT SUM("albums"."release_year") AS sum_id FROM "albums" WHERE ("albums"."release_year" BETWEEN 1970 AND 1979)
 => 5922 
1.9.3p194 :002 > Album.where(:release_year => 1970..1979).order(:name).sum(:release_year)
   (0.3ms)  SELECT SUM("albums"."release_year") AS sum_id FROM "albums" WHERE ("albums"."release_year" BETWEEN 1970 AND 1979)
 => 5922 
1.9.3p194 :003 > exit
MacBook:jukebox xyz$

Das Ergebnis ist bei beiden Abfragen logischerweise identisch. Interessant ist aber, dass ActiveRecord bei beiden Anfragen den gleichen SQL-Code benutzt hat. Es hat erkannt, dass order bei sum völlig irrelevant ist und es deshalb direkt herausgenommen.

Anmerkung

Falls Sie sich fragen, warum die erste Abfrage 3.1 ms und die zweite 0.3 ms gedauert hat: ActiveRecord cached die Ergebnisse einer SQL-Abfrage innerhalb eines Webseitenaufrufes.

exists?

Hin und wieder muss man wissen, ob es einen bestimmten Datensatz gibt, und genau dafür gibt es die Methode exists?. Sie gibt als Ergebnis true oder false zurück:

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.first
  Album Load (0.1ms)  SELECT "albums".* FROM "albums" LIMIT 1
 => #<Album id: 1, name: "Sgt. Pepper's Lonely Hearts Club Band", release_year: 1967, created_at: "2012-04-30 13:45:04", updated_at: "2012-04-30 13:45:04"> 
1.9.3p194 :002 > Album.exists?(1)
  Album Exists (0.2ms)  SELECT 1 FROM "albums" WHERE "albums"."id" = 1 LIMIT 1
 => true 
1.9.3p194 :003 > Album.exists?(10000)
  Album Exists (0.2ms)  SELECT 1 FROM "albums" WHERE "albums"."id" = 10000 LIMIT 1
 => false 
1.9.3p194 :004 > exit
MacBook:jukebox xyz$

Die komplette Hilfe zur Methode exists? können Sie sich mit ri ActiveRecord::FinderMethods.exists? anzeigen lassen.

order und reverse_order

Jede Datenbank-Abfrage kann mit der Methode order sortiert werden. Beispiel: Alle Alben aus den 60ern nach Namen sortiert:

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.where( :release_year => 1960..1969 ).order(:name)
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE ("albums"."release_year" BETWEEN 1960 AND 1969) ORDER BY name
 => [#<Album id: 9, name: "Blonde on Blonde", release_year: 1966, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 4, name: "Highway 61 Revisited", release_year: 1965, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 3, name: "Revolver", release_year: 1966, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 5, name: "Rubber Soul", release_year: 1965, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 1, name: "Sgt. Pepper's Lonely Hearts Club Band", release_year: 1967, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 10, name: "The Beatles", release_year: 1968, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">] 
1.9.3p194 :002 > exit
MacBook:jukebox xyz$

Mit der Methode reverse_order können wir eine mit order definierte Reihenfolge umkehren:

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.where(:release_year => 1960..1969).order(:name).reverse_order
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE ("albums"."release_year" BETWEEN 1960 AND 1969) ORDER BY name DESC
 => [#<Album id: 10, name: "The Beatles", release_year: 1968, created_at: "2012-04-30 14:51:03", updated_at: "2012-04-30 14:51:03">, #<Album id: 1, name: "Sgt. Pepper's Lonely Hearts Club Band", release_year: 1967, created_at: "2012-04-30 14:51:03", updated_at: "2012-04-30 14:51:03">, #<Album id: 5, name: "Rubber Soul", release_year: 1965, created_at: "2012-04-30 14:51:03", updated_at: "2012-04-30 14:51:03">, #<Album id: 3, name: "Revolver", release_year: 1966, created_at: "2012-04-30 14:51:03", updated_at: "2012-04-30 14:51:03">, #<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-04-30 14:51:03", updated_at: "2012-04-30 14:51:03">, #<Album id: 4, name: "Highway 61 Revisited", release_year: 1965, created_at: "2012-04-30 14:51:03", updated_at: "2012-04-30 14:51:03">, #<Album id: 9, name: "Blonde on Blonde", release_year: 1966, created_at: "2012-04-30 14:51:03", updated_at: "2012-04-30 14:51:03">] 
1.9.3p194 :002 > exit
MacBook:jukebox xyz$

limit

Das Ergebnis jeder Suche kann mit der Methode limit auf einen bestimmten Bereich eingegrenzt werden.

Die ersten 5 Alben aus den 60ern:

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.where( :release_year => 1960..1969).limit(5)
  Album Load (0.2ms)  SELECT "albums".* FROM "albums" WHERE ("albums"."release_year" BETWEEN 1960 AND 1969) LIMIT 5
 => [#<Album id: 1, name: "Sgt. Pepper's Lonely Hearts Club Band", release_year: 1967, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 3, name: "Revolver", release_year: 1966, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 4, name: "Highway 61 Revisited", release_year: 1965, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 5, name: "Rubber Soul", release_year: 1965, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">] 
1.9.3p194 :002 > exit
MacBook:jukebox xyz$

Alle Alben nach Namen sortieren und davon die ersten 5:

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.order(:name).limit(5)
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" ORDER BY name LIMIT 5
 => [#<Album id: 9, name: "Blonde on Blonde", release_year: 1966, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 7, name: "Exile on Main St.", release_year: 1972, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 4, name: "Highway 61 Revisited", release_year: 1965, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 8, name: "London Calling", release_year: 1979, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">] 
1.9.3p194 :002 > exit
MacBook:jukebox xyz$

offset

Mit der Methode offset kann die Startposition der Methode limit definiert werden.

Als Erstes geben wir die ersten beiden Datensätze zurück und danach die ersten beiden Datensätze mit einem Offset von 5:

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.limit(2)
  Album Load (0.2ms)  SELECT "albums".* FROM "albums" LIMIT 2
 => [#<Album id: 1, name: "Sgt. Pepper's Lonely Hearts Club Band", release_year: 1967, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">] 
1.9.3p194 :002 > Album.limit(2).offset(5)
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" LIMIT 2 OFFSET 5
 => [#<Album id: 6, name: "What's Going On", release_year: 1971, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 7, name: "Exile on Main St.", release_year: 1972, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">] 
1.9.3p194 :003 > exit
MacBook:jukebox xyz$

group

Mit der Methode group kann das Ergebnis einer Abfrage gruppiert zurückgegeben werden.

Geben wir erst mal alle Alben nach dem Erscheinungsjahr gruppiert zurück:

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.group(:release_year)
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" GROUP BY release_year
 => [#<Album id: 5, name: "Rubber Soul", release_year: 1965, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 9, name: "Blonde on Blonde", release_year: 1966, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 1, name: "Sgt. Pepper's Lonely Hearts Club Band", release_year: 1967, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 10, name: "The Beatles", release_year: 1968, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 6, name: "What's Going On", release_year: 1971, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 7, name: "Exile on Main St.", release_year: 1972, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">, #<Album id: 8, name: "London Calling", release_year: 1979, created_at: "2012-04-26 13:40:13", updated_at: "2012-04-26 13:40:13">] 
1.9.3p194 :002 > exit
MacBook:jukebox xyz$

pluck

Normalerweise zieht ActiveRecord immer alle Tabellenspalten aus der Datenbank und überlässt es dem Programmierer später die für ihn interessanten Komponenten herauszuholen. Bei großen Datenmengen kann es aber praktisch und vor allen Dingen sehr viel schneller sein, direkt bei der Abfrage ein bestimmtes Datenbank-Feld für die Abfrage zu definieren. Das kann mit der Methode pluck gemacht werden. Wollen Sie alle name-Einträge für alle Alben aus den 1960ern?

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.where(:release_year => 1960..1969).pluck(:name)
   (0.2ms)  SELECT name FROM "albums" WHERE ("albums"."release_year" BETWEEN 1960 AND 1969)
 => ["Sgt. Pepper's Lonely Hearts Club Band", "Pet Sounds", "Revolver", "Highway 61 Revisited", "Rubber Soul", "Blonde on Blonde", "The Beatles"] 
1.9.3p194 :002 > exit
MacBook:jukebox xyz$

Als Ergebnis liefert pluck ein Array.

Berechnungen

average

Mit der Methode average lässt sich der Durchschnitt der Werte in einer bestimmten Spalte in der Tabelle ausrechnen. Dafür ist unser Datenmaterial natürlich schlecht geeignet. Als Erstes berechnen wir das Durchschnitts-Veröffentlichungsjahr aller Alben und danach das Gleiche für die Alben aus den 60ern:

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.average(:release_year)
   (3.7ms)  SELECT AVG("albums"."release_year") AS avg_id FROM "albums" 
 => #<BigDecimal:7fbdd1f01ef0,'0.19685E4',18(45)> 
1.9.3p194 :002 > Album.average(:release_year).to_s
   (0.3ms)  SELECT AVG("albums"."release_year") AS avg_id FROM "albums" 
 => "1968.5" 
1.9.3p194 :003 > Album.where( :release_year => 1960..1969 ).average(:release_year)
   (0.3ms)  SELECT AVG("albums"."release_year") AS avg_id FROM "albums" WHERE ("albums"."release_year" BETWEEN 1960 AND 1969)
 => #<BigDecimal:7fbdd1b07980,'0.1966142857 142857E4',27(45)> 
1.9.3p194 :004 > Album.where( :release_year => 1960..1969 ).average(:release_year).to_s
   (0.2ms)  SELECT AVG("albums"."release_year") AS avg_id FROM "albums" WHERE ("albums"."release_year" BETWEEN 1960 AND 1969)
 => "1966.142857142857" 
1.9.3p194 :005 > exit
MacBook:jukebox xyz$

count

Die Methode count zählt die Anzahl der Datensätze.

Als Erstes geben wir die Anzahl aller Alben in der Datenbank zurück und danach die Anzahl der Alben in den 60ern:

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.count
   (0.1ms)  SELECT COUNT(*) FROM "albums" 
 => 10 
1.9.3p194 :002 > Album.where( :release_year => 1960..1969 ).count
   (0.2ms)  SELECT COUNT(*) FROM "albums" WHERE ("albums"."release_year" BETWEEN 1960 AND 1969)
 => 7 
1.9.3p194 :003 > exit
MacBook:jukebox xyz$

maximum

Mit der Methode maximum kann aus einer Abfrage der Eintrag mit dem höchsten Wert ausgegeben werden.

Suchen wir das höchste Veröffentlichungsjahr:

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.maximum(:release_year)
   (0.2ms)  SELECT MAX("albums"."release_year") AS max_id FROM "albums" 
 => 1979 
1.9.3p194 :002 > exit
MacBook:jukebox xyz$

minimum

Mit der Methode minimum kann aus einer Abfrage der Eintrag mit dem niedrigsten Wert ausgegeben werden.

Suchen wir das niedrigste Veröffentlichungsjahr:

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.minimum(:release_year)
   (0.1ms)  SELECT MIN("albums"."release_year") AS min_id FROM "albums" 
 => 1965 
1.9.3p194 :002 > exit
MacBook:jukebox xyz$

sum

Mit der Methode sum lässt sich die Summe aller Einträge in einer bestimmten Spalte der Datenbank-Abfrage berechnen.

Ziehen wir die Summe aller Veröffentlichungsjahre:

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.sum(:release_year)
   (3.2ms)  SELECT SUM("albums"."release_year") AS sum_id FROM "albums" 
 => 19685 
1.9.3p194 :002 > exit
MacBook:jukebox xyz$

SQL EXPLAIN

Die meisten SQL-Datenbanken können mit dem Befehl EXPLAIN detaillierte Informationen zu einer SQL-Abfrage liefern. Dies macht bei unserer Mini-Applikation wenig Sinn, aber wenn Sie einmal mit einer großen Datenbank arbeiten, dann ist EXPLAIN eine gute Debugging-Methode, um z. B. herauszufinden, an welcher Stelle ein Index gesetzt werden sollte. SQL EXPLAIN kann mit der explain-Methode abgerufen werden (mit einem puts kann man sich die Ausgabe schöner anzeigen lassen):

MacBook:jukebox xyz$ rails console
Loading development environment (Rails 3.2.3)
1.9.3p194 :001 > Album.where(:release_year => 1960..1969)  Album Load (0.2ms)  SELECT "albums".* FROM "albums" WHERE ("albums"."release_year" BETWEEN 1960 AND 1969)
 => [#<Album id: 1, name: "Sgt. Pepper's Lonely Hearts Club Band", release_year: 1967, created_at: "2012-04-30 14:51:03", updated_at: "2012-04-30 14:51:03">, #<Album id: 2, name: "Pet Sounds", release_year: 1966, created_at: "2012-04-30 14:51:03", updated_at: "2012-04-30 14:51:03">, #<Album id: 3, name: "Revolver", release_year: 1966, created_at: "2012-04-30 14:51:03", updated_at: "2012-04-30 14:51:03">, #<Album id: 4, name: "Highway 61 Revisited", release_year: 1965, created_at: "2012-04-30 14:51:03", updated_at: "2012-04-30 14:51:03">, #<Album id: 5, name: "Rubber Soul", release_year: 1965, created_at: "2012-04-30 14:51:03", updated_at: "2012-04-30 14:51:03">, #<Album id: 9, name: "Blonde on Blonde", release_year: 1966, created_at: "2012-04-30 14:51:03", updated_at: "2012-04-30 14:51:03">, #<Album id: 10, name: "The Beatles", release_year: 1968, created_at: "2012-04-30 14:51:03", updated_at: "2012-04-30 14:51:03">] 
1.9.3p194 :002 > Album.where(:release_year => 1960..1969).explain
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE ("albums"."release_year" BETWEEN 1960 AND 1969)
  EXPLAIN (0.1ms)  EXPLAIN QUERY PLAN SELECT "albums".* FROM "albums" WHERE ("albums"."release_year" BETWEEN 1960 AND 1969)
 => "EXPLAIN for: SELECT \"albums\".* FROM \"albums\"  WHERE (\"albums\".\"release_year\" BETWEEN 1960 AND 1969)\n0|0|0|SCAN TABLE albums (~500000 rows)\n" 
1.9.3p194 :003 > puts Album.where(:release_year => 1960..1969).explain
  Album Load (0.3ms)  SELECT "albums".* FROM "albums" WHERE ("albums"."release_year" BETWEEN 1960 AND 1969)
  EXPLAIN (0.1ms)  EXPLAIN QUERY PLAN SELECT "albums".* FROM "albums" WHERE ("albums"."release_year" BETWEEN 1960 AND 1969)
EXPLAIN for: SELECT "albums".* FROM "albums"  WHERE ("albums"."release_year" BETWEEN 1960 AND 1969)
0|0|0|SCAN TABLE albums (~500000 rows)
 => nil 
1.9.3p194 :004 > exit
MacBook:jukebox xyz$

^[24]Sie können eine Exception auch mit rescue abfangen, aber darauf gehe ich in diesem Anfängerbuch nicht weiter ein.

Autor

Stefan Wintermeyer

@wintermeyer folgen

Rails 5.1 Consulting und Schulung vom Autor:www.wintermeyer-consulting.de/rails/