Hello,

I got an incompatibility with I use a custom query with filters.

This is the issue:

Inside a grid layout where it shows a list of available jobs, I added a <a href> tag in the "job name" data field with two dynamic parameters (profile of education and level of education) that I needed to filter another grid layout corresponding to a people list.

I added Inside the model of the people list where exist the prepareQuery() function, a couple of lines where I get the url parameters (through the $_GET[] vars), and according with that values I do a $query custom filter.

in short, is something like that:

$mad_p = (int)$_GET[pid];
$mad_n = (int)$_GET[nid];

(The "people" table has 3 profile fields, so this is the query:)

$query->where("(a.perfil_1 = $mad_p AND a.tipo_perfil >= $mad_n) OR (a.perfil_2 = $mad_p AND a.tipo_perfil >= $mad_n) OR (a.perfil_3 = $mad_p AND a.tipo_perfil >= $mad_n)");

Well, it works perfect!

But the problem is when I try to use normal filters created through Cook, like for example filter through the city field, or sex field, or age ranges, etc.

The filter results are not working, sometimes are partially good, but when I combine two or more, it doesn't work.

I know that the problem is with my custom $query, because when I disabled that lines of code, the Cook $query filters work perfect!

Could any one give me some advice? maybe another aprox.? thanks a lot!

Excellent question to start a guide help for that.

Firstly, do not use the $_GET and $_POST vars. They can contain any kind of stuff. Too dangerous.
Then, it is really easy to do the job easy, fast and safe on your component.

TODO :
models/peoples.php (People, collection)

What does it means behind ?

-> it receive the params 'filter_pid' and 'filter_nid'
-> parse the received values against injection (type cmd)
-> set up 2 states vars for this model : 'filter.pid', 'filter.nid'


Then in prepareQuery(), the trick is to call this filter only if it is requested through url.
Your example is complex, so I make it easy for readers :

-> Definitively parse to integer for security because int is not quoted
-> Use addWhere() available thanks to Cook, because it is incredible when you mix up a lot of possibles SQL calls, because the model is able to send unique SQL statements and avoid conflicts.
Anyway, try to use addWhere(), addJoin(), addSelect()...

@madaura :
Your example now

Can be simplified :
TOAnd maybe better ...

After the Regular Expressions, where is the SQL Guru ???

Just a consideration ....

What is the type of tipo_perfil ?
I supposed it was integer as well because you apply a >= sign.

Anyway, you understood the concept. It is working for any kind of params types.

TOTAL : 3 lines !!

Not amazing stuff ?

And to add a precision, no need to explain you that you can active/unactive this filter feature for all, or for a certain page (layout). All you have to do is to place previous code in the switch in the beginning of prepareQuery().

You do not need to respet any kind or order when you compose the SQL query, you can mix up the source and reorganize as you want.

In prepareQuery(), you can put everything down, and rewrite any kind of algorithm for SQL creation, unless you still use the states vars and the addJoin()... statements.

Have fun.

Thanks a lot admin, one question:

I'm sending the content vars (pid, nid) from a list of "available jobs" created by a layout view collection (internally of this code I get the values of the pid (profile) and nid (level of study)), so how can I connect this parameters with your explanation?

Best,

pd. the idea is when the users go to the available jobs list, they can click any of them and the app shows the list of people result according with the pid and nid values. thnaks!

I just read your other post, and the answers is yes, the type of both of them are integers..

You include them in the post :

option=com_xxx
view=xxx
...
filter_pid=###
filter_nid=###


You can put them in an hidden input of the form.



Thanks JDom