Having relationships between tables in a relational database can significantly enhance data modeling and querying capabilities. But it can also lead to complex queries and difficulty maintaining data integrity.
In oRm, relationships aren’t full R6 objects like
Engine, TableModel, or Record,
but they are attributes of TableModels that get inherited by their
Records. This makes it possible to traverse linked tables using the
record you’re working with.
Getting Started
Let’s create Students and Classes tables
and populate them with some dummy data.
library(oRm)
engine <- Engine$new(
drv = RSQLite::SQLite(),
dbname = ":memory:",
persist = TRUE
)
Classes <- engine$model(
tablename = "classes",
id = Column('INTEGER', primary_key = TRUE),
subject = Column('TEXT'),
teacher_id = ForeignKey('INTEGER', references = 'teachers.id')
)
Classes$create_table(overwrite=T)
#> <TableModel>
#> Table: classes
#> Fields:
#> 🔑 id INTEGER
#> subject TEXT
#> teacher_id INTEGER
set.seed(100)
Students <- engine$model(
tablename = "students",
id = Column('INTEGER', primary_key = TRUE),
name = Column('TEXT', default = charlatan::ch_name),
class_id = ForeignKey('INTEGER', references = 'classes.id'),
grade = Column('NUMBER', default = \(x) round(rnorm(1, 85, 5)))
)
Students$create_table(overwrite=T)
#> <TableModel>
#> Table: students
#> Fields:
#> 🔑 id INTEGER
#> name TEXT
#> class_id INTEGER
#> grade NUMBER
for (i in 1:10) {
Classes$record(
id = i,
subject = ifelse(i %% 2 == 0, "Math", "Science"),
teacher_id = ifelse(i %% 2 == 0, 1, 2)
)$create()
}
Classes$read(.limit = 3)
#> [[1]]
#> <Record>: 'classes'
#> id: 1
#> subject: Science
#> teacher_id: 2
#>
#> [[2]]
#> <Record>: 'classes'
#> id: 2
#> subject: Math
#> teacher_id: 1
#>
#> [[3]]
#> <Record>: 'classes'
#> id: 3
#> subject: Science
#> teacher_id: 2
for (i in 1:100) {
Students$record(
id = i,
class_id = sample(1:10, 1)
)$create()
}
Students$read(.limit = 3)
#> [[1]]
#> <Record>: 'students'
#> name: Tonja Zulauf-Kemmer
#> grade: 86
#> id: 1
#> class_id: 10
#>
#> [[2]]
#> <Record>: 'students'
#> name: Helen O'Kon-Pfeffer
#> grade: 81
#> id: 2
#> class_id: 6
#>
#> [[3]]
#> <Record>: 'students'
#> name: Infant Wyman
#> grade: 88
#> id: 3
#> class_id: 7Okay, so in the old days, before oRm you might use dplyr
to view related fields between tables via joins. We’ll look at all the
students in class with id=1
class_tbl <- Classes$tbl()
student_tbl <- Students$tbl()
dplyr::left_join(class_tbl, student_tbl, by = c(id = "class_id")) |>
dplyr::filter(id == 1) |>
dplyr::collect()
#> # A tibble: 10 × 6
#> id subject teacher_id id.y name grade
#> <int> <chr> <int> <int> <chr> <int>
#> 1 1 Science 2 9 Ms. Celeste Spencer PhD 87
#> 2 1 Science 2 43 Debby Goldner 84
#> 3 1 Science 2 54 Mr. Drew Schuster MD 80
#> 4 1 Science 2 63 Gorge Predovic 85
#> 5 1 Science 2 64 Mr. Felipe Hudson 86
#> 6 1 Science 2 73 Theta Rau 85
#> 7 1 Science 2 74 Phebe Kuvalis-Ziemann 87
#> 8 1 Science 2 79 Korey Rice 88
#> 9 1 Science 2 90 Mayo Hickle 83
#> 10 1 Science 2 96 Clinton Collins MD 89That is a beutiful join that finds all students in class 1. Ready for
all kinds of analysis now. But what if you’re not trying to read and
analyze the data, but make a change? What if we need to apply a curve to
the grades in class 1? We can splice things dplyr style and
hope to get our joins right, or, we could rely on a previously described
relationship to get the necessary records and apply the curve.
define_relationship(
local_model = Classes,
local_key = 'id',
type = 'one_to_many',
related_model = Students,
related_key = 'class_id',
ref = 'students',
backref = 'class'
)
#> <TableModel>
#> Table: classes
#> Fields:
#> 🔑 id INTEGER
#> subject TEXT
#> teacher_id INTEGER The possible types are ‘one_to_many’, ‘one_to_one’, ‘many_to_many’, or ‘many_to_one’. The ref and backref arguments are used to define the relationship between the two models. You’ll use those values to call on related records. Let’s see that in action:
class1 = Classes$read(id == 1, mode='get')
class1_students = class1$relationship('students')
class1_students |> sapply(\(x) paste(
x$data$name, round(x$data$grade), sep = ': '))
#> [1] "Ms. Celeste Spencer PhD: 87" "Debby Goldner: 84"
#> [3] "Mr. Drew Schuster MD: 80" "Gorge Predovic: 85"
#> [5] "Mr. Felipe Hudson: 86" "Theta Rau: 85"
#> [7] "Phebe Kuvalis-Ziemann: 87" "Korey Rice: 88"
#> [9] "Mayo Hickle: 83" "Clinton Collins MD: 89"
# let's go ahead and apply that curve to the grades
for (student in class1_students) {
student$data$grade <- student$data$grade + 3
student$update()
}
class1_students |> sapply(\(x) paste(
x$data$name, round(x$data$grade), sep = ': '))
#> [1] "Ms. Celeste Spencer PhD: 90" "Debby Goldner: 87"
#> [3] "Mr. Drew Schuster MD: 83" "Gorge Predovic: 88"
#> [5] "Mr. Felipe Hudson: 89" "Theta Rau: 88"
#> [7] "Phebe Kuvalis-Ziemann: 90" "Korey Rice: 91"
#> [9] "Mayo Hickle: 86" "Clinton Collins MD: 92"And if we look at that in reverse:
class1_students[[1]]$relationship('class')
#> <Record>: 'classes'
#> id: 1
#> subject: Science
#> teacher_id: 2We can also apply filtering to the related records:
class1$relationship('students', grade < 87)
#> [[1]]
#> <Record>: 'students'
#> name: Mr. Drew Schuster MD
#> grade: 83
#> id: 54
#> class_id: 1
#>
#> [[2]]
#> <Record>: 'students'
#> name: Mayo Hickle
#> grade: 86
#> id: 90
#> class_id: 1Nested Relationships
Let’s expand on the complexity by creating a new table for teachers. There are only a handful of teachers, and they may have multipe classes to take care of, so we’ll assign teachers to multiple classes.
set.seed(100)
Teachers <- engine$model(
tablename = "teachers",
id = Column('INTEGER', primary_key = TRUE),
name = Column('TEXT', default = charlatan::ch_name)
)
Teachers$create_table(overwrite=T)
#> <TableModel>
#> Table: teachers
#> Fields:
#> 🔑 id INTEGER
#> name TEXT
for (i in 1:3) {
Teachers$record(id = i)$create()
}
Teachers$read()[[1]]
#> <Record>: 'teachers'
#> name: Carrie Roob
#> id: 1
set.seed(100)
TeacherAssignments <- engine$model(
tablename = "teacher_assignments",
id = Column('INTEGER', primary_key = TRUE),
teacher_id = ForeignKey('INTEGER', references = 'teachers.id'),
class_id = ForeignKey('INTEGER', references = 'classes.id')
)
TeacherAssignments$create_table(overwrite=T)
#> <TableModel>
#> Table: teacher_assignments
#> Fields:
#> 🔑 id INTEGER
#> teacher_id INTEGER
#> class_id INTEGER
for (i in 1:length(Classes$read())) {
TeacherAssignments$record(
teacher_id = sample(1:3, 1),
class_id = i
)$create()
}
TeacherAssignments$read()[1]
#> [[1]]
#> <Record>: 'teacher_assignments'
#> id: 1
#> teacher_id: 2
#> class_id: 1We have two relationships we need to deine now: ‘one_to_many’ from Teachers to TeacherAssignments, and ‘many_to_one’ from TeacherAssignments to Classes.
define_relationship(
local_model = Teachers,
local_key = 'id',
type = 'one_to_many',
related_model = TeacherAssignments,
related_key = 'teacher_id',
ref = 'teacher_assignments',
backref = 'teacher'
)
#> <TableModel>
#> Table: teachers
#> Fields:
#> 🔑 id INTEGER
#> name TEXT
define_relationship(
local_model = TeacherAssignments,
local_key = 'class_id',
type ='one_to_one',
related_model = Classes,
related_key = 'id',
ref = 'class',
backref = 'teacher_assignment'
)
#> <TableModel>
#> Table: teacher_assignments
#> Fields:
#> 🔑 id INTEGER
#> teacher_id INTEGER
#> class_id INTEGER You can now traverse from Teachers ->
TeacherAssignments -> Classes and back
again. TO demonstrate that, we’ll make a deck of info cards for a
teacher showing each class and the students in each class.
teacher <- Teachers$read(id == 3, mode='get')
# teacher$relationship('teacher_assignments') |>
# lapply(\(x) x$relationship('class')) |>
# lapply(\(x) x$relationship('students'))
bslib::card(
bslib::card_title(teacher$data$name),
bslib::card_body(
teacher$relationship('teacher_assignments') |>
lapply(\(x) {
class = x$relationship('class')
bslib::card(max_height = '800px',
bslib::card_title(class$data$subject),
bslib::card_body(
class$relationship('students') |>
lapply(\(x) htmltools::tags$p(paste(x$data$name, round(x$data$grade))))
)
)
})
)
)Theophile Zemlak
Math
Renada Corwin DDS 92
Dion Orn 87
Metta Mohr 87
Hamp Koch II 83
Elvia Kuhlman 89
Shanae Hudson MD 86
Annabella Beatty MD 86
Math
Dr. Bob Heller 91
Kathey Dietrich 81
Tory Boyle 81
Drury Funk 79
Dr. Jagger Hoppe Sr. 88
Dr. Palmer Morissette PhD 87
Everett Medhurst DVM 86
Luis Kub 86
Kareem Schimmel 88
Mekhi Bednar 82
Arvilla Langworth-Stamm 91
Math
Kacie Torphy 86
Steve Prohaska-Konopelski 90
Perry Harris 94
Miss Danna Stroman DVM 88
Cecily Gleichner 76
Ms. Theda Dare 84
Mr. Marvin Zboncak 88
Ananias Walter 91
Dr. Montrell Renner 92
Jorja Grant 79
Murdock Nader 90