How can I filter ZQuery by multiple fields?

[wat4dog]

Hi ALL

I'm trying to merge old Delphi app from BDE+MySQLODBC to Zeos.

I have a complicated query which joins few tables:

Code: Select all

SELECT *, bodyType(cars.id) as `class` from cars
LEFT JOIN clients ON cars.client_id = clients.id
LEFT JOIN models ON cars.model_id=models.id
LEFT JOIN engine_types ON cars.engine_type_id=engine_types.id
LEFT JOIN transmissions ON cars.transmission_id=transmissions.id 
ORDER BY cars.body ASC, cars.vin ASC

Can anybody tell me how can I filter results by few fields?

For example:

Code: Select all

ZQuery1.Filter := 'body LIKE '''+Edit1.Text+'%'' AND VIN LIKE '''+Edit1.Text+'%''';

If I set ZQuery1.Filtered := True; it doens't work. Just shows empty table.

Thank you in advance! And sorry for my english

[jeremicm]

don't use ZQuery1.Filter... add filters in sql instead...

Code: Select all

SELECT *, bodyType(cars.id) as `class` from cars 
LEFT JOIN clients ON cars.client_id = clients.id 
LEFT JOIN models ON cars.model_id=models.id 
LEFT JOIN engine_types ON cars.engine_type_id=engine_types.id 
LEFT JOIN transmissions ON cars.transmission_id=transmissions.id 
ORDER BY cars.body ASC, cars.vin ASC
WHERE body LIKE :body AND vin LIKE :vin

ParamByNAme('body').Value := '%'+ Edit1.Text + '%';
ParamByNAme('vid').Value := '%'+ Edit1.Text + '%';

[wat4dog]

Oh cool! You've opened my eyes LOL
Thanks a lot!

PS: all ingenious is simple!

[wat4dog]

But it's working worse than filter...
So, I need to filter results on user typing... Like "search on the fly"

And REFRESH query every time is not good because there is so many records in tables.
I think better to filter records locally than refresh every user "Key press".

I'm hoping there is other method exists?

Any ideas are welcome!

[jeremicm]

use ZTable then instead of ZQuery maybe?

[wat4dog]

use ZTable then instead of ZQuery maybe?

I think it's not good idea, when I need to join more than 2 tables Hard to imaginate...
So, I've just made checking on "param value changed", also, made small delay after user press key in search field: so, I actually refresh query after 1 second after user done typing.

User don't like to press "enter" after typing search string

Thank you, jeremicm!

[mdaems]

Guy Fink made a nice help file for expressions (as used in client side filters)

2.4 String operators
+ Concatenates strings
LIKE special comparison operator for strings
The LIKE-Operator is a special string comparison operator. It takes a regular expression as rigthside
operand. NOT may be used as modificator.
The LIKE-Operator calls the pattern matching function IsMatch.
Examples from the source file :
IsMatch allows unix grep-like pattern comparisons, for instance:
? Matches any single characer
* Matches any contiguous characters
[abc] Matches a or b or c at that position
[^abc] Matches anything but a or b or c at that position
[!abc] Ditto
[a-e] Matches a through e at that position
'ma?ch.*' -Would match match.exe, mavch.dat, march.on, etc
'this [e-n]s a [!zy]est' - Would match 'this is a test',
but would not match 'this as a yest'