Search_rs not returning newly created rows

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
25 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Search_rs not returning newly created rows

Kenneth S Mclane
I have succeeded in creating a new row in my table with "->create". I then go back to my page that lists all the rows in that table, and I get the list using "search_rs", but the newly created rows are not showing. I verified that the rows do exist. I'm not sure why this isn't working. Is there something I need to do to make it pull a fresh copy of the data? I thought it was automatic, but it doesn't seem to be.

Regards

Kenneth McLane  700 Locust St
Systems Compliance Services  Dubuque, 52001-6838
I1OB  USA
GTS Services Delivery  
Phone: +1-563-845-4674  
Tie-Line: 946-4674  
Mobile: +1-563-940-7147  
e-mail: [hidden email]  

"Ideas come from everything" -- Alfred Hitchcock
 
 


_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Search_rs not returning newly created rows

Len Jaffe


On Tue, Jun 19, 2012 at 11:27 AM, Kenneth S Mclane <[hidden email]> wrote:
I have succeeded in creating a new row in my table with "->create". I then go back to my page that lists all the rows in that table, and I get the list using "search_rs", but the newly created rows are not showing. I verified that the rows do exist. I'm not sure why this isn't working. Is there something I need to do to make it pull a fresh copy of the data? I thought it was automatic, but it doesn't seem to be.

1) Caching enabled?
  
2) You can set the DBI_TRACE  env variable to 1 and make sure that the query that's being run against the DB should be finding the new record.

Len.


--
[hidden email]   614-404-4214             www.volunteerable.net
Proprietor: http://www.theycomewithcheese.com/ - An Homage to Fromage
Greenbar: Grubmaster: 2012-2009, Grub Asst: 2008, Trained: 2007.



_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Search_rs not returning newly created rows

Kenneth S Mclane

Yes, I have done that but it doesn't show anything that would stop it from showing the new rows. I now have 80 rows in the table and I only get 66 back. Could the relationships be limiting the rows returned? I think I'll look inot that, I may need to create some related records.


Len Jaffe <[hidden email]> wrote on 06/19/2012 11:03:43 AM:

> From:

>
> Len Jaffe <[hidden email]>

>
> To:

>
> "DBIx::Class user and developer list" <[hidden email]>

>
> Date:

>
> 06/19/2012 11:09 AM

>
> Subject:

>
> Re: [Dbix-class] Search_rs not returning newly created rows

>
>

> On Tue, Jun 19, 2012 at 11:27 AM, Kenneth S Mclane <[hidden email]
> > wrote:

> I have succeeded in creating a new row in my table with "->create".
> I then go back to my page that lists all the rows in that table, and
> I get the list using "search_rs", but the newly created rows are not
> showing. I verified that the rows do exist. I'm not sure why this
> isn't working. Is there something I need to do to make it pull a
> fresh copy of the data? I thought it was automatic, but it doesn't seem to be.

>
> 1) Caching enabled?

>   
> 2) You can set the DBI_TRACE  env variable to 1 and make sure that
> the query that's being run against the DB should be finding the new record.

>
> Len.

>
> --
> [hidden email]   614-404-4214             
www.volunteerable.net
> Proprietor: http://www.theycomewithcheese.com/ - An Homage to Fromage
> Greenbar: Grubmaster: 2012-2009, Grub Asst: 2008, Trained: 2007.
>
> _______________________________________________
> 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@...
_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Search_rs not returning newly created rows

Len Jaffe


On Tue, Jun 19, 2012 at 12:15 PM, Kenneth S Mclane <[hidden email]> wrote:

Yes, I have done that but it doesn't show anything that would stop it from showing the new rows. I now have 80 rows in the table and I only get 66 back. Could the relationships be limiting the rows returned? I think I'll look inot that, I may need to create some related records.

Related records sounds like a promising avenue of inquiry.
Easy way to test that hypothesis is to copy the query to SQL and see what that result set looks like.
 L.
--
[hidden email]   614-404-4214             www.volunteerable.net
Proprietor: http://www.theycomewithcheese.com/ - An Homage to Fromage
Greenbar: Grubmaster: 2012-2009, Grub Asst: 2008, Trained: 2007.



_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Search_rs not returning newly created rows

Moritz Lenz
In reply to this post by Kenneth S Mclane
On 06/19/2012 05:27 PM, Kenneth S Mclane wrote:
> I have succeeded in creating a new row in my table with "->create". I
> then go back to my page that lists all the rows in that table, and I get
> the list using "search_rs", but the newly created rows are not showing.
> I verified that the rows do exist. I'm not sure why this isn't working.

Maybe an uncommitted transaction somewhere?
Does your database have the facility to list open transactions? If yes,
do that while the program is running; maybe you'll find something of
interest.

Cheers,
Moritz

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Search_rs not returning newly created rows

Kenneth S Mclane

Moritz Lenz <[hidden email]> wrote on 06/19/2012 01:16:42 PM:

> From:

>
> Moritz Lenz <[hidden email]>

>
> To:

>
> [hidden email]

>
> Date:

>
> 06/19/2012 01:20 PM

>
> Subject:

>
> Re: [Dbix-class] Search_rs not returning newly created rows

>
> On 06/19/2012 05:27 PM, Kenneth S Mclane wrote:
> > I have succeeded in creating a new row in my table with "->create". I
> > then go back to my page that lists all the rows in that table, and I get
> > the list using "search_rs", but the newly created rows are not showing.
> > I verified that the rows do exist. I'm not sure why this isn't working.
>
> Maybe an uncommitted transaction somewhere?
> Does your database have the facility to list open transactions? If yes,
> do that while the program is running; maybe you'll find something of
> interest.
>
> Cheers,
> Moritz
>
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.

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Search_rs not returning newly created rows

Len Jaffe


On Tue, Jun 19, 2012 at 2:58 PM, Kenneth S Mclane <[hidden email]> wrote:

Moritz Lenz <[hidden email]> 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.

--
[hidden email]   614-404-4214             www.volunteerable.net
Proprietor: http://www.theycomewithcheese.com/ - An Homage to Fromage
Greenbar: Grubmaster: 2012-2009, Grub Asst: 2008, Trained: 2007.



_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Search_rs not returning newly created rows

Kenneth S Mclane
Len Jaffe <[hidden email]> wrote on 06/19/2012 02:06:21 PM:

> From:

>
> Len Jaffe <[hidden email]>

>
> To:

>
> "DBIx::Class user and developer list" <[hidden email]>

>
> 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 <[hidden email]> wrote:
>
> Moritz Lenz <[hidden email]> 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.

>
> --
> [hidden email]   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%'
_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Search_rs not returning newly created rows

Len Jaffe


On Tue, Jun 19, 2012 at 3:22 PM, Kenneth S Mclane <[hidden email]> wrote:
Len Jaffe <[hidden email]> wrote on 06/19/2012 02:06:21 PM:

> 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.

>

This is the code with the prefetch commented out.  

We were right. The prefetch is generating INNER JOINS, using the old grammar, rather than the JOIN grammer, so if you do not have the related records, you will not get the "master" records.

select *
from foo, bar
where foo.id = bar.foo_id  

will only return records where the join condition (foo.id = bar.foo_id) is true.  But 

select *
from foo
left outer join bar on foo.id = bar.foo_id

will return a row for every row of foo and will return the data from bar where the join condition is true, and tuples full of NULLs where there is not bar row that satisfies the join condition.

I'm going to punt here, and announce that implementing the outer join in DBIX/SQLA is left as an exercise for the reader.

But try the two queries above (modified for your database) from a SQL prompt and compare & contrast the results.

Len.
- depending on your database, you might need to select foo.*, bar.* rather than just the generic *. in order to get the data from both tables.


Len. 

--
[hidden email]   614-404-4214             www.volunteerable.net
Proprietor: http://www.theycomewithcheese.com/ - An Homage to Fromage
Greenbar: Grubmaster: 2012-2009, Grub Asst: 2008, Trained: 2007.



_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Search_rs not returning newly created rows

Kenneth S Mclane
Len Jaffe <[hidden email]> wrote on 06/19/2012 03:45:34 PM:

> From:

>
> Len Jaffe <[hidden email]>

>
> To:

>
> "DBIx::Class user and developer list" <[hidden email]>

>
> Date:

>
> 06/19/2012 03:49 PM

>
> Subject:

>
> Re: [Dbix-class] Search_rs not returning newly created rows

>
>

> On Tue, Jun 19, 2012 at 3:22 PM, Kenneth S Mclane <[hidden email]> wrote:
> Len Jaffe <[hidden email]> wrote on 06/19/2012 02:06:21 PM:

> > 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.
> >

> This is the code with the prefetch commented out.  
>
> We were right. The prefetch is generating INNER JOINS, using the old
> grammar, rather than the JOIN grammer, so if you do not have the
> related records, you will not get the "master" records.

>
> select *

> from foo, bar
> where foo.id = bar.foo_id  
>
> will only return records where the join condition (foo.id =
> bar.foo_id) is true.  But 

>
> select *

> from foo
> left outer join bar on foo.id = bar.foo_id
>
> will return a row for every row of foo and will return the data from
> bar where the join condition is true, and tuples full of NULLs where
> there is not bar row that satisfies the join condition.

>
> I'm going to punt here, and announce that implementing the outer
> join in DBIX/SQLA is left as an exercise for the reader.

>
> But try the two queries above (modified for your database) from a
> SQL prompt and compare & contrast the results.

>
> Len.

> - depending on your database, you might need to select foo.*, bar.*
> rather than just the generic *. in order to get the data from both tables.

>
> Len. 

>
> --
> [hidden email]   614-404-4214             
www.volunteerable.net
> Proprietor: http://www.theycomewithcheese.com/ - An Homage to Fromage
> Greenbar: Grubmaster: 2012-2009, Grub Asst: 2008, Trained: 2007.
>
Strangely, I found that without the prefetch I am getting all the data, it's just that the columns are null as you said. It seems to do the left outer join when left to it's own devices. I can still access the fields in the related tables from my templates. I just added some template code to compensate for the null fields.

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Search_rs not returning newly created rows

Len Jaffe


On Tue, Jun 19, 2012 at 4:57 PM, Kenneth S Mclane <[hidden email]> wrote:
Len Jaffe <[hidden email]> wrote on 06/19/2012 03:45:34 PM:

>
Strangely, I found that without the prefetch I am getting all the data, it's just that the columns are null as you said. It seems to do the left outer join when left to it's own devices. I can still access the fields in the related tables from my templates. I just added some template code to compensate for the null fields.


I  an saddened by the fact that you're making database calls from your templates. 
I am consoled by the fact that I don't have to support it :-) 
Glad we could find a solution.
L.

--
[hidden email]   614-404-4214             www.volunteerable.net
Proprietor: http://www.theycomewithcheese.com/ - An Homage to Fromage
Greenbar: Grubmaster: 2012-2009, Grub Asst: 2008, Trained: 2007.



_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Search_rs not returning newly created rows

Kenneth S Mclane


Len Jaffe <[hidden email]> wrote on 06/19/2012 04:29:53 PM:

> From:

>
> Len Jaffe <[hidden email]>

>
> To:

>
> "DBIx::Class user and developer list" <[hidden email]>

>
> Date:

>
> 06/19/2012 04:32 PM

>
> Subject:

>
> Re: [Dbix-class] Search_rs not returning newly created rows

>
>

> On Tue, Jun 19, 2012 at 4:57 PM, Kenneth S Mclane <[hidden email]> wrote:
> Len Jaffe <[hidden email]> wrote on 06/19/2012 03:45:34 PM:
>
> >
> Strangely, I found that without the prefetch I am getting all the
> data, it's just that the columns are null as you said. It seems to
> do the left outer join when left to it's own devices. I can still
> access the fields in the related tables from my templates. I just
> added some template code to compensate for the null fields.

> I  an saddened by the fact that you're making database calls from
> your templates. 

> I am consoled by the fact that I don't have to support it :-) 
> Glad we could find a solution.
> L.
>
> --
> [hidden email]   614-404-4214             
www.volunteerable.net
> Proprietor: http://www.theycomewithcheese.com/ - An Homage to Fromage
> Greenbar: Grubmaster: 2012-2009, Grub Asst: 2008, Trained: 2007.
>
Wow, way to jump to a conclusion. I am not making a DB call from my template, I just added an IF so if the field is null it displays a "0" instead of nothing, just a readability issue. :-)

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Search_rs not returning newly created rows

Jorge Gonzalez
Completely offtopic by now, but have you tried this:

[% var || '0' %]

instead of

[% IF var %]
[%   var %]
[% ELSE %]
[%   '0' %]
[% END %]

??

(just guessing that you are using Template Toolkit...)

BR
J.



El 20/06/12 14:53, Kenneth S Mclane escribió:


Len Jaffe [hidden email] wrote on 06/19/2012 04:29:53 PM:

> From:

>
> Len Jaffe [hidden email]

>
> To:

>
> "DBIx::Class user and developer list" [hidden email]

>
> Date:

>
> 06/19/2012 04:32 PM

>
> Subject:

>
> Re: [Dbix-class] Search_rs not returning newly created rows

>
>

> On Tue, Jun 19, 2012 at 4:57 PM, Kenneth S Mclane [hidden email] wrote:
> Len Jaffe [hidden email] wrote on 06/19/2012 03:45:34 PM:
>
> >
> Strangely, I found that without the prefetch I am getting all the
> data, it's just that the columns are null as you said. It seems to
> do the left outer join when left to it's own devices. I can still
> access the fields in the related tables from my templates. I just
> added some template code to compensate for the null fields.

> I  an saddened by the fact that you're making database calls from
> your templates. 

> I am consoled by the fact that I don't have to support it :-) 
> Glad we could find a solution.
> L.
>
> --
> [hidden email]   614-404-4214             
www.volunteerable.net
> Proprietor: http://www.theycomewithcheese.com/ - An Homage to Fromage
> Greenbar: Grubmaster: 2012-2009, Grub Asst: 2008, Trained: 2007.
>
Wow, way to jump to a conclusion. I am not making a DB call from my template, I just added an IF so if the field is null it displays a "0" instead of nothing, just a readability issue. :-)


_______________________________________________
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@...



_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Search_rs not returning newly created rows

Kenneth S Mclane

I'll give that a try, certainly cleaner. Thanks.


Jorge Gonzalez <[hidden email]> wrote on 06/20/2012 08:19:03 AM:

> From:

>
> Jorge Gonzalez <[hidden email]>

>
> To:

>
> [hidden email]

>
> Date:

>
> 06/20/2012 08:24 AM

>
> Subject:

>
> Re: [Dbix-class] Search_rs not returning newly created rows

>
> Completely offtopic by now, but have you tried this:
>
> [% var || '0' %]
>
> instead of
>
> [% IF var %]
> [%   var %]
> [% ELSE %]
> [%   '0' %]
> [% END %]
>
> ??
>
> (just guessing that you are using Template Toolkit...)
>
> BR
> J.
>

>
> El 20/06/12 14:53, Kenneth S Mclane escribió:

>
>
> Len Jaffe <[hidden email]> wrote on 06/19/2012 04:29:53 PM:
>
> > From:
> >
> > Len Jaffe <[hidden email]>
> >
> > To:
> >
> > "DBIx::Class user and developer list" <[hidden email]>
> >
> > Date:
> >
> > 06/19/2012 04:32 PM
> >
> > Subject:
> >
> > Re: [Dbix-class] Search_rs not returning newly created rows
> >
> >
>
> > On Tue, Jun 19, 2012 at 4:57 PM, Kenneth S Mclane <[hidden email]>
> wrote:
> > Len Jaffe <[hidden email]> wrote on 06/19/2012 03:45:34 PM:
> >
> > >
> > Strangely, I found that without the prefetch I am getting all the
> > data, it's just that the columns are null as you said. It seems to
> > do the left outer join when left to it's own devices. I can still
> > access the fields in the related tables from my templates. I just
> > added some template code to compensate for the null fields.
>
> > I  an saddened by the fact that you're making database calls from
> > your templates. 
> > I am consoled by the fact that I don't have to support it :-) 
> > Glad we could find a solution.
> > L.
> >
> > --
> > [hidden email]   614-404-4214             
www.volunteerable.net
> > Proprietor:
http://www.theycomewithcheese.com/ - An Homage to Fromage
> > Greenbar: Grubmaster: 2012-2009, Grub Asst: 2008, Trained: 2007.
> >
> Wow, way to jump to a conclusion. I am not making a DB call from my
> template, I just added an IF so if the field is null it displays a
> "0" instead of nothing, just a readability issue. :-)

> _______________________________________________
> 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@...
>
> _______________________________________________
> 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@...
_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Search_rs not returning newly created rows

Len Jaffe
In reply to this post by Kenneth S Mclane


On Wed, Jun 20, 2012 at 8:53 AM, Kenneth S Mclane <[hidden email]> wrote:
Len Jaffe <[hidden email]> wrote on 06/19/2012 04:29:53 PM:
 
Wow, way to jump to a conclusion. I am not making a DB call from my template, I just added an IF so if the field is null it displays a "0" instead of nothing, just a readability issue. :-)
 
Sorry if you think I jumped anywhere.  
I assure you my conclusion was well-reasoned and reached via careful deliberation.

L
--
[hidden email]   614-404-4214             www.volunteerable.net
Proprietor: http://www.theycomewithcheese.com/ - An Homage to Fromage
Greenbar: Grubmaster: 2012-2009, Grub Asst: 2008, Trained: 2007.



_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Search_rs not returning newly created rows

Benjamin Hitz-2
Not to get in the way here... but I think (not a Dbix::Class/Catalyst expert) that Len is saying that:

putting {accounts => $rs and pager => $rs->pager()}in the stash is effectively forcing the template system to make DB calls.  It has to be even though Kenneth didn't actually post his template code.

At first I thought it was just the pager, but I re-read and saw the $rs itself was passed.

But I am not posting this just to pile on (and in fact, I could well be wrong -- or correct materially but wrong in details)... but I want to ask Len:

What is bad about this design?   Especially in a case where the pager (possibly not this one) has to fetch pages?   

Is the (your) preferred design pattern to put any UI fetches behind a webservice/ajax call?  

Thanks,
Ben


On Jun 20, 2012, at 8:04 AM, Len Jaffe wrote:

On Wed, Jun 20, 2012 at 8:53 AM, Kenneth S Mclane <[hidden email]> wrote:
Len Jaffe <[hidden email]> wrote on 06/19/2012 04:29:53 PM:
 
Wow, way to jump to a conclusion. I am not making a DB call from my template, I just added an IF so if the field is null it displays a "0" instead of nothing, just a readability issue. :-)
 
Sorry if you think I jumped anywhere.  
I assure you my conclusion was well-reasoned and reached via careful deliberation.

L


--
Ben Hitz
Senior Scientific Programmer ** Saccharomyces Genome Database ** GO Consortium
Stanford University ** [hidden email]




_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Search_rs not returning newly created rows

Len Jaffe


On Wed, Jun 20, 2012 at 12:06 PM, Benjamin Hitz <[hidden email]> wrote:
Not to get in the way here... but I think (not a Dbix::Class/Catalyst expert) that Len is saying that:
putting {accounts => $rs and pager => $rs->pager()}in the stash is effectively forcing the template system to make DB calls.  It has to be even though Kenneth didn't actually post his template code.
That was my well reasoned conclusion :-)
 
At first I thought it was just the pager, but I re-read and saw the $rs itself was passed.
But I am not posting this just to pile on (and in fact, I could well be wrong -- or correct materially but wrong in details)... but I want to ask Len:
What is bad about this design?   Especially in a case where the pager (possibly not this one) has to fetch pages?   
Is the (your) preferred design pattern to put any UI fetches behind a webservice/ajax call?  

Controller uses the model to manipulate and retrieve all the data,  crams it into the stash, and hands the stash to the the view for presentation to the end user.

While it feels cleaner to gram a result set and iterate over it in the template, it totally violates MVC. View is for presentation, Model is about the data and business logic.  

While I've been know to violate this convention once or twice myself, I think that retrieve multiple related records for each master record, in the view, is the kind of violation that will cause maintenance nightmares.  Since code spends 80-90 percent of its life in maintenance mode, coding for maintainability is a key goal of mine (again, I fall off that horse occasionally -- all things in moderation).

Experience is why I say I dislike the idea of doing it that way.  It will certainly work. It might event be faster to develop that way, because you won't have to retrieve all the data ahead of time, and structure it so that the view can access it efficiently.

I have similar conversations with folks I know who are new to application maintenance, and who are doing the same thing with Rails/ActiveRecord/HAML/ERB.

Pager: pagers should not be retrieving rows, they should be calculating which rowset parameters to send to the controller to get the new records they want.
 
L.
--
[hidden email]   614-404-4214             www.volunteerable.net
Proprietor: http://www.theycomewithcheese.com/ - An Homage to Fromage
Greenbar: Grubmaster: 2012-2009, Grub Asst: 2008, Trained: 2007.



_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Search_rs not returning newly created rows

Kenneth S Mclane
In reply to this post by Benjamin Hitz-2
From what I understand from the books and docs etc, I am putting the results of a DB call into the stash, my template code just loops through what is stored in the stash. The pager does go back to the db, but I don't know of any other way to do it. I'm just following the docs and that's what they show. If there is a better way I'd be happy to work on that.

Benjamin Hitz <[hidden email]> wrote on 06/20/2012 11:06:57 AM:

> From:

>
> Benjamin Hitz <[hidden email]>

>
> To:

>
> "DBIx::Class user and developer list" <[hidden email]>

>
> Date:

>
> 06/20/2012 11:34 AM

>
> Subject:

>
> Re: [Dbix-class] Search_rs not returning newly created rows

>
> Not to get in the way here... but I think (not a Dbix::Class/
> Catalyst expert) that Len is saying that:

>
> putting {accounts => $rs and pager => $rs->pager()}in the stash is
> effectively forcing the template system to make DB calls.  It has to
> be even though Kenneth didn't actually post his template code.

>
> At first I thought it was just the pager, but I re-read and saw the
> $rs itself was passed.

>
> But I am not posting this just to pile on (and in fact, I could well
> be wrong -- or correct materially but wrong in details)... but I
> want to ask Len:

>
> What is bad about this design?   Especially in a case where the
> pager (possibly not this one) has to fetch pages?  

>
> Is the (your) preferred design pattern to put any UI fetches behind
> a webservice/ajax call?  

>
> Thanks,

> Ben
>
> On Jun 20, 2012, at 8:04 AM, Len Jaffe wrote:

>
> On Wed, Jun 20, 2012 at 8:53 AM, Kenneth S Mclane <[hidden email]> wrote:

> Len Jaffe <[hidden email]> wrote on 06/19/2012 04:29:53 PM:
>  
> Wow, way to jump to a conclusion. I am not making a DB call from my
> template, I just added an IF so if the field is null it displays a
> "0" instead of nothing, just a readability issue. :-)

>  
> Sorry if you think I jumped anywhere.  
> I assure you my conclusion was well-reasoned and reached via careful
> deliberation.

>
> L

>
> --

> Ben Hitz
> Senior Scientific Programmer ** Saccharomyces Genome Database ** GO Consortium
> Stanford University ** [hidden email]
>
> _______________________________________________
> 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@...
_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Search_rs not returning newly created rows

Bill Moseley


On Wed, Jun 20, 2012 at 11:51 AM, Kenneth S Mclane <[hidden email]> wrote:
From what I understand from the books and docs etc, I am putting the results of a DB call into the stash, my template code just loops through what is stored in the stash. The pager does go back to the db, but I don't know of any other way to do it. I'm just following the docs and that's what they show. If there is a better way I'd be happy to work on that.

Any chance you can put together a complete (and very simple) example that shows what you are seeing?  Should be able to do it with just a few files w/o Catalyst and include code to populate the database.  Then put that some place we can fetch and test -- assuming just the act of doing that doesn't resolve the issue.

I'd worry less about where your database calls are happening.  Do you really care if [% user.address.city | html %] hits the database in the template or in the controller?  One point of the object abstraction is you don't care about the implementation.   The real problem I've seen, though, is doing something stupid like huge nested loops in the template where each one generates a query.  Watch the logs.  Having an ORM doesn't mean you don't pay attention.

 
--
Bill Moseley
[hidden email]

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|

Re: Search_rs not returning newly created rows

Len Jaffe


On Wed, Jun 20, 2012 at 2:39 PM, Bill Moseley <[hidden email]> wrote:

I'd worry less about where your database calls are happening.  Do you really care if [% user.address.city | html %] hits the database in the template or in the controller?  
Well, not caring where this happens tends to lead to the what you describe next.  Why not put the address data in the stash? The model knows it will need to display it.
 
One point of the object abstraction is you don't care about the implementation.   The real problem I've seen, though, is doing something stupid like huge nested loops in the template where each one generates a query.
This is what I meant to about maintenance nightmare. 

I've inherited apps like this where I had to roll nested queries (in templates) up into a single query to go from 120 round-trips to the database, taking 8 seconds, into one round trip, taking half a second.

L.


--
[hidden email]   614-404-4214             www.volunteerable.net
Proprietor: http://www.theycomewithcheese.com/ - An Homage to Fromage
Greenbar: Grubmaster: 2012-2009, Grub Asst: 2008, Trained: 2007.



_______________________________________________
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@...
12