Como posso encontrar uma tabela associada registro mais antigo de filtragem por um de seus atributos?

Question 1

Eu tenho o modelo Subscription que has_many Versions.

Um Version tem um status, plan_id e authorized_at a data.

Quaisquer alterações feitas a uma Subscription vem de uma Version modificações, atualizando-o pai da Subscription.

O objetivo é encontrar cada Inscrição Version com a mais antiga authorized_at data WHERE o versions.plan_id é o mesmo que o subscriptions.plan_id (em outras palavras, eu preciso de autorização data do Version onde o plan_id alterado para o atual Subscription's plan_id).

Esta é a consulta que eu vim acima com. Eu estou recebendo um erro na sintaxe de função de agregação:

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

consulta:

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'

Eu também não tenho certeza se eu deveria estar agrupar as versões plan_id e, em seguida, a escolha de cada grupo. Eu estou meio perdido.

Question 2

Você pode usar um lateral subconsulta que pode ser melhor descrito como um loop foreach em SQL. Eles são extremamente funcionais para selecionar colunas a partir de um único correlacionada registro ou mesmo agregados a partir de um grupo de registros relacionados.

Para cada linha em assinaturas a DB vai selecionar uma única linha de versões ordenar por 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

A criação de lateral ingressa no ActiveRecord pode ser feito com seqüências de caracteres SQL ou 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

Se você deseja apenas seleccionar o id e current_version a coluna você deve considerar o uso de arrancar em vez de selecionar modelos de banco de dados que não são devidamente hidratado.

Question 3

Você pode usar DISTINCT ON para filtrar linhas, e manter uma única conta por assinatura-o primeiro, por grupo, de acordo com o ORDER BY cláusula.

Por exemplo:

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

Código abaixo vai lhe dar o versions onde a Versão do plan_id é igual a Inscrição de plan_id.

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

Para filtrar registros da Versão status

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

Para filtrar registros da Versão status e a ordem por authorized_at em ordem crescente.

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

Para filtrar registros da Versão status e a ordem por authorized_at em ordem decending.

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

Espero que isso funciona para você!

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

Você pode usar um lateral subconsulta que pode ser melhor descrito como um loop foreach em SQL. Eles são extremamente funcionais para selecionar colunas a partir de um único correlacionada registro ou mesmo agregados a partir de um grupo de registros relacionados.

Para cada linha em assinaturas a DB vai selecionar uma única linha de versões ordenar por 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

A criação de lateral ingressa no ActiveRecord pode ser feito com seqüências de caracteres SQL ou 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

Se você deseja apenas seleccionar o id e current_version a coluna você deve considerar o uso de arrancar em vez de selecionar modelos de banco de dados que não são devidamente hidratado.

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

Você pode usar DISTINCT ON para filtrar linhas, e manter uma única conta por assinatura-o primeiro, por grupo, de acordo com o ORDER BY cláusula.

Por exemplo:

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

Obrigado. A consulta parece funcionar no console, mas quando eu tento usá-lo em meu ponto de vista como um modelo de escopo de eu chegar ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR: syntax error at or near "WHERE" LINE 6: ...s.id, v.authorized_at WHERE "sub...
@pinkfloyd90 eu não sou nenhum especialista no ActiveRecord, mas talvez isso não funciona, porque a visão é, provavelmente, não atualizável.

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

Código abaixo vai lhe dar o versions onde a Versão do plan_id é igual a Inscrição de plan_id.

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

Para filtrar registros da Versão status

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

Para filtrar registros da Versão status e a ordem por authorized_at em ordem crescente.

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

Para filtrar registros da Versão status e a ordem por authorized_at em ordem decending.

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

Espero que isso funciona para você!

Eu acho que você precisa para ingressar no subscriptions.id = versions.subscription_id ou você terá versões em que o resultado que não pertencem a essa subscrição. Você também ainda nem tocou no cerne da questão, que é a seleção de uma função de agregação.

Como posso encontrar uma tabela associada registro mais antigo de filtragem por um de seus atributos?

Pergunta

Melhor resposta

Em outros idiomas

Esta página está em outros idiomas

Popular nesta categoria

Perguntas populares nesta categoria