Radicore Forum: How To » Customizing the SQL statement

Home » RADICORE » How To » Customizing the SQL statement

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Customizing the SQL statement [message #857]

Mon, 28 May 2007 08:38

salamsy
Messages: 18
Registered: October 2006
Location: Dakar

Junior Member

Hi,

I cannot implement an SQL statement containing the modifier "WITH ROLL UP" in the "GROUP BY" clause. It runs fine in Mysql but Radicore returns a parse error.

What about the usage of these modifiers in customizing the SQL statement ?

Thanks.

Abdou

Report message to a moderator

Re: Customizing the SQL statement [message #858 is a reply to message #857]

Mon, 28 May 2007 11:45

AJM
Messages: 2371
Registered: April 2006
Location: Surrey, UK

Senior Member

There are certain places in the framework where I parse the SQL statement in order to make adjustments, such as qualifying field names, and as I haven't yet dealt with any GROUP BY ... WITH ROLL UP it probable than deconstructing and reconstructing the string is causing a problem. Can you provide an example of the sql query you are trying to execute?

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: Customizing the SQL statement [message #859 is a reply to message #858]

Tue, 29 May 2007 06:06

salamsy
Messages: 18
Registered: October 2006
Location: Dakar

Junior Member

Hi,

This query runs on a single table "awl_leader". It counts the number of records and gives a total for each column specified in GROUP BY.

"select lea_region, lea_degree, count(lea_id) from awl_leader group by lea_region, lea_degree with rollup"

Thanks.

Abdou Salam

Abdou Salam

Report message to a moderator

Re: Customizing the SQL statement [message #860 is a reply to message #859]

Tue, 29 May 2007 08:20

AJM
Messages: 2371
Registered: April 2006
Location: Surrey, UK

Senior Member

I have discovered what the problem is - MySQL does not allow a "GROUP BY ... WITH ROLLUP" to be used with "ORDER BY ...". The solution is to edit the getdata() method inside the 'dml.mysql.class.inc' file in order to change

    if (!empty($this->sql_groupby)) {
        $group_str = "GROUP BY $this->sql_groupby";
    } else {
        $group_str = NULL;
    } // if

to the following:

    if (!empty($this->sql_groupby)) {
        $group_str = "GROUP BY $this->sql_groupby";
        if (eregi(" WITH ROLLUP", $this->sql_groupby)) {
            $this->sql_orderby = null;
        } // if
    } else {
        $group_str = NULL;
    } // if

This will cause the ORDER BY clause to be ommitted if the GROUP BY clause contains "WITH ROLLUP".

This change has been tested with version 1.24.0 and will be included in version 1.25.0

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator