Mailing List Archive

On 2005-11-29 at 20:20:10 [+0100], Kevin Monceaux <Kevin@RawFedDogs.net>
wrote:
> Zope Enthusiasts,
>
> I'm new to Zope so forgive me if the answer to this question is obvious. If
> one performs a select with a limit clause is it possible to determine how
> many results would have been returned if there was no limit? For example,
> if I wanted to select the first picture in a photo album but wanted to know
> the total number of pictures in the album, how would I do that via Z SQL?
> With MySQL it can be done this way:
>
> SELECT SQL_CALC_FOUND_ROWS, * from Pictures where AlbumID = 25 LIMIT 1;
>
> SELECT FOUND_ROWS() as Count;
>
> According to the "Z SQL Methods User's Manual" multiple SQL statements can
> be included in a Z SQL method but that no more than one select statement can
> be included. The above MySQL technique requires two select statements.

You can use <dtml-var sqldelimiter> - search the archives for that.
But that is wrong. If you want to count the number of records, use count()

Charlie
--
Charlie Clark
eGenix.com

Professional Python Services directly from the Source
>>> Python/Zope Consulting and Support ... http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
________________________________________________________________________
2005-10-17: Released mxODBC.Zope.DA 1.0.9 http://zope.egenix.com/

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Charlie Clark wrote:
> You can use <dtml-var sqldelimiter> - search the archives for that.
> But that is wrong.

Why?

> If you want to count the number of records, use count()

That doesn't work if you use offsets and limits to only return part of a
result set...

cheers,

Chris

--
Simplistix - Content Management, Zope & Python Consulting
- http://www.simplistix.co.uk

_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Kevin Monceaux wrote at 2005-11-29 13:20 -0600:
>I'm new to Zope so forgive me if the answer to this question is obvious. If
>one performs a select with a limit clause is it possible to determine how
>many results would have been returned if there was no limit? For example,
>if I wanted to select the first picture in a photo album but wanted to know
>the total number of pictures in the album, how would I do that via Z SQL?
>With MySQL it can be done this way:
>
>SELECT SQL_CALC_FOUND_ROWS, * from Pictures where AlbumID = 25 LIMIT 1;
>
>SELECT FOUND_ROWS() as Count;
>
>According to the "Z SQL Methods User's Manual" multiple SQL statements can
>be included in a Z SQL method but that no more than one select statement can
>be included. The above MySQL technique requires two select statements.

If you really want to use this esoteric feature (someone else
already proposed a more standard alternative), you
can put the two selects in different Z SQL Methods and call them
one after the other...

--
Dieter
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

On 2005-11-30 at 20:06:21 [+0100], Chris Withers <chris@simplistix.co.uk>
wrote:
> Charlie Clark wrote:
> > You can use <dtml-var sqldelimiter> - search the archives for that.
> > But that is wrong.
>
> Why?
>
> > If you want to count the number of records, use count()
>
> That doesn't work if you use offsets and limits to only return part of a
> result set...

SELECT count(attribute) FROM relatiin WHERE condition

only returns 1 result so it's independent of LIMIT and result sets.

Of course, ZopeDAs such as our mxODBCZopeDA can be extended to support
additional commands on the connection but that is not part of the ZSQL
specification and will vary from RDBMS to RDBMS as the initial mail noted.

Charlie
--
Charlie Clark
eGenix.com

Professional Python Services directly from the Source
>>> Python/Zope Consulting and Support ... http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
________________________________________________________________________
2005-10-17: Released mxODBC.Zope.DA 1.0.9 http://zope.egenix.com/

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Charlie Clark wrote:
>>That doesn't work if you use offsets and limits to only return part of a
>>result set...
>
> SELECT count(attribute) FROM relatiin WHERE condition

Ah, okay, now I gotcha...

> only returns 1 result so it's independent of LIMIT and result sets.

I wonder how the two methods compare efficiency-wise?

> Of course, ZopeDAs such as our mxODBCZopeDA can be extended to support
> additional commands on the connection but that is not part of the ZSQL
> specification and will vary from RDBMS to RDBMS as the initial mail noted.

Well, that's why MySQL does it as an SQL statement, no? ;-)

Chris

--
Simplistix - Content Management, Zope & Python Consulting
- http://www.simplistix.co.uk

_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

On 2005-12-02 at 10:33:34 [+0100], Chris Withers <chris@simplistix.co.uk>
wrote:
> Charlie Clark wrote:
> >>That doesn't work if you use offsets and limits to only return part of a
> >>result set...
> >
> > SELECT count(attribute) FROM relatiin WHERE condition
>
> Ah, okay, now I gotcha...
>
> > only returns 1 result so it's independent of LIMIT and result sets.
>
> I wonder how the two methods compare efficiency-wise?

ah, that might well be in the realms of db-tuning and magic but one might
expect a count() operation to be slightly faster as it requires less data to
be packaged. The implementation of LIMITs and result sets might vary
significantly from system to system.

> > Of course, ZopeDAs such as our mxODBCZopeDA can be extended to support
> > additional commands on the connection but that is not part of the ZSQL
> > specification and will vary from RDBMS to RDBMS as the initial mail noted.
>
> Well, that's why MySQL does it as an SQL statement, no? ;-)

Maybe although I sometimes why MySQL does anything: the documentation seems
to imply that many decisions were sort of "let's do it like this": cf.
particularly the recommendations for writing queries with JOINs. Personally I
do not see it as valid SQL to predicate a query on "what was just asked".

Regarding the original question: if I know I am working with LIMITs or result
sets then this implies I don't want to know the total size of the results
beyound len(results). Of course, it can be a right Tony Blair rewriting a
complex SQL query just to add a count in there as opposed to calling a method
on a cursor object. And this is where ZopeDA connections could do with
improved support beyond the current "it's good enough as it is" situation so
that database functions can be called directly more easily where required.
But as we all know, so far no one has come forward to champion new generation
ZSQLs and ZopeDAs to do this.

Charlie
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Charlie Clark wrote:
>>>SELECT count(attribute) FROM relatiin WHERE condition
>>
>>Ah, okay, now I gotcha...
>>
>>>only returns 1 result so it's independent of LIMIT and result sets.
>>
>>I wonder how the two methods compare efficiency-wise?

Yes, well, we're talking about MySQL specifically ;-)

> Maybe although I sometimes why MySQL does anything: the documentation seems
> to imply that many decisions were sort of "let's do it like this": cf.
> particularly the recommendations for writing queries with JOINs. Personally I
> do not see it as valid SQL to predicate a query on "what was just asked".

Sorry, not a lot of this paragraph made sense :-S

> Regarding the original question: if I know I am working with LIMITs or result
> sets then this implies I don't want to know the total size of the results
> beyound len(results).

Not so. Think of batches:

"Now showing Results 5 - 10 of 25"

But why not just return all the rows and only show 5 of them?

"Now showing Results 5 - 10 of 250000"

;-)

Yes, it'll likely be a slower query anyway, but I can assure you, Zope
turning each of those rows into a Result object will be a LOT slower...

cheers,

Chris

--
Simplistix - Content Management, Zope & Python Consulting
- http://www.simplistix.co.uk

_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

On 2005-12-05 at 08:42:09 [+0100], Chris Withers <chris@simplistix.co.uk>
wrote:
> Charlie Clark wrote:
> >>>SELECT count(attribute) FROM relatiin WHERE condition
> >>
> >>Ah, okay, now I gotcha...
> >>
> >>>only returns 1 result so it's independent of LIMIT and result sets.
> >>
> >>I wonder how the two methods compare efficiency-wise?
>
> Yes, well, we're talking about MySQL specifically ;-)

Well, with reference to Zope we shouldn't be...

> > Maybe although I sometimes why MySQL does anything: the documentation
> > seems
> > to imply that many decisions were sort of "let's do it like this": cf.
> > particularly the recommendations for writing queries with JOINs.
> > Personally I
> > do not see it as valid SQL to predicate a query on "what was just asked".
>
> Sorry, not a lot of this paragraph made sense :-S

It still does to me. Have you had your morning tea? It's not that important.

> > Regarding the original question: if I know I am working with LIMITs or
> > result
> > sets then this implies I don't want to know the total size of the results
> > beyound len(results).
>
> Not so. Think of batches:
>
> "Now showing Results 5 - 10 of 25"
>
> But why not just return all the rows and only show 5 of them?
>
> "Now showing Results 5 - 10 of 250000"

Yes, let's talk about batches - ZSQL doesn't implement batching so it
collects the whole set of results available so len() is available. I agree
that this is likely to be inefficient for large results but it doesn't
require any additional calls. Or you do as Dieter suggested an run two calls
- first one simply counts and the second one effectively does batching.
Result sets aren't supported directly so unless the DA provides a method to
query on the connection or cursor you will also have to run a separate query
with count().

Which means: it would be nice if ZSQL supported batching, preferably by
result sets and this means the DAs have to expose more of the underlying
functionality and getting more information from the Python driver.

Charlie
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

Charlie Clark wrote:
>>>>I wonder how the two methods compare efficiency-wise?
>>
>>Yes, well, we're talking about MySQL specifically ;-)
>
> Well, with reference to Zope we shouldn't be...

Huh? the original poster was talking about MySQL, why should he care
about any other rdb?

>>Not so. Think of batches:
>>
>>"Now showing Results 5 - 10 of 25"
>>
>>But why not just return all the rows and only show 5 of them?
>>
>>"Now showing Results 5 - 10 of 250000"
>
> Yes, let's talk about batches - ZSQL doesn't implement batching so it
> collects the whole set of results available so len() is available.

How would you implement batching in a way that's agnostic of the rdb used?

> Result sets aren't supported directly

by what?

> so unless the DA provides a method to
> query on the connection or cursor you will also have to run a separate query
> with count().

*sigh*

cheers,

Chris

--
Simplistix - Content Management, Zope & Python Consulting
- http://www.simplistix.co.uk

_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

On 2005-12-06 at 10:48:29 [+0100], Chris Withers <chris@simplistix.co.uk>
wrote:
> Charlie Clark wrote:
> >>>>I wonder how the two methods compare efficiency-wise?
> >>
> >>Yes, well, we're talking about MySQL specifically ;-)
> >
> > Well, with reference to Zope we shouldn't be...
>
> Huh? the original poster was talking about MySQL, why should he care
> about any other rdb?

Well, he was talking about using ZSQL and cited by MySQL and MS SQL
statements. ie., he was interested in a generic approach. At least that's
how I read it.

> >>Not so. Think of batches:
> >>
> >>"Now showing Results 5 - 10 of 25"
> >>
> >>But why not just return all the rows and only show 5 of them?
> >>
> >>"Now showing Results 5 - 10 of 250000"
> >
> > Yes, let's talk about batches - ZSQL doesn't implement batching so it
> > collects the whole set of results available so len() is available.
>
> How would you implement batching in a way that's agnostic of the rdb used?

Currently I do this with a script but the LCD solution would be to use a
LIMITs based on parameters passed in. Essentially, however, the RDBMS has
to support the feature and the Python DB API should be cover it. Having had
to work with both MySQL and SQLite recently the compromises required to
support "lesser" systems can be really annoying.

> > Result sets aren't supported directly
>
> by what?

By ZopeDA's and ZSQL

> > so unless the DA provides a method to
> > query on the connection or cursor you will also have to run a separate
> > query
> > with count().
>
> *sigh*

I've not discovered this to be a real problem yet although my result sets
are rarely more than several thousand items long when ZPTs rendering speed
becomes the problem. One could imagine RDBMs support in Zope3 being so much
better if only anyone could be really bothered about it ;-)

Charlie
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db