My CRUD application's data models:
What is the proper way to set up the FilmGroup model? It has a one-to-many relationship with Film and a one-to-one relationship with FilmOrder.
My models for the other entities:
type Film struct {
ID int `db:"id"`
FilmName string `db:"film_name"`
FilmFormat *string `db:"film_format"`
FilmProcessingType *string `db:"film_processing_type"`
ScansFolder *string `db:"scans_folder"`
RollLabel *string `db:"roll_label"`
Comments *string `db:"comments"`
DateShot *string `db:"date_shot"`
ScanCompleted *bool `db:"scan_completed"`
CutAndSleeved *bool `db:"cut_and_sleeved"`
}
type FilmOrder struct {
ID int `db:"id"`
FilmLabName string `db:"film_lab_name"`
ToLabTracking *string `db:"to_lab_tracking"`
FromLabTracking *string `db:"from_lab_tracking"`
DateFilmReceivedByLab *time.Time `db:"date_film_received_by_lab"`
DateFilmReceivedByMe *time.Time `db:"date_film_received_by_me"`
DateOrdered *time.Time `db:"date_ordered"`
DateMailedToLab *time.Time `db:"date_mailed_to_lab"`
ScansRequested *bool `db:"scans_requested"`
ScansReceived *bool `db:"scans_received"`
DateScansReceivedByMe *time.Time `db:"date_scans_received_by_me"`
}
And for FilmGroup:
type FilmGroup struct {
ID int `db:"id"`
Films []Film `db:"films"`
FilmOrderID *int `db:"film_order_id"`
FilmOrder *FilmOrder
Status string `db:"status"`
Comments *string `db:"comments"`
}
I need both FilmOrderID and FilmOrder to associate the FilmOrderID with this model, but also want easy access to the associated FilmOrder since I render all data to the user.
In my AddFilmGroup method I pass just the FilmOrderID:
func AddFilmGroup(db *sql.DB, filmGroup models.FilmGroup) error {
insertGroupQuery := `
INSERT INTO FilmGroup (status, comments, film_order_id)
VALUES (?, ?, ?);`
result, err := db.Exec(insertGroupQuery, filmGroup.Status, filmGroup.Comments, filmGroup.FilmOrderID)
if err != nil {
return fmt.Errorf("failed to insert FilmGroup: %w", err)
}
groupID, err := result.LastInsertId()
if err != nil {
return fmt.Errorf("failed to retrieve FilmGroup ID: %w", err)
}
for _, film := range filmGroup.Films {
insertFilmGroupFilmQuery := `
INSERT INTO FilmGroup_Film (film_group_id, film_id)
VALUES (?, ?);`
_, err := db.Exec(insertFilmGroupFilmQuery, groupID, film.ID)
if err != nil {
return fmt.Errorf("failed to associate Film %d with FilmGroup %d: %w", film.ID, groupID, err)
}
}
log.Printf("FilmGroup '%d' added successfully.\n", groupID)
return nil
}
It complicates fetching:
func GetFilmGroupWithDetails(db *sql.DB, groupID int) (models.FilmGroup, error) {
var filmGroup models.FilmGroup
query := `
SELECT id, status, comments, film_order_id
FROM FilmGroup
WHERE id = ?;`
err := db.QueryRow(query, groupID).Scan(&filmGroup.ID, &filmGroup.Status, &filmGroup.Comments, &filmGroup.FilmOrderID)
if err != nil {
if err == sql.ErrNoRows {
return filmGroup, fmt.Errorf("FilmGroup with ID %d not found", groupID)
}
return filmGroup, fmt.Errorf("failed to fetch FilmGroup: %w", err)
}
// Fetch the associated films
filmQuery := `
SELECT f.id, f.film_name, f.film_format, f.film_processing_type, f.scans_folder, f.roll_label, f.comments, f.date_shot, f.scan_completed, f.cut_and_sleeved
FROM Film f
JOIN FilmGroup_Film fgf ON f.id = fgf.film_id
WHERE fgf.film_group_id = ?;`
rows, err := db.Query(filmQuery, groupID)
if err != nil {
return filmGroup, fmt.Errorf("failed to fetch films for FilmGroup %d: %w", groupID, err)
}
defer rows.Close()
var films []models.Film
for rows.Next() {
var film models.Film
var filmFormat, filmProcessingType, scansFolder, rollLabel, comments, dateShot sql.NullString
var scanCompleted, cutAndSleeved sql.NullBool
if err := rows.Scan(&film.ID, &film.FilmName, &filmFormat, &filmProcessingType, &scansFolder, &rollLabel, &comments, &dateShot, &scanCompleted, &cutAndSleeved); err != nil {
return filmGroup, fmt.Errorf("failed to scan film row: %w", err)
}
// Handle nullable fields
// Redacted
films = append(films, film)
}
if err := rows.Err(); err != nil {
return filmGroup, fmt.Errorf("error iterating film rows: %w", err)
}
filmGroup.Films = films
var filmOrder models.FilmOrder
if filmGroup.FilmOrderID != nil {
filmOrderQuery := `
SELECT id, film_lab_name, to_lab_tracking, from_lab_tracking, date_film_received_by_lab, date_film_received_by_me, date_ordered, date_mailed_to_lab, scans_requested, scans_received, date_scans_received_by_me
FROM FilmOrder
WHERE id = ?;`
err = db.QueryRow(filmOrderQuery, *filmGroup.FilmOrderID).Scan(&filmOrder.ID, &filmOrder.FilmLabName, &filmOrder.ToLabTracking, &filmOrder.FromLabTracking, &filmOrder.DateFilmReceivedByLab, &filmOrder.DateFilmReceivedByMe, &filmOrder.DateOrdered, &filmOrder.DateMailedToLab, &filmOrder.ScansRequested, &filmOrder.ScansReceived, &filmOrder.DateScansReceivedByMe)
if err != nil {
if err == sql.ErrNoRows {
return filmGroup, fmt.Errorf("FilmOrder not found for FilmGroup %d", groupID)
}
return filmGroup, fmt.Errorf("failed to fetch FilmOrder: %w", err)
}
filmGroup.FilmOrder = &filmOrder
}
filmGroup.FilmOrder = &filmOrder
return filmGroup, nil
}
My database:
CREATE TABLE IF NOT EXISTS Film (
id INTEGER PRIMARY KEY AUTOINCREMENT,
film_name TEXT NOT NULL,
film_format TEXT,
film_processing_type TEXT,
scans_folder TEXT,
roll_label TEXT,
comments TEXT,
date_shot TEXT,
scan_completed BOOLEAN,
cut_and_sleeved BOOLEAN
);
CREATE TABLE IF NOT EXISTS FilmOrder (
id INTEGER PRIMARY KEY AUTOINCREMENT,
film_lab_name TEXT NOT NULL,
to_lab_tracking TEXT,
from_lab_tracking TEXT,
date_film_received_by_lab DATE,
date_film_received_by_me DATE,
date_ordered DATE,
date_mailed_to_lab DATE,
scans_requested BOOLEAN,
scans_received BOOLEAN,
date_scans_received_by_me DATE
);
CREATE TABLE IF NOT EXISTS FilmGroup (
id INTEGER PRIMARY KEY AUTOINCREMENT,
status TEXT,
comments TEXT,
film_order_id INTEGER,
FOREIGN KEY (film_order_id) REFERENCES FilmOrders (id)
);
// Junction table
CREATE TABLE IF NOT EXISTS FilmGroup_Film (
film_group_id INTEGER NOT NULL,
film_id INTEGER NOT NULL,
PRIMARY KEY (film_group_id, film_id),
FOREIGN KEY (film_group_id) REFERENCES FilmGroup (id),
FOREIGN KEY (film_id) REFERENCES Film (id)
);
How do I simplify the FilmGroup model so it does not need FilmOrderID and a reference to a FilmOrder object? How to map FilmOrder *FilmOrder to a database field? I want to use the same struct for the database model and to display the object.
