Forums

Complex SQL Statement Woes

Permalink
I've got a SQL statement I need to run for a report that uses a UNION clause and I'm running into some difficulty getting it to execute successfully.

I'm running it in a class that extends DatabaseItemList (for example):

class myList extends DatabaseItemList {
function __construct() {
    $this->setQuery("mySQLhere");
}


The trouble is that my SQL looks something like this:

SELECT a,b,c from tblUsers where a = 1 UNION SELECT a,b,c from tblCustomers where c = 2 order by b DESC


Each WHERE clause needs to be dynamic using the db::filter() function -- but that function automatically appends the WHERE clause to the end of the statement. I need it, essentially, in the middle of my statement.

Ideas? Suggestions?
brennaH
 
jaredquinn replied on at Permalink Reply
jaredquinn
Try something like:

$this->setQuery('SELECT a,b,c from tblUsers');
$this->filter('`tblUsers`.a = 1');
$this->addToQuery('UNION SELECT a,b,c from tblCustomers');
$this->filter('`tblCustomers`.c = 2');
$this->sortBy('`tblCustomers`.b', 'DESC');


(Note: this hasn't been tested to check it, but it feels right).
brennaH replied on at Permalink Reply
brennaH
That was the first thing I tried -- made perfect sense to me as well. Sadly, no dice. The where clause still ends up at the end of the statement.

I've been thinking maybe of creating two separate objects, each part of the query, and then merging them after the fact into a third object.

Is there an object merge function for page list objects, by any miracle?
Shotster replied on at Permalink Reply
Shotster
I recently subclassed UserList (which extends DatabaseItemList) and overrode the necessary properties and methods to get the desired functionality. This included overriding the get() method and creating a custom DB query. While it wasn't as quick and trivial as I thought it would be, the knowledge gained about the class internals will hopefully pay off down the road.

So I guess my suggestion is to dig deeper and override.

-Steve
hudsonn replied on at Permalink Reply
My knowledge about c5 is very limited but a stored procedure could be your answer and less complex than digging in concrete5 core.