Rails – Eindeutig nach einer Verbindung

Ich verwende Rails 4.2 mit PostgreSQL. Ich habe ein Product und ein Purchase mit Product has many Purchases . Ich möchte die verschiedenen kürzlich erworbenen Produkte finden. Anfangs habe ich versucht:

 Product.joins(:purchases) .select("DISTINCT products.*, purchases.updated_at") #postgresql requires order column in select .order("purchases.updated_at DESC") 

Dies führt jedoch zu Duplikaten, da versucht wird, alle Tupel zu finden, bei denen das Paar ( product.id und product.id ) einen eindeutigen Wert hat. Ich möchte jedoch nur die Produkte mit eindeutiger id nach dem Beitritt auswählen. Wenn eine Produkt-ID in der Verknüpfung mehrfach angezeigt wird, wählen Sie nur die erste aus. Also habe ich es auch versucht:

 Product.joins(:purchases) .select("DISTINCT ON (product.id) purchases.updated_at, products.*") .order("product.id, purchases.updated_at") #postgres requires that DISTINCT ON must match the leftmost order by clause 

Dies funktioniert nicht, weil ich product.id in der order Klausel angeben order , weil diese Einschränkung eine unerwartete Reihenfolge product.id .

Was ist der path, um dies zu erreichen?

Solutions Collecting From Web of "Rails – Eindeutig nach einer Verbindung"

Verwenden Sie eine Unterabfrage und fügen Sie eine andere ORDER BY Klausel in die äußere SELECT statement ein:

 SELECT * FROM ( SELECT DISTINCT ON (pr.id) pu.updated_at, pr.* FROM Product pr JOIN Purchases pu ON pu.product_id = pr.id -- guessing ORDER BY pr.id, pu.updated_at DESC NULLS LAST ) sub ORDER BY updated_at DESC NULLS LAST; 

Details für DISTINCT ON :

  • Wählen Sie die erste Zeile in jeder Gruppe GROUP BY?

Oder eine andere Abfragetechnik:

  • Optimieren Sie die GROUP BY-Abfrage, um den neuesten datasatz pro Benutzer abzurufen

Aber wenn alles, was Sie von updated_at brauchen, ist updated_at , können Sie dies billiger mit einem einfachen Aggregat in einer Unterabfrage bekommen, bevor Sie join:

 SELECT * FROM Product pr JOIN ( SELECT product_id, max(updated_at) AS updated_at FROM Purchases GROUP BY 1 ) pu ON pu.product_id = pr.id -- guessing ORDER BY pu.updated_at DESC NULLS LAST; 

Über NULLS LAST :

  • PostgreSQL nach date sortieren asc, null zuerst?

Oder noch einfacher, aber nicht so schnell beim Abrufen aller Zeilen:

 SELECT pr.*, max(updated_at) AS updated_at FROM Product pr JOIN Purchases pu ON pu.product_id = pr.id GROUP BY pr.id -- must be primary key ORDER BY 2 DESC NULLS LAST; 

Product.id muss als Primärschlüssel definiert werden, damit dies funktioniert. Einzelheiten:

  • PostgreSQL – GROUP BY-Klausel
  • Geben Sie eine gruppierte list mit Vorkommen mithilfe von Rails und PostgreSQL zurück

Wenn Sie nur eine kleine Auswahl pr.id (mit einer WHERE Klausel, die beispielsweise nur auf eine oder wenige pr.id ), ist dies schneller.

Um auf der Antwort von erwin-brandstetter aufzubuild, können Sie dies mit ActiveRecord tun (sollte zumindest nah sein):

 Product .select('*') .joins('INNER JOIN (SELECT product_id, max(updated_at) AS updated_at FROM Purchases GROUP BY 1) pu ON pu.product_id = pr.id') .order('pu.updated_at DESC NULLS LAST') 

Auf der Antwort von @ErwinBrandstetter aufbuildd habe ich endlich den richtigen path gefunden. Die Abfrage, um verschiedene kürzlich getätigte Käufe zu finden, ist

 SELECT * FROM ( SELECT DISTINCT ON (pr.id) pu.updated_at, pr.* FROM Product pr JOIN Purchases pu ON pu.product_id = pr.id ) sub ORDER BY updated_at DESC NULLS LAST; 

Der order_by wird in der Unterabfrage nicht benötigt, da wir in der äußeren Abfrage sowieso bestellen.

Die Art, dies zu tun, ist –

 inner_query = Product.joins(:purchases) .select("DISTINCT ON (products.id) products.*, purchases.updated_at as date") #This selects all the unique purchased products. result = Product.from("(#{inner_query.to_sql}) as unique_purchases") .select("unique_purchases.*").order("unique_purchases.date DESC") 

Der zweite (und bessere) path, wie von @ErwinBrandstetter vorgeschlagen, ist dies

 SELECT * FROM Product pr JOIN ( SELECT product_id, max(updated_at) AS updated_at FROM Purchases GROUP BY 1 ) pu ON pu.product_id = pr.id ORDER BY pu.updated_at DESC NULLS LAST; 

Das kann in Rails wie beschrieben werden

 join_query = Purchase.select("product_id, max(updated_at) as date") .group(1) #This selects most recent date for all purchased products result = Product.joins("INNER JOIN (#{join_query.to_sql}) as unique_purchases ON products.id = unique_purchases.product_id") .order("unique_purchases.date") 

Ich bin damit gelandet –

 Product.joins(:purchases) .select("DISTINCT ON (products.id) products.*, purchases.updated_at as date") .sort_by(&:date) .reverse 

Ich suche immer noch nach einem besseren path, dies zu tun.

Versuchen Sie Folgendes:

 Product.joins(:purchases) .select("DISTINCT ON (products_id) purchases.product_id, purchases.updated_at, products.*") .order("product_id, purchases.updated_at") #postgres requires that DISTINCT ON must match the leftmost order by clause