英文:
How to join a class with self column of it which is of jsonb type
问题
我有一个名为'School'的类，具有以下架构
学校表中的“学生”列是jsonb类型。目前我的表如下所示 -
[学校表](https://i.stack.imgur.com/qj2lN.png)
现在，我希望以以下形式执行连接 -
[期望的结果](https://i.stack.imgur.com/XDpt2.png)
我可以使用Snowflake中的flatten函数来实现这一点 -
`select school.*, student.value from school, table(flatten(students)) as student`
如何在psql或Rails中的Active Record中实现这一点？
我尝试了不同的交叉连接，但没有成功。我已经尝试了5个小时，但仍然面临此问题。
英文:
I have a class named 'School' with the following schema
table "school" do |t|
t.uuid "school_name"
t.string "city"
t.jsonb "students"
end
student column of the above table is of jsonb type. Currently my table looks like this -
Now, I want to perform a join in such way that I have the records in following form -
I can achieve this using flatten function in snowflake -
select school.*, student.value from school, table(flatten(students)) as student
How can I do this in psql or Active record in rails
I tried different cross joins but it didn't work. I am trying since 5 hrs to get desired result but I am still facing this issue.
答案1
得分: 0
你的表设计应该更改以更好地映射现实世界的条件。一个城市可能有很多学校，而一个给定的学校可能有很多学生。因此，适当的关系是：
class City < ApplicationRecord
# 有列: id, name
has_many :schools
end
class School < ApplicationRecord
# 有列: id, city_id, name
belongs_to :city
has_many :students
end
class Student < ApplicationRecord
# 有列: id, school_id, name, date_of_birth
belongs_to :school
end
然后，生成所需结果的查询是：
# student.rb
def self.list_all
select("students.name, students.date_of_birth, schools.name, schools.id, city.name").
joins(school: :city)
end
顺便说一下，存储学生的年龄没有意义，因为它随时间而变化。如果您希望在视图中显示年龄，请从出生日期计算它。
英文:
Your table design should be changed in order to better map the real-world conditions. There may be many schools in a city, and a given school may have many students. So the appropriate relationships are:
class City < ApplicationRecord
# has columns: id, name
has_many :schools
end
class School < ApplicationRecord
# has columns id, city_id, name
belongs_to :city
has_many :students
end
class Student < ApplicationRecord
# has columns id, school_id, name, date_of_birth
belongs_to :school
end
Then the query that produces the desired result is:
# student.rb
def self.list_all
select("students.name, students.date_of_birth, schools.name, schools.id, city.name").
joins(school: :city)
end
By the way, it doesn't make sense to store a student's age, b/c it changes with time, if you want that in your view, calculate it from date_of_birth.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论