Jak mogę znaleźć tabelę połączoną filtrujący najstarsze nagrania po jednym z jej atrybutów?

Question 1

Mam model Subscription który has_many Versions.

A Version ma status, plan_id i authorized_at Data.

Wszelkie zmiany wprowadzone w Subscription pochodzi od Version zmiany aktualizujący jego macierzysty Subscription.

Celem jest, aby znaleźć każdą subskrypcję Version z najstarszy authorized_at Data WHERE to versions.plan_id to to samo, co i subscriptions.plan_id (innymi słowy, potrzebuję data autoryzacji Version Gdzie plan_id zmieniony na aktualny Subscription'z plan_id).

To jest pytanie, które zadałem. Pojawia się błąd w składni funkcja agregująca:

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

zapytanie:

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'

Ja również nie jestem pewien, czy należy grupować w wersji na plan_id a następnie wybieramy z każdej grupy. Chyba się zgubił.

Question 2

Można użyć bocznej podzapytanie, który najlepiej można opisać jako pętli foreach w SQL. Są one niezwykle skutecznym sposobem wyboru kolumn z jednej коррелированной nagrywania lub nawet jednostek z grupy powiązanych rekordów.

Dla każdego wiersza w subskrypcji bazie wybierze jedną linię z wersji zamówionych 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

Tworzenie bocznych połączeń w ActiveRecord może być wykonane albo z pomocą wierszy SQL, albo za pomocą 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

Jeśli chcesz po prostu wybrać id i current_version kolumnę należy rozważyć możliwość wykorzystania pluck zamiast wyboru modeli baz danych, które są słabo nawilżone.

Question 3

Można użyć DISTINCT ON aby filtrować wiersze i zapisz jeden wiersz na abonament-pierwszą na grupy zgodnie z ORDER BY pkt.

Na przykład:

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

Poniższy kod daje versions gdzie wersja plan_id mimo kosztów subskrypcji plan_id.

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

Do filtrowania rekordów w wersji status

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

Do filtrowania rekordów w wersji status i zamówić authorized_at w kolejności rosnącej.

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

Do filtrowania rekordów w wersji status i zamówić authorized_at w kolejności malejącej.

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

Mam nadzieję, że to zadziała dla ciebie!

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

Można użyć bocznej podzapytanie, który najlepiej można opisać jako pętli foreach w SQL. Są one niezwykle skutecznym sposobem wyboru kolumn z jednej коррелированной nagrywania lub nawet jednostek z grupy powiązanych rekordów.

Dla każdego wiersza w subskrypcji bazie wybierze jedną linię z wersji zamówionych 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

Tworzenie bocznych połączeń w ActiveRecord może być wykonane albo z pomocą wierszy SQL, albo za pomocą 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

Jeśli chcesz po prostu wybrać id i current_version kolumnę należy rozważyć możliwość wykorzystania pluck zamiast wyboru modeli baz danych, które są słabo nawilżone.

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

Można użyć DISTINCT ON aby filtrować wiersze i zapisz jeden wiersz na abonament-pierwszą na grupy zgodnie z ORDER BY pkt.

Na przykład:

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

Dziękuję. Wniosek wydaje się, działa w konsoli, ale gdy próbuję go używać w swoich widokach jako obszar modelu, dostaję ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR: syntax error at or near "WHERE" LINE 6: ...s.id, v.authorized_at WHERE "sub...
@pinkfloyd90 nie Jestem ekspertem w ActiveRecord, ale może to nie działa, bo widok, prawdopodobnie, nie można zaktualizować.

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

Poniższy kod daje versions gdzie wersja plan_id mimo kosztów subskrypcji plan_id.

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

Do filtrowania rekordów w wersji status

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

Do filtrowania rekordów w wersji status i zamówić authorized_at w kolejności rosnącej.

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

Do filtrowania rekordów w wersji status i zamówić authorized_at w kolejności malejącej.

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

Mam nadzieję, że to zadziała dla ciebie!

Myślę, że musisz dołączyć subscriptions.id = versions.subscription_id lub w wyniku otrzymasz wersji, które nie odnoszą się do tej subskrypcji. Możesz także nawet nie dotknęły istotą problemu, który polega na wyborze populacji.

Jak mogę znaleźć tabelę połączoną filtrujący najstarsze nagrania po jednym z jej atrybutów?

Pytanie

Najlepsza odpowiedź

W innych językach

Ta strona jest w innych językach

Popularne w tej kategorii

Najczęściej zadawane pytania w tej kategorii