mardi 14 mars 2017

SQL + Rails - How to query the same column in 2 tables in hierarchy, when the child's value is NULL in the database?

class Baby
  belongs_to :child
  attr :is_public
  scope :public, includes(:child).merge(Child.public).where('babies.is_public IS TRUE')
end

class Child
  belongs_to :parent
  attr :is_public

  scope: public, where ???
  def is_public; read_attribute(:is_public).blank? ? self.parent.is_public : super(); end
end

class Parent
  has_many :children
  attr :is_public
end

situation: child.is_public is NULL in the database and the parent.is_public is true in the database

Issues:

  1. Child.public should give the correct result based on inheritance
  2. same column query - .merge(Child.public)

Issue 1: I think it would work with an sql if statement or case statement, so I already tried some things but no luck yet:

Child.includes(:parent).select("CASE WHEN children.is_public IS NOT NULL AND children.is_public IS TRUE THEN TRUE WHEN children.is_public IS NULL AND parent.is_public IS TRUE THEN TRUE END")

I think I am not the sql IF and CASE statements the way they were intended, I am still looking for the answer, but your help would be very much appreciated :)

Aucun commentaire:

Enregistrer un commentaire