[Catalyst] wrong generated SQL
Can someone tell me why this: my $rs1 = $c-model('ORANGES::File')-search({'report_id' = $rptcode}, { rows = 1, page = $page, }); Results in the following SQL? SELECT COUNT( * ) FROM HCDB_TEST.FILE me WHERE ( report_id = ? ): '541861' When it used to create SQL that actually pulled the fields. It was working and I have had to re-create several of my template files which is how I discovered this. I made no changes to this code and suddenly it is generating this count(*) instead of getting the columns. ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] wrong generated SQL
On Fri, Jun 1, 2012 at 6:55 PM, Kenneth S Mclane ksmcl...@us.ibm.com wrote: Can someone tell me why this: my $rs1 = $c-model('ORANGES::File')-search({'report_id' = $rptcode}, { rows = 1, page = $page, }); Results in the following SQL? SELECT COUNT( * ) FROM HCDB_TEST.FILE me WHERE ( report_id = ? ): '541861' When it used to create SQL that actually pulled the fields. It was working and I have had to re-create several of my template files which is how I discovered this. I made no changes to this code and suddenly it is generating this count(*) instead of getting the columns. you probably used something like .all, .first or a while loop with .next on your resultset in your template. unless you do so again, no actual results are going to be fetched from your database you might just as well use find instead of search if you are only going for one row https://metacpan.org/module/DBIx::Class::ResultSet cheers m -- siggen.pl: Segmentation Fault ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] wrong generated SQL
Matthias Zeichmann matthias.zeichm...@gmail.com wrote on 06/01/2012 01:04:58 PM: From: Matthias Zeichmann matthias.zeichm...@gmail.com To: The elegant MVC web framework catalyst@lists.scsys.co.uk Date: 06/01/2012 01:07 PM Subject: Re: [Catalyst] wrong generated SQL On Fri, Jun 1, 2012 at 6:55 PM, Kenneth S Mclane ksmcl...@us.ibm.com wrote: Can someone tell me why this: my $rs1 = $c-model('ORANGES::File')-search({'report_id' = $rptcode}, { rows = 1, page = $page, }); Results in the following SQL? SELECT COUNT( * ) FROM HCDB_TEST.FILE me WHERE ( report_id = ? ): '541861' When it used to create SQL that actually pulled the fields. It was working and I have had to re-create several of my template files which is how I discovered this. I made no changes to this code and suddenly it is generating this count(*) instead of getting the columns. you probably used something like .all, .first or a while loop with .next on your resultset in your template. unless you do so again, no actual results are going to be fetched from your database you might just as well use find instead of search if you are only going for one row https://metacpan.org/module/DBIx::Class::ResultSet cheers m -- siggen.pl: Segmentation Fault I did use .next in my template, but this code should (and did) return 4 rows. I am also using a pager on the resultset, which still shows 4 pages when I display the template, however I get no column data, just the count. ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] wrong generated SQL
On Fri, Jun 1, 2012 at 8:10 PM, Kenneth S Mclane ksmcl...@us.ibm.com wrote: I did use .next in my template, but this code should (and did) return 4 rows. I am also using a pager on the resultset, which still shows 4 pages when I display the template, however I get no column data, just the count. did make sure the block of your template with the while loop actually gets to run? -- siggen.pl: Segmentation Fault ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] wrong generated SQL
Matthias Zeichmann matthias.zeichm...@gmail.com wrote on 06/01/2012 01:25:23 PM: From: Matthias Zeichmann matthias.zeichm...@gmail.com To: The elegant MVC web framework catalyst@lists.scsys.co.uk Date: 06/01/2012 01:26 PM Subject: Re: [Catalyst] wrong generated SQL On Fri, Jun 1, 2012 at 8:10 PM, Kenneth S Mclane ksmcl...@us.ibm.com wrote: I did use .next in my template, but this code should (and did) return 4 rows. I am also using a pager on the resultset, which still shows 4 pages when I display the template, however I get no column data, just the count. did make sure the block of your template with the while loop actually gets to run? -- siggen.pl: Segmentation Fault As far as I know it does, I did have to create the template page again, but I have been doing it all day with other pages. The only problem I see is the SQL is all of a sudden doing a count(*) instead of pulling the fields. ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/
Re: [Catalyst] wrong generated SQL
The count query is issued to get a total row count for the pager. In your template, is it possible that you currently just display the number of results from the pager instead of the actual resultset data? As soon as you ask the resultset for data you should see another query being issued for the row data. What does your template code look like? On 01/06/12 19:30, Kenneth S Mclane wrote: Matthias Zeichmann matthias.zeichm...@gmail.com wrote on 06/01/2012 01:25:23 PM: From: Matthias Zeichmann matthias.zeichm...@gmail.com To: The elegant MVC web framework catalyst@lists.scsys.co.uk Date: 06/01/2012 01:26 PM Subject: Re: [Catalyst] wrong generated SQL On Fri, Jun 1, 2012 at 8:10 PM, Kenneth S Mclane ksmcl...@us.ibm.com wrote: I did use .next in my template, but this code should (and did) return 4 rows. I am also using a pager on the resultset, which still shows 4 pages when I display the template, however I get no column data, just the count. did make sure the block of your template with the while loop actually gets to run? -- siggen.pl: Segmentation Fault As far as I know it does, I did have to create the template page again, but I have been doing it all day with other pages. The only problem I see is the SQL is all of a sudden doing a count(*) instead of pulling the fields. ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/ -- The Wellcome Trust Sanger Institute is operated by Genome Research Limited, a charity registered in England with number 1021457 and a company registered in England with number 2742969, whose registered office is 215 Euston Road, London, NW1 2BE. ___ List: Catalyst@lists.scsys.co.uk Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst Searchable archive: http://www.mail-archive.com/catalyst@lists.scsys.co.uk/ Dev site: http://dev.catalyst.perl.org/