<tt><font size=2>Len Jaffe <lenjaffe@jaffesystems.com> wrote on
06/19/2012 02:06:21 PM:<br>
<br>
> From:</font></tt>
<br><tt><font size=2>> <br>
> Len Jaffe <lenjaffe@jaffesystems.com></font></tt>
<br><tt><font size=2>> <br>
> To:</font></tt>
<br><tt><font size=2>> <br>
> "DBIx::Class user and developer list" <dbix-class@lists.scsys.co.uk></font></tt>
<br><tt><font size=2>> <br>
> Date:</font></tt>
<br><tt><font size=2>> <br>
> 06/19/2012 02:10 PM</font></tt>
<br><tt><font size=2>> <br>
> Subject:</font></tt>
<br><tt><font size=2>> <br>
> Re: [Dbix-class] Search_rs not returning newly created rows</font></tt>
<br><tt><font size=2>> <br>
> <br>
</font></tt>
<br><tt><font size=2>> On Tue, Jun 19, 2012 at 2:58 PM, Kenneth S Mclane
<ksmclane@us.ibm.com> wrote:</font></tt>
<br><tt><font size=2>> <br>
> Moritz Lenz <moritz@faui2k3.org> wrote on 06/19/2012 01:16:42
PM:<br>
</font></tt>
<br><tt><font size=2>> </font></tt>
<br><tt><font size=2>> It looks like it is because of the relationships
limiting the rows <br>
> returned. I can comment out the prefetch statement and my extra rows<br>
> actually show up. In one case this works fine, in another it causes
<br>
> the search to fail. I think I may need to create a second rs with
<br>
> just the related data so if I get a null value I can bypass it. Not
<br>
> sure exactly how to do this and keep the data straight.</font></tt>
<br><tt><font size=2>> <br>
> Can you share the relevant queries?</font></tt>
<br><tt><font size=2>> <br>
> It may be that you're using an inner join when you need and outer
<br>
> join if you can;t create the related records before the re-query.</font></tt>
<br><tt><font size=2>> <br>
> L.</font></tt>
<br><tt><font size=2>> <br>
> -- <br>
> lenjaffe@jaffesystems.com 614-404-4214
</font></tt><a href=www.volunteerable.net><tt><font size=2>www.volunteerable.net</font></tt></a>
<br><tt><font size=2>> Proprietor: </font></tt><a href=http://www.theycomewithcheese.com/><tt><font size=2>http://www.theycomewithcheese.com/</font></tt></a><tt><font size=2>
- An Homage to Fromage</font></tt>
<br><tt><font size=2>> Greenbar: Grubmaster: 2012-2009, Grub
Asst: 2008, Trained: 2007.</font></tt>
<br>
<br><tt><font size=2>This is the code with the prefetch commented out.</font></tt>
<br><tt><font size=2>sub list :Local {</font></tt>
<br><tt><font size=2> my ($self, $c,
$page) = @_;</font></tt>
<br><tt><font size=2> $page = $c->req->param('page')
|| 1;</font></tt>
<br><tt><font size=2> my $search = $c->req->param('search');</font></tt>
<br><tt><font size=2> $search =~ s/($search)/\U$1/gi;</font></tt>
<br><tt><font size=2> my $rs = $c->model('ORANGES::Account')->search_rs(
{ -or => [</font></tt>
<br><tt><font size=2>
'me.account_code' => {'like', "%$search%"
},</font></tt>
<br><tt><font size=2>
'me.account_name' => {'like', "%$search%"
}, </font></tt>
<br><tt><font size=2> ] },{
</font></tt>
<br><tt><font size=2> #prefetch =>
{ account => [ { department => 'manager'}, { servers => 'subs'
}, 'metrics' ] },</font></tt>
<br><tt><font size=2> order_by =>
'me.account_code',</font></tt>
<br><tt><font size=2> rows => 5,</font></tt>
<br><tt><font size=2> page => $page,</font></tt>
<br><tt><font size=2> });</font></tt>
<br><tt><font size=2> $c->stash(accounts
=> $rs);</font></tt>
<br><tt><font size=2> $c->stash(pager
=> $rs->pager());</font></tt>
<br><tt><font size=2> $c->stash->{'template'}=>'results/list';</font></tt>
<br><tt><font size=2>}</font></tt>
<br>
<br><tt><font size=2>which generates this SQL and gives me the account
info minus the related data I normally get:</font></tt>
<br>
<br><tt><font size=2>SELECT account_id, account_code, account_name, account_policy,
account_workitem, account_target, start_date, kit_date, upgrade_date, approval_date,
sunset_date, alert_flag, cirats_flag, report_flag, priv_flag, sample_flag,
sample_rate, account_pid, department_id, chip_id FROM (</font></tt>
<br><tt><font size=2> SELECT account_id, account_code, account_name,
account_policy, account_workitem, account_target, start_date, kit_date,
upgrade_date, approval_date, sunset_date, alert_flag, cirats_flag, report_flag,
priv_flag, sample_flag, sample_rate, account_pid, department_id, chip_id,
ROW_NUMBER() OVER( ORDER BY me.account_code ) AS rno__row__index
FROM (</font></tt>
<br><tt><font size=2> SELECT me.account_id, me.account_code,
me.account_name, me.account_policy, me.account_workitem, me.account_target,
me.start_date, me.kit_date, me.upgrade_date, me.approval_date, me.sunset_date,
me.alert_flag, me.cirats_flag, me.report_flag, me.priv_flag, me.sample_flag,
me.sample_rate, me.account_pid, me.department_id, me.chip_id FROM
HCDB_TEST.ACCOUNT me WHERE ( ( me.account_code LIKE ? OR me.account_name
LIKE ? ) )</font></tt>
<br><tt><font size=2> ) me</font></tt>
<br><tt><font size=2>) me WHERE rno__row__index >= ? AND rno__row__index
<= ?</font></tt>
<br>
<br><tt><font size=2>: '%11211%', '%11211%', '1', '5'</font></tt>
<br><tt><font size=2>SELECT me.department_id, me.department_code, me.department_pid,
me.manager_id FROM HCDB_TEST.DEPARTMENT me WHERE ( me.department_id = ?
): '170'</font></tt>
<br><tt><font size=2>SELECT me.account_id, me.account_code, me.num_servers,
me.num_subsystems FROM HCDB_TEST.METRICS me WHERE ( me.account_id = ? ):
'2445'</font></tt>
<br><tt><font size=2>SELECT me.account_id, me.account_code, me.num_servers,
me.num_subsystems FROM HCDB_TEST.METRICS me WHERE ( me.account_id = ? ):
'2445'</font></tt>
<br>
<br><tt><font size=2>And if I un-comment it I get this sql and no results
on page:</font></tt>
<br>
<br><tt><font size=2>SELECT me.account_id, me.account_code, me.account_name,
me.account_policy, me.account_workitem, me.account_target, me.start_date,
me.kit_date, me.upgrade_date, me.approval_date, me.sunset_date, me.alert_flag,
me.cirats_flag, me.report_flag, me.priv_flag, me.sample_flag, me.sample_rate,
me.account_pid, me.department_id, me.chip_id, account.account_id, account.account_code,
account.account_name, account.account_policy, account.account_workitem,
account.account_target, account.start_date, account.kit_date, account.upgrade_date,
account.approval_date, account.sunset_date, account.alert_flag, account.cirats_flag,
account.report_flag, account.priv_flag, account.sample_flag, account.sample_rate,
account.account_pid, account.department_id, account.chip_id, department.department_id,
department.department_code, department.department_pid, department.manager_id,
manager.manager_id, manager.manager_name, manager.manager_email, servers.server_id,
servers.server_name, servers.server_domain, servers.server_ip, servers.server_type,
servers.os_vendor, servers.os_name, servers.os_version, servers.account_id,
subs.subsystem_id, subs.subsystem_name, subs.subsystem_env, subs.subsystem_app,
subs.subsystem_date, subs.software_vendor, subs.software_name, subs.software_version,
subs.software_fixpack, subs.confidential_flag, subs.hck_frequency, subs.hck_retention,
subs.hck_fixtime, subs.hck_notify, subs.subsystem_pid, subs.server_id,
metrics.account_id, metrics.account_code, metrics.num_servers, metrics.num_subsystems
FROM (</font></tt>
<br><tt><font size=2>SELECT account_id, account_code, account_name, account_policy,
account_workitem, account_target, start_date, kit_date, upgrade_date, approval_date,
sunset_date, alert_flag, cirats_flag, report_flag, priv_flag, sample_flag,
sample_rate, account_pid, department_id, chip_id FROM (</font></tt>
<br><tt><font size=2> SELECT account_id, account_code, account_name,
account_policy, account_workitem, account_target, start_date, kit_date,
upgrade_date, approval_date, sunset_date, alert_flag, cirats_flag, report_flag,
priv_flag, sample_flag, sample_rate, account_pid, department_id, chip_id,
ROW_NUMBER() OVER( ORDER BY me.account_code ) AS rno__row__index
FROM (</font></tt>
<br><tt><font size=2> SELECT me.account_id, me.account_code,
me.account_name, me.account_policy, me.account_workitem, me.account_target,
me.start_date, me.kit_date, me.upgrade_date, me.approval_date, me.sunset_date,
me.alert_flag, me.cirats_flag, me.report_flag, me.priv_flag, me.sample_flag,
me.sample_rate, me.account_pid, me.department_id, me.chip_id FROM
HCDB_TEST.ACCOUNT me JOIN HCDB_TEST.ACCOUNT account ON account.account_id
= me.account_id JOIN HCDB_TEST.DEPARTMENT department ON department.department_id
= account.department_id JOIN HCDB_TEST.MANAGER manager ON manager.manager_id
= department.manager_id JOIN HCDB_TEST.METRICS metrics ON metrics.account_id
= account.account_id WHERE ( ( me.account_code LIKE ? OR me.account_name
LIKE ? ) )</font></tt>
<br><tt><font size=2> ) me</font></tt>
<br><tt><font size=2>) me WHERE rno__row__index >= ? AND rno__row__index
<= ?</font></tt>
<br>
<br><tt><font size=2>) me JOIN HCDB_TEST.ACCOUNT account ON account.account_id
= me.account_id JOIN HCDB_TEST.DEPARTMENT department ON department.department_id
= account.department_id JOIN HCDB_TEST.MANAGER manager ON manager.manager_id
= department.manager_id LEFT JOIN HCDB_TEST.SERVER servers ON servers.account_id
= account.account_id LEFT JOIN HCDB_TEST.SUBSYSTEM subs ON subs.server_id
= servers.server_id JOIN HCDB_TEST.METRICS metrics ON metrics.account_id
= account.account_id WHERE ( ( me.account_code LIKE ? OR me.account_name
LIKE ? ) ) ORDER BY me.account_code, servers.account_id, subs.server_id:
'%11211%', '%11211%', '1', '5', '%11211%', '%11211%'</font></tt>