#! pesudocode
Entity {
id integer PRIMARY KEY,
name varchar,
...
}
Play {
id integer PRIMARY KEY REFERENCES Entity(id),
director varchar,
...
}
Concert {
id integer PRIMARY KEY REFERENCES Entity(id),
start datetime,
venue varchar,
band varchar,
...
}
Comment {
id integer PRIMARY KEY,
entity integer REFERENCES Entity(id),
comment_text varchar,
...
}
If you need a list of concerts:
SELECT *
FROM Concert c
INNER JOIN Entity e ON c.id=e.id
If you need a list of plays:
SELECT *
FROM Play p
INNER JOIN Entity e ON p.id=e.id
If you need a list of comments on plays:
SELECT *
FROM Comment k
INNER JOIN Entity e ON k.entity=e.id
INNER JOIN Play p ON k.entity=p.id
If you need a list of comments on everything:
SELECT *
FROM Comment k
INNER JOIN Entity e ON k.entity=e.id
LEFT JOIN Play p ON k.entity=p.id
LEFT JOIN Concert c ON k.entity=c.id
In the long run, you'll benefit a lot from factoring out as much of the common stuff between Concerts, Plays, Paintings, etc into a shared Entity table, then creating additional tables to store Concert-specific, Play-specific, Painting-specific, etc fields. I don't use DBIC, so can't offer specific advice on how to achieve it with that, but from a database design point of view, this is the better way to lay stuff out.
When you want to insert a new play, you insert it into the Entity table first to get its primary key number, then use that number when inserting the additional data into the Play table.
| [reply]
[d/l]
[select] |
Hello again dear brother Bliako,
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 🙇🏼♂️.
(TL;DR The complete script to create the following demo, with SQL used to create the database, a shell call to do so and another to dump the DBIx::Class schema files; many insert commands and the selects summarized below, is at the end of this post.)
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:
- I believe you are a worthy Parson committed to doing things Right.
- I am always very glad to share what little knowledge I have of a Right TIMTOWTDI in Perl ... I might one day be working with you or someone you teach!
- I am very pleased to be able to show something new to one of my mentors like Toby ... no one has time to learn everything. And as is the case with many larger toolsets, e.g. Dist::Zilla or his own Type::Tiny framework, sometimes it's difficult to find the time to get past the learning curve to be able to analyze the benefits of using it. I hope this demo shortcuts some of that for somebody.
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 mini-application has as its central entity the Artwork which can be a Painting, a Play or a Poem (each of which has its own attributes). An Artwork has an Artist, who has a Country, where a Language is spoken. Also, there are application Users, who also have a Country and thus a Language, and who can make Comments on Artworks. Phew.
Notes:
- I'm still not totally happy with the schema as it seems it should be possible to do e.g. $painting->artist, but I think that could be solved with the existing DB schema using some more advanced DBIx features. (Update: solution shown in this Addendum.)
- One style preference: I prefer not to use id as the name of the primary ID column for a table, since (again longevity...) tables have a way of accumulating various IDs, and related objects more so.
- Apologies in advance for any cultural/gender oversimplifications/oversights/inequalities etc. :-)
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.
First, a quick look at some select snippets.
Just for context, here's the bind params and SQL generated by DBIx::Class needed to show "Poems not by English Artists that Toby doesn't hate":
'bad', 'tobyink'
SELECT me.poem_id, me.artwork_id, me.meter, me.rhyme, artwork.artwork_
+id, artwork.artist_id, artwork.name, artwork.year, artist.artist_id,
+artist.name, artist.country_id, country.country_id, country.iso_code,
+ country.name, country.language_id, comments.comment_id, comments.use
+r_id, comments.artwork_id, comments.text, user.user_id, user.country_
+id, user.name, country_2.country_id, country_2.iso_code, country_2.na
+me, country_2.language_id FROM poem me JOIN artwork artwork ON artwo
+rk.artwork_id = me.artwork_id JOIN artist artist ON artist.artist_id
+ = artwork.artist_id JOIN country country ON country.country_id = ar
+tist.country_id LEFT JOIN comment comments ON comments.artwork_id = a
+rtwork.artwork_id LEFT JOIN user user ON user.user_id = comments.user
+_id LEFT JOIN country country_2 ON country_2.country_id = user.countr
+y_id WHERE ( ( comments.text != ? AND country.name != country_2.name
+AND user.name = ? ) ) ORDER BY me.poem_id
... and that's why I use an ORM, lol.
List 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,
);
}
Output:
Leonardo Da Vinci: "Mona Lisa" (oil)
Leonardo Da Vinci: "Salvator Mundi" (oil)
Leonardo Da Vinci: "John the Baptist" (oil)
Raffaello Sanzio: "Portrait of Baldassare Castiglione" (oil)
Lucien Freud: "Benefits Supervisor Sleeping" (oil)
David Hockney: "A Bigger Splash" (acrylic)
María Gutiérrez Blanchard: "Femme à la guitare" (oil)
List 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,
);
}
Output:
William Shakespeare: "Hamlet" (tragedy)
William Shakespeare: "As You Like It" (comedy)
Naguib Mahfouz: "تحت المظلة" (other)
Federico García Lorca: "El Maleficio de la Mariposa" (other)
Comments 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;
}
Output:
About "Hamlet", 1nickt said "good"
About "Hamlet", discipulus said "meh"
About "Hamlet", tobyink said "good"
About "Hamlet", bliako said "good"
About "As You Like It", 1nickt said "good"
About "As You Like It", tobyink said "meh"
About "As You Like It", bliako said "bad"
About "As You Like It", marioroy said "bad"
About "As You Like It", marto said "bad"
About "تحت المظلة", 1nickt said "good"
About "تحت المظلة", discipulus said "bad"
About "تحت المظلة", bliako said "meh"
About "تحت المظلة", marioroy said "meh"
Art that Bliako likes
$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,
);
}
Output:
bliako likes: "Hamlet" (William Shakespeare)
bliako likes: "Venus and Adonis" (William Shakespeare)
bliako likes: "El Niño Mudo" (Federico García Lorca)
bliako likes: "2000 ام الل" (Ghassan Zaqtan)
Paintings that Discipulus does not love
$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,
);
}
Output:
discipulus does not love "Salvator Mundi" (Leonardo Da Vinci)
discipulus does not love "Portrait of Baldassare Castiglione" (Raffaello Sanzio)
Poems not by English artists that tobyink doesn't hate
$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,
);
}
Output:
tobyink doesn't hate "التلال المالحة" (Ghassan Zaqtan)
tobyink doesn't hate "El Niño Mudo" (Federico García Lorca)
tobyink doesn't hate "Sonnet 17" (Pablo Neruda)
Comments 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,
);
}
Output:
English : 1nickt said about "As You Like It" (William Shakespeare) : "good"
English : 1nickt said about "Hamlet" (William Shakespeare) : "good"
English : 1nickt said about "Venus and Adonis" (William Shakespeare) : "bad"
Eatalian : discipulus said about "Salvator Mundi" (Leonardo Da Vinci) : "meh"
Eatalian : discipulus said about "Portrait of Baldassare Castiglione" (Raffaello Sanzio) : "meh"
English : tobyink said about "As You Like It" (William Shakespeare) : "meh"
English : tobyink said about "Sonnet 17" (William Shakespeare) : "meh"
English : tobyink said about "Benefits Supervisor Sleeping" (Lucien Freud) : "good"
English : tobyink said about "Hamlet" (William Shakespeare) : "good"
English : tobyink said about "Venus and Adonis" (William Shakespeare) : "meh"
English : marioroy said about "As You Like It" (William Shakespeare) : "bad"
English : marioroy said about "Sonnet 17" (William Shakespeare) : "bad"
English : marto said about "Venus and Adonis" (William Shakespeare) : "meh"
English : marto said about "As You Like It" (William Shakespeare) : "bad"
English : marto said about "Sonnet 17" (William Shakespeare) : "meh"
English : marto said about "A Bigger Splash" (David Hockney) : "meh"
And here is the complete script for the demo.
Hope this helps!
The way forward always starts with a minimal test.
| [reply]
[d/l]
[select] |
Hi again Bliako,
"... Unless a JOIN can take additional conditions (like a WHERE comment_type='play' ..."
Heh. No reason to use DBIx::Class if not to make things easy ;-). Also please familiarize yourself with SQL::Abstract, which builds the SQL used by DBIx. That's where you'll learn how to build complex where clauses. The DBIx manuals have the info on join and prefetch and the relationships that DBIx creates for you. Note in the code below that the "join" is actually a DBIx relationship and thus the identifier "event" is the name of the relationship, not of the table (although it may be the same value, as here).
Using the schema I created above:
use strict;
use warnings;
use feature 'say';
use Bliako::Schema;
my $db = Bliako::Schema->connect(
'DBI:mysql:database=Bliako', undef, undef,
{ RaiseError => 1 },
);
my @comments = $db->resultset('Comment')->search({
'event.type' => 'concert',
'event.name' => 'Rolling Stones',
}, {
join => 'event',
});
say sprintf(
'%s said about the %s concert: "%s"',
$_->user->name, $_->event->name, $_->text,
) for @comments;
Output:
$ perl -I. bliako-2.pl
Bliako said about the Rolling Stones concert: "Greatest rock and roll
+band in the world!"
1nickt said about the Rolling Stones concert: "There is cool, then the
+re is Charlie Watts."
Hope this helps!
The way forward always starts with a minimal test.
| [reply]
[d/l]
[select] |