Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP

Loading…

Difficulties with HAVING clauses in AR #135

Closed
qiangxue opened this Issue · 3 comments

5 participants

@qiangxue
Owner

What steps will reproduce the problem?

  1. Set up a CGridView using a CActiveDataProvider
  2. In the CADP, assign a CDbCriteria
  3. Set the CDbCriteria's having property to something that will constrain the data set

What is the expected output? What do you see instead?
I expect to see and page through the rows as specified. But CAR does not apply CDbCriteria::$having when counting rows. So the pager and count displays do not work properly.

Yii 1.1.9.r3527 OSX 10.7.2, FreeBSD 8.1, Gentoo 11

To demonstrate the problem and my workaround, please see https://github.com/tom--/SQActiveRecord
This is an attempt to allow the use of subqueries in CGVs with functional filtering and sorting. The files are based on a standard (1.1.9) yiic webapp. See how the addSubQueries method has to use a nasty work around calculate total item count which it returns to the user can pass it on to CADP::$totalItemCount..

Another difficulty related to the above is that there is nothing like CDbCriteria::compare() for HAVING clauses. Note how lines 125 through 129 are a fugly workaround for this. To make this easier for users, I propose adding a new method to CDbCriteria that does the equivalent of CDbCriteria::compare() but adds conditions to the having property OR add a parameter to CDbCriteria::compare() that alters its behavior so it adds conditions to the having property.

Migrated from http://code.google.com/p/yii/issues/detail?id=3152

@alexandernst

+1 for this issue

@klimov-paul klimov-paul referenced this issue from a commit in klimov-paul/yii
@klimov-paul klimov-paul Issue #135 has been added to CHANGELOG. ff226c8
@samdark samdark was assigned
@samdark samdark closed this
@Rupert-RR Rupert-RR referenced this issue from a commit in Rupert-RR/yii
Rupert-RR Added #135 back in changelog, which got lost somehow.. 2f7a799
@cebe cebe referenced this issue from a commit
@cebe cebe Merge branch 'Rupert-RR-2131-add-accept-header-parsing'
* Rupert-RR-2131-add-accept-header-parsing:
  php doc adjustments after #2132
  Yii code style correction
  Removed unnecessary spaces.
  Transferred data for unit tests from the test functions into data providers.
  Undo accidental permissions change on bootstrap.php
  Added unit test file for CHttpRequest for the methods parseAcceptHeader() and compareAcceptTypes(). Modified the regexp in parseAcceptHeader() to accept wildcards in the path. Modified the description of compareAcceptTypes() to better reflect the comparison result (higher preference returns lower value, so that most preferred is first in the array).
  parseAcceptHeader() function description tidy up.
  Typo corrections and code tidy up.
  Altered parseAcceptHeader() to use only one regexp. Thanks to Ka on StackExchange for this expression.
  Typo corrections
  Separated out parse and compare functions. Reduced regular expression count to 2 from 3. Modified MIME type array map structure.
  Added #135 back in changelog, which got lost somehow..
  Moved position of line to follow numerical order.
  Enh #2131: Added Accept header parsing to CHttpRequest to give an array of accepted types in order of preference
968ff9c
@PrplHaz4

I realize the first part of this issue has been resolved, but has any thought been given to the second part of tom's request:

Another difficulty related to the above is that there is nothing like CDbCriteria::compare() for HAVING clauses. Note how lines 125 through 129 are a fugly workaround for this. To make this easier for users, I propose adding a new method to CDbCriteria that does the equivalent of CDbCriteria::compare() but adds conditions to the having property OR add a parameter to CDbCriteria::compare() that alters its behavior so it adds conditions to the having property.

I have been working on a similar problem today, and ended up copying a bunch of code from the compare method. Seeing as the SQL support in Yii is so robust, I am surprised there is no equivalent method for compare with the HAVING clause.

The use case I'm working with is where I am searching on the sum of related columns - maybe there is an easier way to achieve this?

@saulfautley

The use case I'm working with is where I am searching on the sum of related columns - maybe there is an easier way to achieve this?

I've been battling with this exact thing for ages. Doesn't seem like there's any native support for this (yet). One workaround is documented here. As for me I've opted to use a customised CDbCriteria::compare() for HAVING conditions, since this really is the correct way to use aggregates in queries, as opposed to sub-queries. There are unfortunately quite a few issues with this method, so I'm not completely satisfied with it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.