Hello everyone,

I am a noob. I am facing a problem. In one of my module, i want to create a sql query for a dbtable connected with node table.

Assuming dbtable is name of sql table i want to search. There are columns nid, time, uid, number
I want to write a sql query
SELECT dbtable.time AS dbtable_time, dbtable.uid AS dbtable_uid, dbtable.number AS dbtable_number, count(dbtable.number) AS dbtable_count from node inner join dbtable ON dbtable.nid = node.nid GROUP BY dbtable_number, dbtable_uid ORDER BY dbtable_time DESC

I have added time, uid, amount, and count(Amount) as fields and sort criteria as time desc. But the query i get is:
SELECT dbtable.time AS dbtable_time, dbtable.uid AS dbtable_uid, dbtable.number AS dbtable_number, count(dbtable.number) AS dbtable_count from node inner join dbtable ON dbtable.nid = node.nid GROUP BY dbtable_number, dbtable_uid, dbtable_time, dbtable_number ORDER BY dbtable_time DESC

which gives erroneous results for count(number) and gives undesired results. Then i searched some issues already posted here. Then i found query.inc line 956: $groupby = "GROUP BY " . implode(', ', array_unique(array_merge($this->groupby, $non_aggregates))) . "\n"; I have to delete $non_aggregates and it worked except for dbtable_time. It was still there in GROUP BY statement. I tried exploring query.inc myself. in function add_orderby from line 824 some lines of code are:
// If grouping, all items in the order by must also be in the
// group by clause. Check $table to ensure that this is not a
// formula.
if ($this->groupby && $table) {
$this->add_groupby($as);
}
I deleted this piece of code and it solved my problem. Here i am confused first why do need to put item of add_orderby array in groupby array. I mean why we are making this thing compulsory.

My problem is i don't want to hack views module for one particular view to work. Please somebody give some advice how to do it in right way.

I would appreciate any help. Thanks

Comments

merlinofchaos’s picture

merlinofchaos commented
Status: Active » Closed (works as designed)

ANSI SQL requres this, and postgres enforces that rule. Therefore Views must do this.

shenagarg’s picture

shenagarg commented

Is there any parameter by which i can make my view work in a way i want it to be without changing code of views module? I mean can a set any parameter for the fields i don't want to be in groupby field.

merlinofchaos’s picture

merlinofchaos commented

If you are using $query->add_field() then yes, the 4th argument is a params array. Make this array('aggregate' => TRUE) and your field will be considered an agregate field and not added to the group by.

shenagarg’s picture

shenagarg commented

Thanks. That solved one of problems i am facing.

For time column i am storing it as timestamp using time(). I am adding time as Sort criteria with default file in views to sort views_handler_Sort_Date. Can u suggest something so that i can add time as sort criteria without adding it in group by array even if i have to write handler for sorting i can, just need a idea how to do that. Can u suggest something?