Verständnisfrage: Nested SQL- queries mit ActiveRecord

Community DE
1

Hallo,

leider habe ich ein Problem mit ActiveRecord.
Hier ein kurzes Beispiel:

competitors (id, name,score)
has_won_against(id, competitor1,competitor2,price)

Nun möchte ich gerne, die alle Wettbewerber Selektieren, die

  • Die mind 2x angetreten sind
  • Niemals verloren haben
  • und in ihrer Karriere schon mehr als 1000 verdient haben.

Ein SQL-Query dazu würde etwa so aussehen:
SELECT DISTINCT c.id,c.name,c.score FROM (
SELECT id,name,score,sum(h.price),count(h.id)
FROM (has_won_against as h) JOIN (competitors as c) on c.id =
h.competitor1
WHERE c.id not IN (SELECT competitors2 FROM competitors as c1)
GROUP_BY h.competitor1
HAVING sum(h.price) > 1000 AND cound(h.id) > 1
)

Diese Abfrage möchte ich nun gerne mit ActiveRecord umsetzen. Mein Problem
ist, dass ich nicht weiß, wie ich den inneren Query umsehen soll.
Da ein competitor an vielen hbtm-Beziehungen hängt, wird das SELECT und
der JOIN-Teil von ActiveRecord gebaut und die Beziehungen werden
über :include geladen.
Irgendwie müsste ich aber den alias-Namen des ersten competitors kennen, um
den 2. Select in den where-Teil bauen zu können.
Am liebsten wäre es mir jedoch, wenn ich mir über das konkrete SQL keine
Gedanken machen muss, da der ganze Query über Formularparameter gebaut wird.
Gehe ich dann sehr frei mit SQL um, laufe ich u.U. in Escape-Orgien und
halte Resultate, die der OR-Mapper ggf. gar nicht mehr in Objekte packen
kann.

Wie setze ich das am besten mit ActiveRecord um?

Danke,
Keep smiling
yanosz

2

On Friday 29 May 2009, [email protected] wrote:

Ein SQL-Query dazu würde etwa so aussehen:
SELECT DISTINCT c.id,c.name,c.score FROM (
SELECT id,name,score,sum(h.price),count(h.id)
FROM (has_won_against as h) JOIN (competitors as c) on c.id =
h.competitor1 WHERE c.id not IN (SELECT competitors2 FROM competitors
as c1) GROUP_BY h.competitor1
HAVING sum(h.price) > 1000 AND cound(h.id) > 1
)

Ich würde zur Klarheit folgende Änderung vornehmen:

matches(id, winner_id, loser_id, price)

Dann

class Competitor < ActiveRecord::Base
named_scope :winners, :select => %{
competitors.,
(SELECT COUNT() from matches
WHERE matches.winner_id = competitors.id) AS won_match_count,
(SELECT SUM(price) from matches
WHERE matches.winner_id = competitors.id) AS earning,
(SELECT COUNT(*) from matches
WHERE matches.loser_id = competitors.id) AS lost_match_count},
:conditions => %{
won_match_count > 2 AND
lost_match_count = 0 AND
earning > 1000}
end

Ich bin sicher, dass da noch Fehler drin sind und dass es besser geht.
PostgreSQL hat keine Schwierigkeiten mit Sub-SELECTs in der Selectliste,
ich weiß nicht, wie es bei MySQL aussieht.

Diese Abfrage möchte ich nun gerne mit ActiveRecord umsetzen. Mein
Problem ist, dass ich nicht weiß, wie ich den inneren Query umsehen
soll. Da ein competitor an vielen hbtm-Beziehungen hängt, wird das
SELECT und der JOIN-Teil von ActiveRecord gebaut und die Beziehungen
werden über :include geladen.

Da kann ich nicht ganz folgen. Wie dem auch sei, du darfst SQL
verwenden!

Wie setze ich das am besten mit ActiveRecord um?

Du könntest die schwierigen Teile in die Datenbank verschieben und
hinter Views verstecken[*]. Empfehlen würde ich aber eine Kombination
aus Counter Caches und Association Callbacks:

In einer Migration zu Competitor die Spalten won_matches_count,
lost_matches_count und earnings
hinzufügen.
class Competitor < ActiveRecord::Base
has_many :won_matches, :class_name => ‘Match’,
:foreign_key => :winner_id, :counter_cache => ‘won_matches_count’,
:after_add => :accumulate_earnings
has_many :lost_matches, :class_name => ‘Match’,
:foreign_key => :loser_id, :counter_cache => ‘lost_matches_count’

def accumulate_earnings(match)
self.earnings += match.price
end

named_scope :winners, :conditions =>
%{won_matches_count > 2 AND
lost_matches_count = 0 AND
earnings > 1000}
end

Wenn du weißt, dass niemand an den Models vorbei die Datenbank ändert,
die Caches also nicht inkonsistent werden können, dann ist das die
einfachste Lösung. Wenn diese Voraussetzung nicht erfüllt ist, nimm
Datenbank-Views.

Michael

[*] Kreative Assoziationen

Michael S.
mailto:[email protected]
http://www.schuerig.de/michael/