Forums

how to use addToQuery() and filter()

Permalink
I created my List model. That extended DatabaseItemList library.
In that I set my base query like
protected function setBaseQuery() {
        $this->setQuery('SELECT table1.col1, table3.col2, table1.col2, table2.col1 FROM table1, table2, table3');
    }

When I use the following query in my controller
$List->addToQuery("where table2.col1 = table1.col3 and table3.col1 = table1.col2");

It adds "where 1=1" and I keep getting sql error.

The addToQuery() content is primary key and foreign key relation.
How to solve this problem...
cjramki
 
Remo replied on at Permalink Reply
Remo
this looks like a double post - please avoid that. It's annoying if someone else is reading this post and spends time to help you while the question has already been answered..

http://www.concrete5.org/developers/pro-accounts/community-leaders-...
Remo replied on at Permalink Reply
Remo
I just saw that the other question was posted in a protected area, here's my answer I've posted there:

---

use ANSI SQL - it's easier to read, looks nicer and doesn't need an additional addToQuery:

protected function setBaseQuery() {
        $this->setQuery('SELECT table1.col1, table3.col2, table1.col2, table2.col1 FROM table1 INNER JOIN table2 ON tabl1.col2=table2.col1 INNER JOIN table3 ON table2.col1 = table3.col1');
    }


I'm not sure I got the column names right..
cjramki replied on at Permalink Reply
cjramki
Sorry Remo,

I first posted at here only...
I did not get any response... So, I posted at Leaders Discussion forum...
I don't know how to delete it...
Non-Community members cannot view that page...
So, it may helpful to all community members also... ;-)
Remo replied on at Permalink Reply
Remo
at least make sure that you add a comment to both posts telling others that you've asked it before..
cjramki replied on at Permalink Reply
cjramki
Ok Remo, Thanks for your information...
cjramki replied on at Permalink Reply
cjramki
I have Another query...

If I want to use Between clause using filter query...
How to do that?
Any syntax is there for that?
robkovacs replied on at Permalink Reply
For any other situation where you need to add something to the WHERE clause that can't be expressed as a filter, use the following:

$list->postUserQuery = 'additional SQL';


Using the above example:

$List->postUserQuery('where table2.col1 = table1.col3 and table3.col1 = table1.col2');
ricecream replied on at Permalink Reply
Is this new? I don't see this in the API at all. I see that its used in DatabaseItemList but that property is protected and there is no way of altering it