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.

In reply to Re: DBIx::Class and "complex" joins (was Re^4: DB: what kind of relationship?) by 1nickt
in thread DB: what kind of relationship? by bliako

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.