JOIN conditions can be just as complicated as WHERE conditions, but overall having Play and Concert be totally separate things will bite you in the butt repeatedly in the future. The best structure for your database is something like this:

#! 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,
  ...
}
[download]

If you need a list of concerts:

SELECT *
FROM Concert c
INNER JOIN Entity e ON c.id=e.id
[download]

If you need a list of plays:

SELECT *
FROM Play p
INNER JOIN Entity e ON p.id=e.id
[download]

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
[download]

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
[download]

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.

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:

1. I believe you are a worthy Parson committed to doing things Right.
2. 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!
3. 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.

• List of Paintings
• List of Plays
• Comments on Plays
• Artworks that Bliako likes
• Paintings that Discipulus does not love
• Poems not by English Artists that Toby doesn't hate
• Comments by users who speak the same language as the artist

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
[download]

... 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,
    );
}
[download]

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,
    );
}
[download]

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;
}
[download]

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,
    );
}
[download]

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,
    );
}
[download]

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,
    );
}
[download]

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,
    );
}
[download]

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.

Read more... (20 kB)

Hope this helps!



The way forward always starts with a minimal test.

A very comprehensive answer. I am suitably impressed. There are two things I'd do differently.

You noted that your cultural stuff was simplistic. It makes the error of associating languages with countries. An artwork being produced by an artist who lives in the UK in no way means that the artwork is in English. It may be artwork in a foreign language. It may be artwork produced a long time ago. (Latin was once the official language here.) It may be artwork in a British minority language such as Irish Gaelic, Welsh, Scots, Scottish Gaelic, Cornish, or Cumbric. (Side note: prior to Brexit, the UK used to get funding from the EU for supporting Irish Gaelic as a minority language. It is also spoken as a minority language in the Republic of Ireland, but they are ineligible for getting similar funding because although it's spoken by a minority of Irish people, it is one of Ireland's two official languages, and you can't get funding for supporting your own official language. Other side note: Scots and Scottish Gaelic are two entirely different languages. Scots is a cousin of Modern English. Modern English and Scots both evolved from different dialects of Old English. Scottish Gaelic is more closely related to Irish Gaelic, Welsh, Cornish, and Cumbric. Scots and Scottish Gaelic are about as related as French and Greek.)

The other thing I'd do differently is to drop these three columns entirely:

create table painting (
    painting_id smallint unsigned not null primary key auto_increment,

create table poem (
    poem_id smallint unsigned not null primary key auto_increment,

create table play (
    play_id smallint unsigned not null primary key auto_increment,
[download]

All three of these tables already have a column which is not null and with each row having a unique numeric value.

toby döt ink

Addendum

I said above "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".

You can add missing pseudo-relationships very easily by turning your ResultSet class into a Moo class and adding object methods. For this demonstration, add the following code to Painting.pm, Play.pm and Poem.pm (after the "do not add anything above this line" line!):

use Moo;
sub artist { $_[0]->artwork->artist }            # returns a DBIx obj
sub artist_name { $_[0]->artwork->artist->name } # returns a string
sub name { $_[0]->artwork->name }                # returns a string
[download]

... and add the following to Artist.pm and Comment.pm:

use Moo;
sub language { $_[0]->country->language->name } # string
[download]

Now in order to get "Poems not by English artists that tobyink doesn't hate", the query code remains unchanged (because the new accessors are DBIx-side, not SQL-side):

$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
+'
    },
});
[download]

But the application code changes from:

  
while ( my $poem = $rs->next ) {
    say sprintf(q{tobyink doesn't hate "%s" (%s)},
        $poem->artwork->name,
        $poem->artwork->artist->name,
    );
}
[download]

... to:

  
while ( my $poem = $rs->next ) {
    say sprintf(q{tobyink doesn't hate "%s" (%s)},
        $poem->name,
        $poem->artist_name,
    );
}
[download]

You can use the new accessors throughout the application, e.g. for making a comparison similar to the one in "Comments by Users who speak the same Language as the Artist" in application code rather than SQL:

if ( $comment->language eq $play->artist->language ) { ... }
[download]

And you can add arbitrary methods to the classes, not just accessors. For example, if you are building a REST application where your DB tables and ORM classes represent resources exposed via API endpoints, you can add a custom chained deletion flow, or a standard method that builds the HTTP response object:

use Moo;

sub endpoint_data {
    return { map { $_ => $_[0]->$_ } qw/only these fields/ };
}
[download]

... so you can always call $obj->endpoint_data when you are finished processing the logic for the request.

Hope this helps!



The way forward always starts with a minimal test.

1nickt this is a great demonstration and guide. What I tried last night was to bootstrap a DBIx::Class::Schema from your SQL and then take ownership of those schemata, meaning: making any changes in there and not to the SQL. (I have noticed dbicdump produces pod content too which is helpful).

Read more... (15 kB)

Peripheral to the substance provided by 1nickt and tobyink, I will leave here what I learned trying to find alternatives for the shelling-out from 1nickt's excellent demo.

I have tested 2 modules for SQL'ing from a file: DBIx::RunSQL (for both files and strings and quite recent too!) and DBIx::MultiStatementDo for SQL strings. And they work great. We are talking about multiple SQL statements spanning over many lines, etc. So using either of those modules will avoid shelling out for mysql. Added bonus & most importantly both are DB-vendor agnostic: just supply a DSN.

Also, I have tested replacing shelling out dbicdump with DBIx::Class::Schema::Loader's make_schema_at() and that works fine too.

thank you again 1nickt and tobyink, bw bliako

Thank you tobyink your answer will guide me as an example on Table Inheritance mentioned and I will adopt it. Very helpful.

bw, bliako

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.

OK!

Hi Bliako,

Very true, but note that any insert you do using DBIx relationships handles this automatically.

Hope this helps!



The way forward always starts with a minimal test.

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;
[download]

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."
[download]

Hope this helps!