Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

DBIx::Class and "complex" joins (was Re^4: DB: what kind of relationship?)

by 1nickt (Canon)
on May 03, 2020 at 17:38 UTC ( [id://11116400]=note: print w/replies, xml ) Need Help??


in reply to Re^3: DB: what kind of relationship?
in thread DB: what kind of relationship?

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.

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.

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 => '&#1575;&#1604;&#1578;&#1604;&#1575;&#1604; &#1575;& +#1604;&#1605;&#1575;&#1604;&#1581;&#1577;', year => 1998, artist => { name => 'Ghassan Zaqtan'}, }, }, { artwork => { name => '2000 &#1575;&#1605; &#1575;&#1604;&#1604;', 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 => '&#1578;&#1581;&#1578; &#1575;&#1604;&#1605;&#1592;& +#1604;&#1577;', 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) );

Hope this helps!


The way forward always starts with a minimal test.

Replies are listed 'Best First'.
Re: DBIx::Class and "complex" joins (was Re^4: DB: what kind of relationship?)
by tobyink (Canon) on May 03, 2020 at 20:58 UTC

    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,

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

      Thanks, Toby. Glad it was of interest.

      With regard to the language issue; of course you are right that I over-simplified. I was trying to come up with a schema that could show some relatively complex joins: I thought about the family, the CD collection and the farm, but they've all been done ;-)

      Language is a thorny problem. As you say, some artists might speak a primary language that can not be derived from their country, so Language should probably be a property of the Artist. And some artworks might indeed be "in" a language that is not the artist's primary language ... so the Language should also be an attribute of the Artwork ... but does a Painting have a Language? Probably it needs to be nullable, or maybe an attribute of only certain types of artwork in their respective tables.

      With regard to the primary key in the Painting, Play and Poem tables, and having an extra column named <entity_type>_id, as I mentioned I prefer to have separate names because it can make column aliasing simpler, and also because it allows for future expansion.

      What if I realize that most poets publish collections of poetry as an artwork? I will have to add a name column to the Poem table and backfill it. But other than that, all I have to do is drop one unique key:

      unique key poem_artwork_uk (artwork_id)
      ... add a different multi-column unique key:
      unique key poem_name_artwork_uk (name, artwork_id)
      ... rerun dbicdump, and my classes will be rebuilt with a one-to-many relationship between Artwork and Poems (with all custom code preserved).

      Then I could get the number of Poems in the collection, or the other Poems, with code like:

      my $poem = $db->resultset('Poem')->search( { 'me.name' => 'How Great It Is To Frobnicate', 'artist.name' => 'Toby Inks', }, { join => { artwork => 'artist' }, }, )->single; my $num = $poem->artwork->poems->count; my @other_names = map { $_->name } $poem->artwork->poems->search({ poem_id => { '!=' => $poem->poem_id }, })->all;

      Won't be tweaking this demo any more for now though :-)

      Thanks,
      --nick


      The way forward always starts with a minimal test.

        I'd say paintings can have a language; certain paintings have linguistic content. One that springs to mind is Campbell's Soup Cans by Andy Warhol. If ISO 639-2 codes are used to identify languages, there is a code zxx meaning no linguistic content that could be used for paintings with no text, music with no lyrics, etc. There's also mul for multiple languages. This is one of the cool features ISO 639-2 alpha-3 has over the more commonly used ISO 639-2 alpha-2 codes.

Re: DBIx::Class and "complex" joins (was Re^4: DB: what kind of relationship?)
by 1nickt (Canon) on May 03, 2020 at 20:13 UTC

    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
    ... and add the following to Artist.pm and Comment.pm:
    use Moo; sub language { $_[0]->country->language->name } # string

    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 +' }, });

    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, ); }
    ... to:
    while ( my $poem = $rs->next ) { say sprintf(q{tobyink doesn't hate "%s" (%s)}, $poem->name, $poem->artist_name, ); }

    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 ) { ... }

    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/ }; }
    ... 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.
Re: DBIx::Class and "complex" joins (was Re^4: DB: what kind of relationship?)
by bliako (Monsignor) on May 04, 2020 at 08:02 UTC

    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).

Re: DBIx::Class and "complex" joins (was Re^4: DB: what kind of relationship?)
by bliako (Monsignor) on May 04, 2020 at 20:02 UTC

    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

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://11116400]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others rifling through the Monastery: (3)
As of 2024-03-29 01:49 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found