[Dbix-class] Search_rs not returning newly created rows

[Kenneth S Mclane]

Len Jaffe <lenjaffe at jaffesystems.com> wrote on 06/19/2012 02:06:21 PM:

> From:
> 
> Len Jaffe <lenjaffe at jaffesystems.com>
> 
> To:
> 
> "DBIx::Class user and developer list" <dbix-class at lists.scsys.co.uk>
> 
> Date:
> 
> 06/19/2012 02:10 PM
> 
> Subject:
> 
> Re: [Dbix-class] Search_rs not returning newly created rows
> 
> 

> On Tue, Jun 19, 2012 at 2:58 PM, Kenneth S Mclane <ksmclane at us.ibm.com> 
wrote:
> 
> Moritz Lenz <moritz at faui2k3.org> wrote on 06/19/2012 01:16:42 PM:

>  
> It looks like it is because of the relationships limiting the rows 
> returned. I can comment out the prefetch statement and my extra rows
> actually show up. In one case this works fine, in another it causes 
> the search to fail. I think I may need to create a second rs with 
> just the related data so if I get a null value I can bypass it. Not 
> sure exactly how to do this and keep the data straight.
> 
> Can you share the relevant queries?
> 
> It may be that you're using an inner join when you need and outer 
> join if you can;t create the related records before the re-query.
> 
> L.
> 
> -- 
> lenjaffe at jaffesystems.com   614-404-4214             
www.volunteerable.net
> Proprietor: http://www.theycomewithcheese.com/ - An Homage to Fromage
> Greenbar: Grubmaster: 2012-2009, Grub Asst: 2008, Trained: 2007.

This is the code with the prefetch commented out.
sub list :Local {
        my ($self, $c, $page) = @_;
        $page = $c->req->param('page') || 1;
        my $search = $c->req->param('search');
        $search =~ s/($search)/\U$1/gi;
        my $rs = $c->model('ORANGES::Account')->search_rs( { -or => [
                'me.account_code' => {'like', "%$search%" },
                'me.account_name' => {'like', "%$search%" }, 
        ] },{ 
        #prefetch => { account => [ { department => 'manager'}, { servers 
=> 'subs' }, 'metrics' ] },
        order_by => 'me.account_code',
        rows => 5,
        page => $page,
        });
        $c->stash(accounts => $rs);
        $c->stash(pager => $rs->pager());
        $c->stash->{'template'}=>'results/list';
}

which generates this SQL and gives me the account info minus the related 
data I normally get:

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 (
  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 (
    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 ? ) )
  ) me
) me WHERE rno__row__index >= ? AND rno__row__index <= ?

: '%11211%', '%11211%', '1', '5'
SELECT me.department_id, me.department_code, me.department_pid, 
me.manager_id FROM HCDB_TEST.DEPARTMENT me WHERE ( me.department_id = ? ): 
'170'
SELECT me.account_id, me.account_code, me.num_servers, me.num_subsystems 
FROM HCDB_TEST.METRICS me WHERE ( me.account_id = ? ): '2445'
SELECT me.account_id, me.account_code, me.num_servers, me.num_subsystems 
FROM HCDB_TEST.METRICS me WHERE ( me.account_id = ? ): '2445'

And if I un-comment it I get this sql and no results on page:

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 (
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 (
  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 (
    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 ? ) )
  ) me
) me WHERE rno__row__index >= ? AND rno__row__index <= ?

) 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%'
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20120619/837b811b/attachment-0001.htm