Hi Bliako,

I build my schemas the other way, from the database which I have created via an SQL file. I find the DBIx::Class code too gnarly to write by hand!

I used the following schema:

use Bliako;

drop table if exists comment;
drop table if exists event;
drop table if exists user;

create table user (
    user_id smallint unsigned not null primary key auto_increment,
    name varchar(32) not null
);

create table event (
    event_id smallint unsigned not null primary key auto_increment,
    type enum('play','concert') not null,
    name varchar(32) not null,
    unique key type_name_uk (type, name)
);

create table comment (
    comment_id smallint unsigned not null primary key auto_increment,
    user_id smallint unsigned not null,
    event_id smallint unsigned not null,
    text blob,
    key event_id_key (event_id),
    key user_id_key (user_id),
    unique key event_user_uk (event_id, user_id),
    constraint event_id foreign key(event_id) references event (event_
+id),
    constraint user_id foreign key(user_id) references user (user_id)
);

insert into user values (null, 'Bliako'), (null, '1nickt');
select user_id into @bliako_id from user where name = 'Bliako';
select user_id into @1nickt_id from user where name = '1nickt';
insert into event values (null, 'play', 'Hamlet');
insert into event values (null, 'concert', 'Rolling Stones');
select event_id into @play_id from event where name ='Hamlet';
select event_id into @concert_id from event where name ='Rolling Stone
+s';
insert into comment values
  (null, @bliako_id, @play_id, 'Somewhat depressing'),
  (null, @bliako_id, @concert_id, 'Greatest rock and roll band in the 
+world!'),
  (null, @1nickt_id, @concert_id, 'There is cool, then there is Charli
+e Watts.');
[download]

Then I created the DBIx schema with:

$ dbicdump -o dump_directory=. Bliako::Schema 'dbi:mysql:database=Blia
+ko'
[download]

My test script:

use strict;
use warnings;
use feature 'say';
use Bliako::Schema;

my $db = Bliako::Schema->connect(
    'DBI:mysql:database=Bliako', undef, undef, { RaiseError => 1 },
);

for my $username ('Bliako', '1nickt') {
    my $user = $db->resultset('User')->search({ name => $username })->
+first;

    for my $comment ( $user->comments->all ) {
        say sprintf( '%s said about the %s "%s": "%s"',
                      $username,
                      $comment->event->type,
                      $comment->event->name,
                      $comment->text );
    }
}
[download]

Output:

perl -I. bliako.pl

Bliako said about the play "Hamlet": "Somewhat depressing"
Bliako said about the concert "Rolling Stones": "Greatest rock and rol
+l band in the world!"
1nickt said about the concert "Rolling Stones": "There is cool, then t
+here is Charlie Watts."
[download]

Hope this helps!

Thank you 1nickt for showing this handy trick. It's a lot there! I am new to DBIC and SQL in general so that's appreciated.

But I would prefer 2 different tables: Play, Concert. Because the search is like "select all comments for this concert". Unless a JOIN can take additional conditions (like a WHERE comment_type='play' - excuse my lameness?) I can perhaps use an intermediate table which holds comment ids, comment types and foreign key ids (e.g. for play or concert) or even get all comments for a specific event type, then find those with the specific concert id.

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.

toby döt ink

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!



The way forward always starts with a minimal test.