Jak mohu najít přidružené tabulka nejstarší záznam filtrování podle jednoho z atributů?

Question 1

Mám model Subscription což has_many Versions.

A Version má status, plan_id a authorized_at datum.

Jakékoli změny provedené na Subscription pochází z Version změny aktualizace je rodič Subscription.

Cílem je najít každého Odběru Version s nejstarší authorized_at datum WHERE na versions.plan_id je to stejné jako subscriptions.plan_id (jinými slovy potřebuji povolení datum Version kde plan_id změní na aktuální Subscription's plan_id).

Toto je dotaz, jsem vymyslel. Dostávám chybu v agregační funkce syntaxe:

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

dotaz:

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'

Já také nejsem jistý, jestli bych měl být seskupení verze plan_id a pak vybírá z každé skupiny. Jsem trochu ztratil.

Question 2

Můžete použít boční poddotaz , který lze nejlépe popsat jako foreach smyčky v SQL. Oni jsou extrémně výkonný způsob, jak vybrat sloupce z jednoho koreluje záznam, nebo dokonce i agregáty ze skupiny souvisejících záznamů.

Pro každý řádek v předplatné DB vybere jeden řádek z verze nařídil 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

Vytvoření boční připojí v ActiveRecord může být provedeno buď s SQL řetězce nebo 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

Pokud si jen chcete vybrat id a current_version sloupec měli byste zvážit použití trhat namísto výběru databáze modelů, které nejsou správně hydratované.

Question 3

Můžete použít DISTINCT ON odfiltrovat řádky, a udržet jeden za předplatné, první na skupiny podle ORDER BY doložka.

Například:

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

Níže uvedený kód vám versions kde Verze je plan_id je rovna Odběru plan_id.

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

Filtrovat záznamy podle Verze status

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

Filtrovat záznamy podle Verze status a order by authorized_at ve vzestupném pořadí.

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

Filtrovat záznamy podle Verze status a order by authorized_at v decending pořadí.

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

Doufám, že to funguje pro vás!

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

Můžete použít boční poddotaz , který lze nejlépe popsat jako foreach smyčky v SQL. Oni jsou extrémně výkonný způsob, jak vybrat sloupce z jednoho koreluje záznam, nebo dokonce i agregáty ze skupiny souvisejících záznamů.

Pro každý řádek v předplatné DB vybere jeden řádek z verze nařídil 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

Vytvoření boční připojí v ActiveRecord může být provedeno buď s SQL řetězce nebo 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

Pokud si jen chcete vybrat id a current_version sloupec měli byste zvážit použití trhat namísto výběru databáze modelů, které nejsou správně hydratované.

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

Můžete použít DISTINCT ON odfiltrovat řádky, a udržet jeden za předplatné, první na skupiny podle ORDER BY doložka.

Například:

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

Děkuji. Dotaz, zdá se, práce v konzoli, ale když jsem se snažit, aby jej použít v moje názory jako model rozsah dostanu ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR: syntax error at or near "WHERE" LINE 6: ...s.id, v.authorized_at WHERE "sub...
@pinkfloyd90 nejsem žádný odborník na ActiveRecord, ale možná to nebude fungovat, protože ten výhled je pravděpodobně není aktualizovatelný.

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

Níže uvedený kód vám versions kde Verze je plan_id je rovna Odběru plan_id.

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

Filtrovat záznamy podle Verze status

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

Filtrovat záznamy podle Verze status a order by authorized_at ve vzestupném pořadí.

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

Filtrovat záznamy podle Verze status a order by authorized_at v decending pořadí.

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

Doufám, že to funguje pro vás!

Myslím si, že je třeba se připojit na subscriptions.id = versions.subscription_id nebo dostanete verzí v tom, že výsledek, který nepatří do tohoto předplatného. Také jste se nedotkl ani na základní otázky, které je výběr kameniva.

Jak mohu najít přidružené tabulka nejstarší záznam filtrování podle jednoho z atributů?

Otázka

Nejlepší odpověď

V jiných jazycích

Tato stránka je v jiných jazycích

Populární v této kategorii

Oblíbené položky v této kategorii