I am facing an issue while generating views having group by caluse.
I found that query generated by views is not valid.
Here is the query :

SELECT DISTINCT(node.nid) AS nid,
node.title AS node_title
FROM {node} node
WHERE node.type in ('%s')
GROUP BY nid

Oracle require all the columns in GROUP BY clause. Drupal generates query with nid alias.

Anyone facing the same issue.

CommentFileSizeAuthor
#1 database.oracle.inc_.txt59.89 KBmandarmbhagwat78
#1 query.inc_.txt31.97 KBmandarmbhagwat78

Comments

mandarmbhagwat78’s picture

mandarmbhagwat78 commented
StatusFileSize
new query.inc_.txt31.97 KB
new database.oracle.inc_.txt59.89 KB

Hey, I got the solution.

I modified oracle_escape_group_by_alias() and oracle_escape_group_by_alias_callback() functions in databases.oracle.inc file.

I also modified views/include/query.inc file to display the correct query in preview mode.

See attached files.

aaaristo’s picture

aaaristo commented
Priority: Critical » Normal
Status: Active » Needs review

i'm going to review your code asap

aaaristo’s picture

aaaristo commented

which version of the oracle driver are you using?

mandarmbhagwat78’s picture

mandarmbhagwat78 commented

6.x-1.x-dev

mandarmbhagwat78’s picture

mandarmbhagwat78 commented

Andrea I have one query.
I have migrated drupal mysql db into oracle10g using sqldeveloper tool. During migration sqldeveloper added '_' after certain tables and fields as oracle consider those as reserved words e.g. access, comment, uid getting replaced with access_, comment_, uid_.

I found that you have handled reserved words but this scenario is not handled into your code.
I have modified oracle_escape_reserved_words() function with the following code and make it working :

$query= preg_replace($search, $replace, $query);

preg_match_all("/\{(.*?)(\})/", $query, $matches, PREG_SET_ORDER);
$reserved_words = "ACCESS";
foreach ($matches as $val) {
if(preg_match("/$reserved_words/", $val[1]))
$query = preg_replace("/{$val[0]}/", "{{$val[1]}_}", $query);
}

But after that I found that oracle_bind_query_callback() function is not able to execute $stmt->bindParam() and replace parameter :d0 or :s0 with the value.

Do you have any update on this?

aaaristo’s picture

aaaristo commented

I was away for holiday.. So, the point there is that IMUO is a sqldeveloper issue not a driver issue. I would not implement a regular expression if i can rename all that tables and columns in the "" form...

So why don't you rename them?

aaaristo’s picture

aaaristo commented

The query.inc should be modified by Views' maintainers.. But i think they are not going to change it until Drupal officially supports Oracle. Meanwhile i'm writing an integration module for Views-Oracle to stop patching Views, but actually i've little time to do that.

aaaristo’s picture

aaaristo commented
Category: bug » support
Status: Needs review » Closed (won't fix)

About the group by:

SELECT DISTINCT(node.nid) AS nid,
node.title AS node_title
FROM {node} node
WHERE node.type in ('%s')
GROUP BY nid

This query is a none-sense query... There is no need to group by the nid column that is primary key of the table...
And including it into the select columns makes the distinct quite absurd... May be you can use this configuration in you settings.php to rewrite the query to somenthing more SQL and Oracle oriented:

$GLOBALS['oracle_exception_queries']= array(
'SELECT DISTINCT(node.nid) AS nid,
node.title AS node_title
FROM {node} node
WHERE node.type in (\'%s\')
GROUP BY nid'
=>
'SELECT node.nid AS nid,
node.title AS node_title
FROM {node} node
WHERE node.type in (\'%s\')' );

Am i missing somenthing here?

aaaristo’s picture

aaaristo commented
Status: Closed (won't fix) » Reviewed & tested by the community

Hi,
i've tested your code (group by) with the latest version of Views and looks good to me. I'm going to release it with the next version.

Thank you!

aaaristo’s picture

aaaristo commented
Status: Reviewed & tested by the community » Fixed

fixed in the next release

aaaristo’s picture

aaaristo commented
Status: Fixed » Closed (fixed)
mandarmbhagwat78’s picture

mandarmbhagwat78 commented

Thanks very much! If you want me to work on any fixes please send.