Page 1 of 1
How can I filter ZQuery by multiple fields?
Posted: 19.12.2011, 14:32
by 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
Posted: 19.12.2011, 14:55
by 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 + '%';
Posted: 19.12.2011, 14:59
by wat4dog
Oh
cool! You've opened my eyes LOL
Thanks a lot!
PS: all ingenious is simple!
Posted: 19.12.2011, 15:35
by 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!
Posted: 19.12.2011, 17:50
by jeremicm
use ZTable then instead of ZQuery maybe?
Posted: 19.12.2011, 19:20
by wat4dog
jeremicm wrote: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!
Posted: 21.12.2011, 22:02
by 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'