Cum pot găsi un tabel asociat cel mai vechi record de filtrare de unul dintre atributele?

Question 1

Am modelul Subscription care has_many Versions.

O Version are o status, plan_id și authorized_at data.

Orice modificări efectuate de la un Subscription provine dintr-o Version modificări actualizarea este părinte Subscription.

Scopul este de a găsi fiecare Abonament Version cu cele mai vechi authorized_at data WHERE la versions.plan_id este la fel ca subscriptions.plan_id (cu alte cuvinte am nevoie de autorizația dată de Version unde plan_id s-a schimbat la curent Subscriptionmodelului plan_id).

Acest lucru este interogarea am venit cu. Primesc o eroare în funcție agregată sintaxa:

syntax error at or near "MIN" LINE 3: MIN (authorized_at) from versions ^

interogare:

select subscriptions.id,
MIN (authorized_at) from versions
where versions.plan_id = subscriptions.plan_id
) as current_version
from subscriptions
join versions on subscriptions.id = versions.subscription_id
where versions.status = 'processed'

Am, de asemenea, nu sunt sigur dacă ar trebui să fiu gruparea versiuni de plan_id și apoi cules de la fiecare grup. Eu sunt un fel de pierdut.

Question 2

Puteți utiliza un laterală subinterogare care poate fi cel mai bine descris ca o buclă foreach în SQL. Ele sunt extrem de performant mod pentru a selecta coloane dintr-un singur corelate înregistrare sau chiar agregate dintr-un grup de înregistrări corelate.

Pentru fiecare rând din abonamente DB va selecta un singur rând de versiuni comandat de authorized_at:

SELECT "subscriptions".*,
       "latest_version"."authorized_at" AS current_version,
       "latest_version"."id" AS current_version_id -- could be very useful
FROM   "subscriptions" 
LATERAL
  (
     SELECT   "versions"."authorized_at", "versions"."id"
     FROM     "versions"
     WHERE    "versions"."subscription_id" = "subscriptions"."id" -- lateral reference
     AND      "versions"."plan_id" = "subscriptions"."plan_id"
     AND      "versions"."status" = 'processed'
     ORDER BY "versions"."authorized_at" ASC
     LIMIT 1
  ) latest_version ON TRUE

Crearea laterale se alătură în ActiveRecord se poate face fie cu SQL siruri de caractere sau Arel:

class Subscription < ApplicationRecord
  # Performs a lateral join and selects the 
  # authorized_at of the latest version 
  def self.with_current_version
    lateral = Version.arel_table.then do |v|
      v.project(
        v[:authorized_at],
        v[:id] # optional
      ).where(
        v[:subscription_id].eq(arel_table[:id])
          .and(v[:plan_id].eq(arel_table[:plan_id]) )
          .and(v[:status].eq('processed'))
      )
      .order(v[:authorized_at].asc)
      .take(1) # limit 1
      .lateral('latest_version ON TRUE')
    end
    lv = Arel::Table.new(:latest_version) # just a table alias
    select(
      *where(nil).arel.projections, # selects everything previously selected
      lv[:authorized_at].as("current_version"),
      lv[:id].as("current_version_id") # optional
    ).joins(lateral.to_sql)
  end
end

Dacă doriți doar să selectați id și current_version coloana ar trebui să ia în considerare utilizarea smulge în loc de a selecta modelele de baze de date care nu sunt corect hidratat.

Question 3

Puteți utiliza DISTINCT ON pentru a filtra rândurile, și să păstreze o singur pe abonament ... prima pe grupul potrivit ORDER BY clauză.

De exemplu:

select distinct on (s.id) s.id, v.authorized_at
from subscription s
join versions v on v.subscription_id = s.id and v.plan_id = s.plan_id
where v.status = 'processed'
order by s.id, v.authorized_at

Question 4

Codul de mai jos vă va oferi versions în cazul în care Versiune e plan_id este egal cu Abonament plan_id.

@versions = Version.joins("LEFT JOIN subscriptions ON subscriptions.plan_id = versions.plan_id")

Pentru a filtra înregistrările de Versiune e status

@versions = Version.joins("LEFT JOIN subscriptions ON subscriptions.plan_id = versions.plan_id").where(status: "processed")

Pentru a filtra înregistrările de Versiune e status și ordinea de authorized_at în ordine crescătoare.

@versions = Version.joins("LEFT JOIN subscriptions ON subscriptions.plan_id = versions.plan_id").where(status: "processed").order(:authorized_at)

Pentru a filtra înregistrările de Versiune e status și ordinea de authorized_at în coborâre ordine.

@versions = Version.joins("LEFT JOIN subscriptions ON subscriptions.plan_id = versions.plan_id").where(status: "processed").order(authorized_at: :desc)

Sper că acest lucru funcționează pentru tine!

max · Answer 1 · 2021-11-23T16:49:48

Puteți utiliza un laterală subinterogare care poate fi cel mai bine descris ca o buclă foreach în SQL. Ele sunt extrem de performant mod pentru a selecta coloane dintr-un singur corelate înregistrare sau chiar agregate dintr-un grup de înregistrări corelate.

Pentru fiecare rând din abonamente DB va selecta un singur rând de versiuni comandat de authorized_at:

SELECT "subscriptions".*,
       "latest_version"."authorized_at" AS current_version,
       "latest_version"."id" AS current_version_id -- could be very useful
FROM   "subscriptions" 
LATERAL
  (
     SELECT   "versions"."authorized_at", "versions"."id"
     FROM     "versions"
     WHERE    "versions"."subscription_id" = "subscriptions"."id" -- lateral reference
     AND      "versions"."plan_id" = "subscriptions"."plan_id"
     AND      "versions"."status" = 'processed'
     ORDER BY "versions"."authorized_at" ASC
     LIMIT 1
  ) latest_version ON TRUE

Crearea laterale se alătură în ActiveRecord se poate face fie cu SQL siruri de caractere sau Arel:

class Subscription < ApplicationRecord
  # Performs a lateral join and selects the 
  # authorized_at of the latest version 
  def self.with_current_version
    lateral = Version.arel_table.then do |v|
      v.project(
        v[:authorized_at],
        v[:id] # optional
      ).where(
        v[:subscription_id].eq(arel_table[:id])
          .and(v[:plan_id].eq(arel_table[:plan_id]) )
          .and(v[:status].eq('processed'))
      )
      .order(v[:authorized_at].asc)
      .take(1) # limit 1
      .lateral('latest_version ON TRUE')
    end
    lv = Arel::Table.new(:latest_version) # just a table alias
    select(
      *where(nil).arel.projections, # selects everything previously selected
      lv[:authorized_at].as("current_version"),
      lv[:id].as("current_version_id") # optional
    ).joins(lateral.to_sql)
  end
end

Dacă doriți doar să selectați id și current_version coloana ar trebui să ia în considerare utilizarea smulge în loc de a selecta modelele de baze de date care nu sunt corect hidratat.

The Impaler · Answer 2 · 2021-11-23T16:40:51

Puteți utiliza DISTINCT ON pentru a filtra rândurile, și să păstreze o singur pe abonament ... prima pe grupul potrivit ORDER BY clauză.

De exemplu:

select distinct on (s.id) s.id, v.authorized_at
from subscription s
join versions v on v.subscription_id = s.id and v.plan_id = s.plan_id
where v.status = 'processed'
order by s.id, v.authorized_at

Multumesc. Interogarea pare să funcționeze în consola, dar când am încerca să-l folosească în vedere ca un model de aplicare ajung ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR: syntax error at or near "WHERE" LINE 6: ...s.id, v.authorized_at WHERE "sub...
@pinkfloyd90 eu nu sunt expert în ActiveRecord, dar poate că nu merge pentru că punctul de vedere este, probabil, nu poate fi actualizat.

Nitin Srivastava · Answer 3 · 2021-11-23T14:33:48

Codul de mai jos vă va oferi versions în cazul în care Versiune e plan_id este egal cu Abonament plan_id.

@versions = Version.joins("LEFT JOIN subscriptions ON subscriptions.plan_id = versions.plan_id")

Pentru a filtra înregistrările de Versiune e status

@versions = Version.joins("LEFT JOIN subscriptions ON subscriptions.plan_id = versions.plan_id").where(status: "processed")

Pentru a filtra înregistrările de Versiune e status și ordinea de authorized_at în ordine crescătoare.

@versions = Version.joins("LEFT JOIN subscriptions ON subscriptions.plan_id = versions.plan_id").where(status: "processed").order(:authorized_at)

Pentru a filtra înregistrările de Versiune e status și ordinea de authorized_at în coborâre ordine.

@versions = Version.joins("LEFT JOIN subscriptions ON subscriptions.plan_id = versions.plan_id").where(status: "processed").order(authorized_at: :desc)

Sper că acest lucru funcționează pentru tine!

Cred că ai nevoie să se alăture pe subscriptions.id = versions.subscription_id sau vei primi versiuni din care rezultă că nu aparțin acelui abonament. Ai, de asemenea, nici nu am atins miezul problemei, care este selectarea unui agregat.

Cum pot găsi un tabel asociat cel mai vechi record de filtrare de unul dintre atributele?

Problema

Cel mai bun răspuns

În alte limbi

Această pagină este în alte limbi

Popular în această categorie

Întrebări frecvente în această categorie