Hi Simon:
The reason to prepend CALC_FOUND_ROWS in MySQL is to be able to get the
total number of rows for a statement if you restrict with a limit for
pagination. Have a quick look at
http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_found-rows
to
see how it works.
For pagination it would be nice having a way to determine the number of rows
without the limit clause. Since Zend_Db handles multiple databases we would
need an implementation for each flavor.
As mentioned before, I suggested it before but didn't get much feedback on
it. And I didn't have the time to work on it myself.
Cheers,
Gunter
On Thu, Apr 17, 2008 at 9:01 PM, Simon Mundy <[EMAIL PROTECTED]>
wrote:
> Hi Mark
>
> Do I understand correctly that you just needed to use that function as a
> returning field?
>
> If so, did the following not work for you?
>
> $select->from('yourtable', array('line_items_per_product' => new
> Zend_Db_Expr('CALC_FOUND_ROWS')));
>
> Using a Zend_Db_Expr prevents a string from having further escaping
> applied to it.
>
> Cheers
>
>
> Well ... I played around with this and was able to hack up the Select
> > class
> > to put this functionality in. here it is if anyone is interested:
> >
> > http://www.mindfulinteractive.com/zend/Select.txt
> >
> > Basically you can now do something like
> >
> > $select->sqlCalcFoundRows();
> >
> > And it will add 'SQL_CALC_FOUND_ROWS' at the beginning of the sql
> > statement.
> > I don't know what will happen if you do both distinct and
> > sqlCalcFoundRows
> > ...
> >
> > I'm now trying to see if I can subclass it, but I'm not a OOP Master.
> > Here's
> > my attempt, but I can't quite seem to override $_partsInit with my
> > $_partsInit ...
> >
> > <?php
> >
> > class NewSelect extends Zend_Db_Select {
> > const CALC_ROWS = 'sqlCalcFoundRows';
> > const SQL_CALC_ROWS = 'SQL_CALC_FOUND_ROWS';
> >
> > /**
> > * The initial values for the $_parts array.
> > * NOTE: It is important for the 'FOR_UPDATE' part to be last to
> > ensure
> > * meximum compatibility with database adapters.
> > *
> > * @var array
> > */
> > protected static $_partsInit = array(
> > self::DISTINCT => false,
> > self::CALC_ROWS => false,
> > self::COLUMNS => array(),
> > self::FROM => array(),
> > self::WHERE => array(),
> > self::GROUP => array(),
> > self::HAVING => array(),
> > self::ORDER => array(),
> > self::LIMIT_COUNT => null,
> > self::LIMIT_OFFSET => null,
> > self::FOR_UPDATE => false
> > );
> >
> > public function sqlCalcFoundRows( $flag = true )
> > {
> > echo 'test';
> > $this->_parts[self::CALC_ROWS] = (bool) $flag;
> > return $this;
> > }
> >
> > protected function _renderSqlCalcFoundRows( $sql )
> > {
> > if( $this->_parts[self::CALC_ROWS]) {
> > $sql .= ' ' . self::SQL_CALC_ROWS ;
> > }
> >
> > return $sql;
> > }
> > }
> >
> > MS
> > -----Original Message-----
> > From: Shekar C Reddy [mailto:[EMAIL PROTECTED]
> > Sent: Thursday, April 17, 2008 1:41 PM
> > To: Gunter Sammet
> > Cc: Mark Steudel; James Dempster; [email protected]
> > Subject: Re: [fw-general] CALC_FOUND_ROWS vs. count(*)
> >
> > Creating a Jira issue would be ideal to keep a track of this task.
> >
> >
> >
> > On Thu, Apr 17, 2008 at 3:21 PM, Gunter Sammet <[EMAIL PROTECTED]>
> > wrote:
> >
> > > I ended up retrieving the SQL as string and do a string replace and
> > > then
> > > execute the SQL statement. Not very elegant but it solved my problem
> > > at
> > >
> > that
> >
> > > time. If you come up with a more elegant way, please let me know.
> > > Once I have time I'd like to add this as a functionality to the select
> > >
> > class
> >
> > > to allow for an DB independant way of doing this. I remember that I
> > > asked
> > >
> > in
> >
> > > the forum before and at that time it didn't seem to be very much
> > > required
> > > and for that reason didn't have much importance.
> > > HTH
> > >
> > > Gunter
> > >
> > >
> > >
> > > On Thu, Apr 17, 2008 at 2:07 PM, Mark Steudel <[EMAIL PROTECTED]>
> > > wrote:
> > >
> > >
> > > >
> > > >
> > > >
> > > > Well couple of problems,
> > > >
> > > >
> > > >
> > > > I can't mix a count() statement in my sql statement the whole mixing
> > > >
> > > group
> >
> > > columns with non group columns thing
> > >
> > > > Even if I try and trick it by putting at the begging the select
> > > > methods
> > > >
> > > quote everything, so it because something like:
> > >
> > > >
> > > > SELECT `SQL_CALC_FOUND_ROWS table`.`id` ..
> > > >
> > > >
> > > >
> > > > :/ Any other ideas?
> > > >
> > > >
> > > >
> > > > MS
> > > >
> > > >
> > > >
> > > > ________________________________
> > > >
> > >
> > >
> > > > From: James Dempster [mailto:[EMAIL PROTECTED]
> > > > Sent: Thursday, April 17, 2008 12:48 PM
> > > > To: Mark Steudel
> > > > Cc: [email protected]
> > > > Subject: Re: [fw-general] CALC_FOUND_ROWS vs. count(*)
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > Have you tried
> > > >
> > > > array('line_items_per_product' => 'CALC_FOUND_ROWS COUNT(*)'))
> > > >
> > > > I think that might work.
> > > >
> > > > --
> > > > /James
> > > >
> > > >
> > > > On Thu, Apr 17, 2008 at 8:06 PM, Mark Steudel <[EMAIL PROTECTED]>
> > > >
> > > wrote:
> >
> > >
> > > >
> > > >
> > > > Hey Guys,
> > > >
> > > >
> > > >
> > > > I was looking for a way to use CALC_FOUND_ROWS in mysql, I saw in
> > > > the
> > > >
> > > documentation you could get the same thing by doing this:
> > >
> > > >
> > > > array('line_items_per_product' => 'COUNT(*)'))
> > > >
> > > >
> > > >
> > > >
> > > > If I wanted to do CALC_FOUND_ROWS instead, how would I do it
> > > > utilizing
> > > >
> > > the
> >
> > > Zend_DB?
> > >
> > > >
> > > >
> > > >
> > > > Thanks, Mark
> > > >
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> --
>
> Simon Mundy | Director | PEPTOLAB
>
> """ " "" """""" "" "" """"""" " "" """"" " """"" " """""" "" "
>
> 202/258 Flinders Lane | Melbourne | Victoria | Australia | 3000
> Voice +61 (0) 3 9654 4324 | Mobile 0438 046 061 | Fax +61 (0) 3 9654 4124
> http://www.peptolab.com
>
>
