[Dbix-class] Fixing my relationships to get working queries
Hartmaier Alexander
alexander.hartmaier at t-systems.at
Tue Jan 20 08:36:38 GMT 2015
On 2015-01-19 18:41, John Stoffel wrote:
>>>>>> "Peter" == Peter Rabbitson <rabbit+dbic at rabbit.us> writes:
> Peter> On 01/13/2015 03:33 AM, John Stoffel wrote:
>>> # 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.
> Peter> You need to expand on what precisely isn't "as expected". That
> Peter> is - from your query above I can see DBIC generating the SQL to
> Peter> satisfy exactly what you asked for. So the disconnect is in you
> Peter> ot fully understanding the way you formulated the "query" to
> Peter> DBIC itself.
>
> Sorry, I'm expecting to get back consistent results. I've actually
> given up trying to make multi-step join or prefetch work for me,
> because I was running out of time. So I ended up de-normalizing my
> data.
>
> Just to refresh the conversation, I have a table with the following
> two level relationship:
>
> Names -> has_many -> Accounts -> has_one -> Boxfolder
>
> Where many different accounts can share a Boxfolder row. Basic
> stuff.
Seems like one of the more often happening mistakes: the has_one should
be a belongs_to instead because Accounts stores the primary key of a
Boxfolder.
>
>
> So in the end I simply copied the columns from Boxfolder into Accounts
> and then copied over the data. It's only 20,000 rows, so it's nothing
> huge and it now works for me.
>
> Peter> Please expand on this so I can answer your question in a manner that
> Peter> will help you generally in the future.
>
> I've been looking at the DBIx::Class::Manual::Cookbook at the
> "Multi-Step prefetch" but since the example doesn't give the
> relationships, it's hard for me to mentally map what I'm reading in
> the example code to what I have. This is probably my biggest
> complaint of all the examples, they just assume to much knowledge.
>
> Anyway, I have the following classes, with Name being the only class I
> search, using the "full_name" column.
>
> Name.pm:
>
> package Carey::Schema::Result::Name;
> use base 'DBIx::Class::Core';
> __PACKAGE__->table("names");
> __PACKAGE__->add_columns(
> "name_id",
> { data_type => "integer", is_auto_increment => 1, is_nullable => 0
> },
> "full_name",
> { data_type => "varchar", is_nullable => 0, size => 50 },
> "last_name",
> { data_type => "text", is_nullable => 1 },
> "first_name",
> { data_type => "text", is_nullable => 1 },
> "comments",
> { data_type => "varchar", is_nullable => 1, size => 100 },
> );
> __PACKAGE__->set_primary_key("name_id");
> __PACKAGE__->has_many('account', 'Carey::Schema::Result::Account','name_id');
>
>
> Account.pm:
> package Carey::Schema::Result::Account;
> use base 'DBIx::Class::Core';
> __PACKAGE__->table("account");
> __PACKAGE__->add_columns(
> "account_id",
> { data_type => "integer", is_auto_increment => 1, is_nullable => 0
> },
> "account_number",
> { data_type => "varchar", is_nullable => 0, size => 10 },
> "boxfolder_id",
> { data_type => "integer", is_nullable => 0 },
> "name_id",
> { data_type => "integer", is_nullable => 1 },
> "url",
> { data_type => "varchar", is_nullable => 1, size => 1028 },
> "comments",
> { data_type => "varchar", is_nullable => 1, size => 100 },
> "volume",
> { data_type => "varchar", is_nullable => 0, size => 20 },
> "box",
> { data_type => "varchar", is_nullable => 0, size => 10 },
> "folder",
> { data_type => "varchar", is_nullable => 1, size => 20 },
> "range",
> { data_type => "text", is_nullable => 1 },
> "comments",
> { data_type => "varchar", is_nullable => 1, size => 100 },
> );
> __PACKAGE__->set_primary_key("account_id");
> __PACKAGE__->belongs_to('name', 'Carey::Schema::Result::Name','name_id');
> __PACKAGE__->has_one('boxfolder', 'Carey::Schema::Result::Boxfolder', 'boxfolder_id');
>
>
> Boxfolder.pm:
> package Carey::Schema::Result::Boxfolder;
> use base 'DBIx::Class::Core';
> __PACKAGE__->table("boxfolder");
> __PACKAGE__->add_columns(
> "boxfolder_id",
> { data_type => "integer", is_auto_increment => 1, is_nullable => 0
> },
> "volume",
> { data_type => "varchar", is_nullable => 0, size => 20 },
> "box",
> { data_type => "varchar", is_nullable => 0, size => 10 },
> "folder",
> { data_type => "varchar", is_nullable => 1, size => 20 },
> "range",
> { data_type => "text", is_nullable => 1 },
> "comments",
> { data_type => "varchar", is_nullable => 1, size => 100 },
> );
> __PACKAGE__->set_primary_key("boxfolder_id");
> __PACKAGE__->belongs_to('account','Carey::Schema::Result::Account','boxfolder_id');
>
>
>
> So when I run my test search script, it generates an SQL query using
> TWO values passed in, both of which are the regexps I'm searching
> for. Instead of getting something like this query:
>
> mysql> SELECT n.name_id,n.full_name,a.account_id,b.boxfolder_id,
> mysql> b.volume, b.folder, b.range,
> mysql> substring(a.url,118,locate('&',a.url,118)-118) AS value2 FROM
> mysql> names as n LEFT JOIN account AS a ON n.name_id = a.name_id LEFT
> mysql> JOIN boxfolder AS b on a.boxfolder_id = b.boxfolder_id WHERE
> mysql> n.full_name REGEXP '[[:<:]]carpenter[[:>:]]';
>
>
> I get this monstrosity:
>
> 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,
> account.volume, account.box, account.folder, account.range 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 WHERE ( full_name REGEXP ? ) ORDER BY full_name ASC:
> '[[:<:]]carpenter[[:>:]]', '10', '[[:<:]]carpenter[[:>:]]'
>
> And this is my test code:
>
> #!/usr/bin/perl -w
>
> use DBIx::Class;
> use lib '../lib';
> use Carey::Schema;
> use Data::Dumper;
>
> die "Usage: $0 name\n\n" if $#ARGV < 0;
> my $name = shift @ARGV;
>
> my $schema =
> Carey::Schema->connect('DBI:mysql:database=careymss;host=localhost;port=3306',
> 'kiddb','', { PrintError => 1, RaiseError => 1});
>
> my $rs = $schema->resultset('Name')->search({ full_name => { regexp =>
> '[[:<:]]'.$name.'[[:>:]]' },
> },
> {
> prefetch => [ 'account' ],
> order_by => { -asc => 'full_name' },
> });
> $schema->storage->debug(1);
>
> my @r = $rs->all;
> foreach my $r (@r) {
> print "Full Name: ", $r->full_name, " (", $r->name_id, ")\n";
>
> foreach my $a ($r->account()) {
> print " account_id=", $a->account_id();
> print " boxfolder_id=",$a->boxfolder_id()," ";
>
> my $t = $a->url();
> $t =~ m/value2=(\w+)\&/;
> print " URL: $1";
>
> $vol = $a->volume();
> $folder = $a->folder();
> $range = $a->range();
> print " V=$vol " if defined $vol;
> print " F=$folder " if defined $folder;
> print " R=$range " if defined $range;
>
> print "\n";
> }
> }
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
T-Systems Austria GesmbH Rennweg 97-99, 1030 Wien
Handelsgericht Wien, FN 79340b
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
Notice: This e-mail contains information that is confidential and may be privileged.
If you are not the intended recipient, please notify the sender and then
delete this e-mail immediately.
*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*"*
More information about the DBIx-Class
mailing list