[Dbix-class] Fixing my relationships to get working queries
John Stoffel
john at stoffel.org
Mon Jan 19 18:23:24 GMT 2015
Peter> On 01/19/2015 06:41 PM, John Stoffel wrote:
>>
>> Sorry, I'm expecting to get back consistent results.
Peter> I am not entirely sure how to interpret that... :)
Me too... and I wrote it! I did see that the list had issues over the
Christmas break, so I'm sure we'll a bit behind. I've got a cold now
which is also slowing my brain down.
>> I've actually
>> given up trying to make multi-step join or prefetch work for me,
>> because I was running out of time.
Peter> Sorry about that - the mailing list had issues, I only received your
Peter> emails today.
No problem, if I can make it work properly, I'd be happy to use it
since I do think it makes more sense, but I can survive with what I've
got now I think.
>> Just to refresh the conversation,
>>
>> ...
>>
>> 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[[:>:]]'
Peter> This (as you called it) "monstrosity" is there for a good reason.
Peter> However I can't really explain it without the actual code that produced
Peter> it (hint - the thing below is *not* what produced the above query - it
Peter> is missing a rows => spec)
>>
>> my $rs = $schema->resultset('Name')->search({ full_name => { regexp =>
>> '[[:<:]]'.$name.'[[:>:]]' },
>> },
>> {
>> prefetch => [ 'account' ],
>> order_by => { -asc => 'full_name' },
>> });
Peter> If you have time and want to really understand what was going on - let's
Peter> start over with an actual code/query example.
Ok. Here's an example. I'm looking for all matches in full_name for
the name "carpenter". In this case, I'm not limiting the rows
returned because I know I'll only get two matches. I also want to
prefetch all the data from the Account and Boxfolder tables. To do
this, I use the following mysql 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[[:>:]]';
+---------+----------------------+------------+--------------+--------+--------+-----------+---------+
| name_id | full_name | account_id | boxfolder_id | volume | folder | range | value2 |
+---------+----------------------+------------+--------------+--------+--------+-----------+---------+
| 7333 | Carpenter, John | 3176 | 423 | 35 | fol01 | | =fol01 |
| 7333 | Carpenter, John | 3177 | 423 | 35 | fol01 | | =fol01 |
| 7333 | Carpenter, John | 3178 | 423 | 35 | fol01 | | =fol01 |
| 7334 | Carpenter, John Jr. | 3179 | 106 | 16 | 1 | 6003-6128 | =b08f01 |
| 7334 | Carpenter, John Jr. | 3180 | 112 | 16 | 7 | 6780-6806 | =b08f07 |
| 7334 | Carpenter, John Jr. | 3181 | 122 | 17 | 2 | 6999-7125 | =b09f02 |
| 7334 | Carpenter, John Jr. | 3182 | 122 | 17 | 2 | 6999-7125 | =b09f02 |
+---------+----------------------+------------+--------------+--------+--------+-----------+---------+
The substring stuff is just because the URL is long and mostly
redundant. I didn't enter the data, so I'm just working around it in
this example.
For my perl code, to get the same result, I was trying to use the
following perl code, which removes a bunch of setup code to make it
smaller:
my $rs = $schema->resultset('Name')->search({ full_name =>
{ regexp => '[[:<:]]'.$name.'[[:>:]]' }
},
{
prefetch => [ 'account' ],
rows => 10,
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";
}
}
And when I run the code, I got:
> ../bin/dbic-test2.pl carpenter
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[[:>:]]'
Full Name: Carpenter, John (7333)
account_id=3176 boxfolder_id=423 URL: fol01 V=35 F=fol01 R=
account_id=3177 boxfolder_id=423 URL: fol01 V=35 F=fol01 R=
account_id=3178 boxfolder_id=423 URL: fol01 V=35 F=fol01 R=
Full Name: Carpenter, John Jr. (7334)
account_id=3179 boxfolder_id=106 URL: b08f01 V=16 F=1 R=6003-6128
account_id=3180 boxfolder_id=112 URL: b08f07 V=16 F=7 R=6780-6806
account_id=3181 boxfolder_id=122 URL: b09f02 V=17 F=2 R=6999-7125
account_id=3182 boxfolder_id=122 URL: b09f02 V=17 F=2 R=6999-7125
And I'm completely confused why there are multiple SELECTs since the
whole idea of prefetch was to just pull in the info ahead of time even
though I'm not searching against it.
Do you want me to send you more details on the Schema I have?
Thanks,
John
More information about the DBIx-Class
mailing list