Forum: Rails Germany Verständnisfrage: Nested SQL- queries mit ActiveRecord

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

On Friday 29 May 2009, mailinglists_inbox@arcor.de 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

[*] http://www.schuerig.de/michael/pres/kreative-assoziationen/
--
Michael Schuerig
mailto:michael@schuerig.de
http://www.schuerig.de/michael/