Possibly the best advice I personally can give you is to heed and take to heart any advice you can get from the very learned tobyink 🙇🏼♂️.
I apologize for taking so long to post this reply. As you can see I went rather far overboard in preparing a demonstration. (Actually I spent most time lost in the Wikipedia weeds trying to build a real-life data set, lol.) I have spent so much time on it because:
Of course Toby is right about what he says regarding designing a schema for longevity. It's also what you yourself proposed in your OP and again later in this thread. I guess I muddied the water by over-simplifying my demo for brevity. So let's do it again with a better schema, like the one Toby showed. And let's have even more more fun by adding a couple more layers of joining, just to see how DBIx handles it.
This demonstration shows how to produce the SQL queries that require the same joins that Toby showed above, as well as some more complex ones. It also shows various of the many ways how one might insert a record into a (relatively) complex schema like this.
use strict;
use warnings;
use utf8::all;
use feature 'say';
#---------------------------------------------------------------------
+#
# This section for demo setup only
# Create the database with the SQL schema below
my $sql = do { local $/, <DATA> };
system('mysql', '-e', $sql);
# Create the DBIx::Class schema classes
system('dbicdump', '-o', 'quiet=1', '-o', 'dump_directory=.',
'Art::Schema', 'dbi:mysql:database=Art');
# Use the schema classes
unshift @INC, '.';
eval 'use Art::Schema'; # normally loaded, um, normally
#---------------------------------------------------------------------
+#
# Initialize the schema as a DBIx schema object
my $db = Art::Schema->connect('DBI:mysql:database=Art', undef, undef,
+{
RaiseError => 1, mysql_enable_utf8 => 1,
});
# Create the "Mona Lisa" and all its related objects.
# Nothing is in the DB at this point (but no need to worry
# about getting the IDs to use for the FK columns)
$db->resultset('Painting')->create({
artwork => {
name => 'Mona Lisa',
year => 1503,
artist => {
name => 'Leonardo Da Vinci',
country => {
iso_code => 'IT',
name => 'Italy',
language => {
name => 'Eatalian',
},
},
},
},
genre => 'portrait',
medium => 'oil',
});
# Create another Da Vinci painting.
# (no need to worry about the fact that there is
# already an artist record for Da Vinci)
$db->resultset('Painting')->create({
artwork => {
name => 'Salvator Mundi',
year => 1500,
artist => {
name => 'Leonardo Da Vinci', # needed here
country => { # silently ignored here
iso_code => 'IT',
name => 'Italy',
language => {
name => 'Eatalian',
},
},
},
},
genre => 'history',
medium => 'oil',
});
# Create one more Da Vinci painting.
# (starting from the artist record)
my $artist = $db->resultset('Artist')->find({
name => 'Leonardo Da Vinci',
});
$artist->create_related('artworks', {
name => 'John the Baptist',
year => 1517,
})->create_related('painting', {
genre => 'history',
medium => 'oil',
});
# Create "Hamlet" and Shakespeare along with it.
# (new country and language also created)
my $hamlet = $db->resultset('Play')->create({
artwork => {
name => 'Hamlet',
year => 1599,
artist => {
name => 'William Shakespeare',
country => {
iso_code => 'GB-ENG',
name => 'England',
language => {
name => 'English',
},
},
},
},
genre => 'tragedy',
num_acts => 5,
});
# Create a couple more works by the Bard.
# (Using tortuous one-statement syntax here purely as a demonstration)
$hamlet->artwork->artist->create_related('artworks', {
name => 'As You Like It',
year => '1599',
})->create_related('play', {
genre => 'comedy',
num_acts => 5,
})->artwork->artist->create_related('artworks', {
name => 'Venus and Adonis',
year => '1593',
})->create_related('poem', {
meter => 'iamb',
rhyme => 'end',
});
# Create a new language and a couple of countries that use it.
$db->resultset('Language')->create({
name => 'Spanish',
countries => [{
name => 'Chile',
iso_code => 'CL',
}, {
name => 'Spain',
iso_code => 'ES',
}],
});
# Create some more countries and set the language they use.
# (language is created if it does not already exist)
$db->resultset('Country')->create({
name => $_->{name},
iso_code => $_->{code},
language => { name => $_->{lang} },
}) for (
{ name => 'Palestine', code => 'PS', lang => 'Arabic' },
{ name => 'Egypt', code => 'EG', lang => 'Arabic' },
{ name => 'Scotland', code => 'GB-SCT', lang => 'English' },
{ name => 'United States', code => 'US', lang => 'English' },
{ name => 'Malta', code => 'MT', lang => 'Maltese' },
);
# Some more paintings and painters ...
$db->resultset('Painting')->create($_) for ({
genre => 'portrait',
medium => 'oil',
artwork => {
name => 'Portrait of Baldassare Castiglione',
year => 1514,
artist => {
name => 'Raffaello Sanzio',
country => { name => 'Italy' },
},
},
}, {
genre => 'genre',
medium => 'oil',
artwork => {
name => 'Benefits Supervisor Sleeping',
year => 1995,
artist => {
name => 'Lucien Freud',
country => { name => 'England' },
},
},
}, {
genre => 'modern',
medium => 'acrylic',
artwork => {
name => 'A Bigger Splash',
year => 1967,
artist => {
name => 'David Hockney',
country => { name => 'England' },
},
},
}, {
genre => 'modern',
medium => 'oil',
artwork => {
name => 'Femme à la guitare',
year => 1917,
artist => {
name => 'María Gutiérrez Blanchard',
country => { name => 'Spain' },
},
},
});
# Create more artists ...
my @artists = (
['Ghassan Zaqtan' => 'PS'],
['Naguib Mahfouz' => 'EG'],
['Pablo Neruda' => 'CL'],
['Federico García Lorca' => 'ES'],
);
$db->resultset('Artist')->create({
name => $_->[0], country => { iso_code => $_->[1] },
}) for @artists;
# ... create some more poems
$db->resultset('Poem')->create($_) for ({
artwork => {
name => 'التلال ا&
+#1604;مالحة',
year => 1998,
artist => { name => 'Ghassan Zaqtan'},
},
}, {
artwork => {
name => '2000 ام الل',
year => 2003,
artist => { name => 'Ghassan Zaqtan'},
},
}, {
artwork => {
name => 'El Niño Mudo',
year => 1927,
artist => { name => 'Federico García Lorca'},
},
}, {
artwork => {
name => 'Sonnet 17',
year => 1959,
artist => { name => 'Pablo Neruda' },
},
}, {
artwork => {
name => 'Sonnet 17',
year => 1609,
artist => { name => 'William Shakespeare' },
},
meter => 'iamb',
});
# ... a couple of plays
$db->resultset('Play')->create($_) for ({
num_acts => 1,
artwork => {
name => 'تحت المظ&
+#1604;ة',
year => 1973,
artist => { name => 'Naguib Mahfouz'},
},
}, {
num_acts => 1,
artwork => {
name => 'El Maleficio de la Mariposa',
year => 1920,
artist => { name => 'Federico García Lorca'},
},
});
# Finally, some users and their countries ...
$db->resultset('User')->create($_) for (
{ name => '1nickt', country => { iso_code => 'GB-ENG' } },
{ name => 'discipulus', country => { iso_code => 'IT' } },
{ name => 'tobyink', country => { iso_code => 'GB-ENG' } },
{ name => 'bliako', country => { iso_code => 'MT' } },
{ name => 'marioroy', country => { iso_code => 'US' } },
{ name => 'marto', country => { iso_code => 'GB-SCT' } },
);
# ... and at last, their comments.
#
# Each user gets 17 comments, one for each artwork. If the user choose
+s
# to comment more than once on an artwork, one of the user's comments
# is consumed and the existing comment about the artwork is updated, a
+s
# a user can only have one comment stored on a given artwork.
my @comments = ('bad','meh','good');
my @artworks = $db->resultset('Artwork')->all;
for my $user ( $db->resultset('User')->all ) {
for (0 .. $#artworks) {
my $i = int rand($#artworks);
$artworks[ $i ]->update_or_create_related('comments', {
text => $comments[ int rand(3) ],
user_id => $user->user_id,
});
}
}
#---------------------------------------------------------------------
+#
## Complex queries against the database via DBIx relationsips
say "\nLIST OF PAINTINGS";
my $rs = $db->resultset('Painting');
while ( my $painting = $rs->next ) {
say sprintf('%s: "%s" (%s)',
$painting->artwork->artist->name,
$painting->artwork->name,
$painting->medium,
);
}
#---------------------------------------------------------------------
+#
say "\nLIST OF PLAYS";
$rs = $db->resultset('Play');
while ( my $play = $rs->next ) {
say sprintf('%s: "%s" (%s)',
$play->artwork->artist->name,
$play->artwork->name,
$play->genre,
);
}
#---------------------------------------------------------------------
+#
say "\nCOMMENTS ON PLAYS";
$rs = $db->resultset('Play');
while ( my $play = $rs->next ) {
say sprintf('About "%s", %s said "%s"',
$play->artwork->name,
$_->user->name,
$_->text,
) for $play->artwork->comments;
}
#---------------------------------------------------------------------
+#
say "\nART THAT BLIAKO LIKES";
# Starting here from the comments.
# Showing multiple joins.
$rs = $db->resultset('Comment')->search({
'user.name' => 'bliako',
'me.text' => 'good',
}, {
join => ['user', { artwork => 'artist' }],
});
while ( my $comment = $rs->next ) {
say sprintf('bliako likes: "%s" (%s)',
$comment->artwork->name,
$comment->artwork->artist->name,
);
}
#---------------------------------------------------------------------
+#
say "\nPAINTINGS THAT DISCIPULUS DOES NOT LOVE";
# Starting here from the paintings.
# Showing multiple and multi-level joins.
$rs = $db->resultset('Painting')->search({
'user.name' => 'discipulus',
'comments.text' => { '-in' => ['bad', 'meh'] },
}, {
join => { artwork => [ 'artist', { comments => 'user' } ] },
});
while ( my $painting = $rs->next ) {
say sprintf('discipulus does not love "%s" (%s)',
$painting->artwork->name,
$painting->artwork->artist->name,
);
}
#---------------------------------------------------------------------
+#
say "\nPOEMS NOT BY ENGLISH ARTISTS THAT TOBYINK DOESN'T HATE";
# Showing multiple multi-level joins; 'prefetch' needed here
$rs = $db->resultset('Poem')->search({
'user.name' => 'tobyink',
'comments.text' => { '!=' => 'bad' }, # DBIx handles this NOT
'country.name' => \'!= country_2.name' # literal SQL for this NOT
}, {
prefetch => {
artwork => [
{ artist => 'country' },
{ comments => { user => 'country' } },
], # table aliased here to 'country_2
+'
},
});
while ( my $poem = $rs->next ) {
say sprintf(q{tobyink doesn't hate "%s" (%s)},
$poem->artwork->name,
$poem->artwork->artist->name,
);
}
#---------------------------------------------------------------------
+#
say "\nCOMMENTS BY USERS WHO SPEAK THE SAME LANGUAGE AS THE ARTIST";
$rs = $db->resultset('Comment')->search({
'country.language_id' => \'= country_2.language_id', # literal SQL
}, {
prefetch => [
{ user => { country => 'language' } },
{ artwork => { artist => 'country' } },
],
});
while ( my $comment = $rs->next ) {
say sprintf('%s : %s said about "%s" (%s) : "%s"',
$comment->user->country->language->name,
$comment->user->name,
$comment->artwork->name,
$comment->artwork->artist->name,
$comment->text,
);
}
__DATA__
create database if not exists Art
character set = utf8mb4 collate = utf8mb4_unicode_ci;
use Art;
drop table if exists comment;
drop table if exists user;
drop table if exists play;
drop table if exists poem;
drop table if exists painting;
drop table if exists artwork;
drop table if exists artist;
drop table if exists country;
drop table if exists language;
create table language (
language_id smallint unsigned not null primary key auto_increment,
name varchar(32) not null,
unique key language_name_uk (name)
);
create table country (
country_id smallint unsigned not null primary key auto_increment,
iso_code varchar(6) not null,
name varchar(16) not null,
language_id smallint unsigned not null,
unique key country_iso_code_uk (iso_code),
unique key country_name_uk (name),
constraint country_language_fk foreign key (language_id) reference
+s language (language_id)
);
create table artist (
artist_id smallint unsigned not null primary key auto_increment,
name varchar(32) not null,
country_id smallint unsigned not null,
unique key artist_name_uk (name),
constraint artist_country_fk foreign key (country_id) references c
+ountry (country_id)
);
create table artwork (
artwork_id smallint unsigned not null primary key auto_increment,
artist_id smallint unsigned not null,
name varchar(128) not null,
year smallint unsigned not null,
unique key artwork_name_artist_uk (name, artist_id),
constraint artwork_artist_fk foreign key (artist_id) references ar
+tist (artist_id)
);
create table painting (
painting_id smallint unsigned not null primary key auto_increment,
artwork_id smallint unsigned not null,
medium enum('acrylic','oil','watercolour','other') not null defaul
+t 'other',
genre enum('history','portrait','genre','landscape','still life','
+modern','other') not null default 'other',
unique key painting_artwork_uk (artwork_id),
constraint painting_artwork_fk foreign key (artwork_id) references
+ artwork (artwork_id)
);
create table poem (
poem_id smallint unsigned not null primary key auto_increment,
artwork_id smallint unsigned not null,
meter enum('iamb','trochee','spondee','anapest','dactyl','other')
+not null default 'other',
rhyme enum('end','internal','slant','rich','eye','identical','othe
+r') not null default 'other',
unique key poem_artwork_uk (artwork_id),
constraint poem_artwork_fk foreign key (artwork_id) references art
+work (artwork_id)
);
create table play (
play_id smallint unsigned not null primary key auto_increment,
artwork_id smallint unsigned not null,
num_acts smallint unsigned not null,
genre enum('comedy','tragedy','history','other') not null default
+'other',
unique key play_artwork_uk (artwork_id),
constraint play_artwork_fk foreign key (artwork_id) references art
+work (artwork_id)
);
create table user (
user_id smallint unsigned not null primary key auto_increment,
country_id smallint unsigned not null,
name varchar(32) not null,
unique key user_name_uk (name),
constraint user_country_fk foreign key (country_id) references cou
+ntry (country_id)
);
create table comment (
comment_id smallint unsigned not null primary key auto_increment,
user_id smallint unsigned not null,
artwork_id smallint unsigned not null,
text blob,
unique key comment_artwork_user_uk (artwork_id, user_id),
constraint comment_artwork_fk foreign key (artwork_id) references
+artwork (artwork_id),
constraint comment_user_fk foreign key (user_id) references user (
+user_id)
);