[Dbix-class] Fixing my relationships to get working queries

John Stoffel john at stoffel.org
Tue Jan 13 02:33:41 GMT 2015


Hi all,

I'm a DBIx::class newbie who is trying to write a web search app using
the perl Dancer web framework with the Dancer::Plugin::DBIC and having
some problems.  I've got some hand written SQL which seems to do what
I want, and I've attempted to setup my schema properly to do my
queries, but it's not working.  

Working SQL is:

   mysql> SELECT n.name_id,n.full_name,a.account_id,b.boxfolder_id,b.volume,b.folder,b.range,substring(a.url,118,locate('&',a.url,118)-118) AS value2  FROM names as n LEFT JOIN account AS a ON n.name_id = a.name_id LEFT JOIN boxfolder AS b on a.boxfolder_id = b.boxfolder_id WHERE n.full_name REGEXP '[[:<:]]byrne[[:>:]]' LIMIT 10;
   +---------+----------------------------+------------+--------------+--------+--------+-----------+---------+
   | name_id | full_name                  | account_id | boxfolder_id | volume | folder | range     | value2  |
   +---------+----------------------------+------------+--------------+--------+--------+-----------+---------+
   |    7220 | Byrne, E. & McCausland A.  |       2642 |          322 | 31     | 8      | 5123-5148 | =b22f08 |
   |    7221 | Byrne, G.                  |       2643 |          295 | 29     | 11     | 4293-4314 | =b20f11 |
   |    7222 | Byrne, John                |       2644 |          106 | 16     | 1      | 6003-6128 | =b08f01 |
   |    7223 | Byrne, P.                  |       2645 |          425 | 37     | fol03  |           | =fol03  |
   |    7224 | Byrne, P., Estate of       |       2646 |          425 | 37     | fol03  |           | =fol03  |
   |    7225 | Byrne, Patrick             |       2672 |          210 | 23     | 1      | 1404-1433 | =b15f01 |
   |    7225 | Byrne, Patrick             |       2673 |          210 | 23     | 1      | 1404-1433 | =b15f01 |
   |    7225 | Byrne, Patrick             |       2674 |          214 | 23     | 5      | 1502-1538 | =b15f05 |
   |    7225 | Byrne, Patrick             |       2675 |          215 | 23     | 6      | 1539-1596 | =b15f06 |
   |    7225 | Byrne, Patrick             |       2676 |          215 | 23     | 6      | 1539-1596 | =b15f06 |
   +---------+----------------------------+------------+--------------+--------+--------+-----------+---------+
   10 rows in set (0.14 sec)


And my DBIC query ends up looking like this:

SELECT me.name_id, me.full_name, me.last_name, me.first_name, me.comments, account.account_id, account.account_number, account.boxfolder_id, account.name_id, account.url, account.comments, boxfolder.boxfolder_id, boxfolder.volume, boxfolder.box, boxfolder.folder, boxfolder.range, boxfolder.comments FROM (SELECT me.name_id, me.full_name, me.last_name, me.first_name, me.comments FROM names me WHERE ( full_name REGEXP ? ) ORDER BY full_name ASC LIMIT ?) me LEFT JOIN account account ON account.name_id = me.name_id LEFT JOIN boxfolder boxfolder ON boxfolder.boxfolder_id = account.account_id WHERE ( full_name REGEXP ? ) ORDER BY full_name ASC: '[[:<:]]Byrne[[:>:]]', '20', '[[:<:]]Byrne[[:>:]]'


Which is obviously not correct, or at least it's sub-optimal.  My tables are like this:

    mysql> describe names;
    +------------+--------------+------+-----+---------+----------------+
    | Field      | Type         | Null | Key | Default | Extra          |
    +------------+--------------+------+-----+---------+----------------+
    | name_id    | int(7)       | NO   | PRI | NULL    | auto_increment |
    | full_name  | varchar(50)  | NO   |     | NULL    |                |
    | last_name  | text         | YES  |     | NULL    |                |
    | first_name | text         | YES  |     | NULL    |                |
    | comments   | varchar(100) | YES  |     | NULL    |                |
    +------------+--------------+------+-----+---------+----------------+

    mysql> describe account;          
    +----------------+---------------+------+-----+---------+----------------+
    | Field          | Type          | Null | Key | Default | Extra          |
    +----------------+---------------+------+-----+---------+----------------+
    | account_id     | int(7)        | NO   | PRI | NULL    | auto_increment |
    | account_number | varchar(10)   | NO   |     | NULL    |                |
    | boxfolder_id   | int(7)        | NO   |     | NULL    |                |
    | name_id        | int(7)        | YES  |     | NULL    |                |
    | url            | varchar(1028) | YES  |     | NULL    |                |
    | comments       | varchar(100)  | YES  |     | NULL    |                |
    +----------------+---------------+------+-----+---------+----------------+

    mysql> describe boxfolder;
    +--------------+--------------+------+-----+---------+----------------+
    | Field        | Type         | Null | Key | Default | Extra          |
    +--------------+--------------+------+-----+---------+----------------+
    | boxfolder_id | int(7)       | NO   | PRI | NULL    | auto_increment |
    | volume       | varchar(20)  | NO   |     | NULL    |                |
    | box          | varchar(10)  | NO   |     | NULL    |                |
    | folder       | varchar(20)  | YES  |     | NULL    |                |
    | range        | text         | YES  |     | NULL    |                |
    | comments     | varchar(100) | YES  |     | NULL    |                |
    +--------------+--------------+------+-----+---------+----------------+


The Schema files were generated with the DBIx::Class::Schema::Loader 


And this is the following schema I'm using in my perl code:

  # Show SQL query
  $schema->storage->debug(1);

  # log to a specific file
  $schema->storage->debugfh(IO::File->new('/tmp/carey.out','w'));
  
  # search in Carey
  my @r = $schema->resultset('Name')->search( { full_name =>
                                                { regexp => '[[:<:]]'.$query.'[[:>:]]' }
                                              },
                                              {
                                               order_by => { -asc => 'full_name' },
                                               prefetch => { 'account' => 'boxfolder' },
                                               rows => $limit,
                                              });
  return @r;



And I don't get back consistent the info I expect.  Sometimes it works
and I get what I think is the right info, but other times for other
queries it doesn't give me back what I want.  The Schema looks like
this:

Name.pm:

  # Created by DBIx::Class::Schema::Loader v0.07025 @ 2014-11-09 21:26:06
  # DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:phDO23qEE/ccn+lnwUG4Cw

  __PACKAGE__->has_many('account', 'Carey::Schema::Result::Account','name_id');

Account.pm:

  # Created by DBIx::Class::Schema::Loader v0.07025 @ 2014-11-09 21:26:06
  # DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:z+bO9XivBmPGrD9BmbSaRg

  __PACKAGE__->belongs_to('name', 'Carey::Schema::Result::Name','account_id');
  __PACKAGE__->has_one('boxfolder', 'Carey::Schema::Result::Boxfolder','boxfolder_id' );


Boxfolder.pm:

  # Created by DBIx::Class::Schema::Loader v0.07025 @ 2014-11-09 21:26:06
  # DO NOT MODIFY THIS OR ANYTHING ABOVE! md5sum:0niCoJ/aJgMl1QYT77b2sA

  __PACKAGE__->belongs_to('account','Carey::Schema::Result::Account','boxfolder_id');


The basic idea is that I do all my searching on the Names table,
against 'full_name'.  I'm doing a regexp since they want word based
matches and this was simpler thank a bunch of LIKE ... OR LIKE ... OR
LIKE... constructs.  I'm sure it's not optimal, but it's not a huge
DB in terms of the number of rows.  I'm trying to make it work.

So my pure perl/DBIC has the same problem, it doesn't give me back the
results I expect so I'm sure I've done something wrong in my Schema,
which is throwing things off.

Does anyone have any pointers on what I'm doing wrong here?

Thanks,
John



More information about the DBIx-Class mailing list