Mailing List Archive: Catalyst: Users

wrong generated SQL

Index | Next | Previous | View Threaded

ksmclane at us

Jun 1, 2012, 9:55 AM

Post #1 of 6 (241 views)
Permalink

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.

matthias.zeichmann at gmail

Jun 1, 2012, 11:04 AM

Post #2 of 6 (232 views)
Permalink

Re: wrong generated SQL [In reply to]

On Fri, Jun 1, 2012 at 6:55 PM, Kenneth S Mclane <ksmclane [at] us> 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 [at] lists
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst [at] lists/
Dev site: http://dev.catalyst.perl.org/

ksmclane at us

Jun 1, 2012, 11:10 AM

Post #3 of 6 (233 views)
Permalink

Re: wrong generated SQL [In reply to]

Matthias Zeichmann <matthias.zeichmann [at] gmail> wrote on 06/01/2012
01:04:58 PM:

> From:
>
> Matthias Zeichmann <matthias.zeichmann [at] gmail>
>
> To:
>
> The elegant MVC web framework <catalyst [at] lists>
>
> 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 <ksmclane [at] us>
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.

matthias.zeichmann at gmail

Jun 1, 2012, 11:25 AM

Post #4 of 6 (233 views)
Permalink

Re: wrong generated SQL [In reply to]

On Fri, Jun 1, 2012 at 8:10 PM, Kenneth S Mclane <ksmclane [at] us> 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 [at] lists
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst [at] lists/
Dev site: http://dev.catalyst.perl.org/

ksmclane at us

Jun 1, 2012, 11:30 AM

Post #5 of 6 (232 views)
Permalink

Re: wrong generated SQL [In reply to]

Matthias Zeichmann <matthias.zeichmann [at] gmail> wrote on 06/01/2012
01:25:23 PM:

> From:
>
> Matthias Zeichmann <matthias.zeichmann [at] gmail>
>
> To:
>
> The elegant MVC web framework <catalyst [at] lists>
>
> 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 <ksmclane [at] us>
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.

fs5 at sanger

Jun 1, 2012, 4:23 PM

Post #6 of 6 (231 views)
Permalink

Re: wrong generated SQL [In reply to]

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.zeichmann [at] gmail> wrote on 06/01/2012
> 01:25:23 PM:
>
> > From:
> >
> > Matthias Zeichmann <matthias.zeichmann [at] gmail>
> >
> > To:
> >
> > The elegant MVC web framework <catalyst [at] lists>
> >
> > 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
> <ksmclane [at] us> 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 [at] lists
> Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
> Searchable archive: http://www.mail-archive.com/catalyst [at] lists/
> 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 [at] lists
Listinfo: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst [at] lists/
Dev site: http://dev.catalyst.perl.org/

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads