Autocomplete validation for a text field with multiple answers

7 months 1 day ago - 6 months 3 weeks ago #328743 by activha
Hello

I am trying to set a text field with an autocomplete query validation which should display a list of possible results after 3 characters, and ideally the list should decrease as long as the user enters other characters.

I tried
SELECT COUNT(*) as count FROM `code_NAF_APE` WHERE (`Code` LIKE '[value]') OR (`Description` LIKE '[value]') OR (`Section` LIKE '[value]') OR (`Description section` LIKE '[value]');
but it only returns the number of rows

then I tried
SELECT * FROM `code_NAF_APE` WHERE (`Code` LIKE '[value]') OR (`Description` LIKE '[value]') OR (`Section` LIKE '[value]') OR (`Description section` LIKE '[value]');
but it would only return something when the full information is retrieved and not all the rows starting with the typed input.

Can you tell me what I miss here ?

As this is a huge table of NAF codes for companies, I cannot use a query field which would display all the results

Please Log in to join the conversation.

7 months 1 day ago #328749 by krileon
Query Autocomplete example as follows. This should give you an idea of how to setup a query based autocomplete usage.

Query:
SELECT `id`, `username` FROM `#__users` WHERE `username` LIKE '[value]%' LIMIT 10
Value Column: id
Label Column: username
Minimum Length: 3

This will allow autocomplete selection of a user id searching by username. I cannot help you with your custom query, sorry. You'll need to figure that part out yourself and ideally should test it in phpmyadmin for faster checking if your query is working and providing the expected results as well as performance testing to see if you need additional indexes. Be careful when using wildcard % in LIKE usages as indexes cannot be used with the search starts with % (notice above it's after to support partial matches).


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

7 months 1 day ago #328757 by activha
Oupss completely forgot the % wildcard !
Thanks for the correction.

That's working fine to retrieve the value/label, however my field is not saved when a value is chosen.

I have set strict selection, there is no validation set, I tried both in front and backend and there is also no error in the js/console. The html also correctly displays the input value after the autocomplete.

Can you help me where to dig for this issue ?

Please Log in to join the conversation.

7 months 17 hours ago - 7 months 17 hours ago #328762 by krileon
That can happen when the value and the label don't match. So for example the below would fail with strict mode.

SELECT `id`, `username` FROM `#__users` WHERE `username` LIKE '[value]%' LIMIT 10

But the below would work since we're also checking the value that's being stored.

SELECT `id`, `username` FROM `#__users` WHERE `id` = '[value]' OR `username` LIKE '[value]%' LIMIT 10

In my example I'm typing out a username to find a user id. The user id what is stored. So you end up in a situation where what I searched by and what I stored isn't the same. Strict mode doesn't really cover that scenario so you have to do the above OR you can use Query Validation and validate it there with a custom validation rule.


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

7 months 16 hours ago #328766 by activha
Trying both exact queries that you wrote gave the same empty result on my installation.

I have the same result with or without strict selection.

I can type, then select the username, then it shows the correct user id in the input field. But on save no value is stored and the field is still empty.

Please Log in to join the conversation.

7 months 16 hours ago #328767 by krileon
It's possible your query is erroring. Enable debug mode and maximum error reporting in Joomla global configuration then retry and see if any errors output. The above works fine in my tests, but am also working on a clean up release for CB Query Field to improve the internal code to be more strict. That should be available today so issue might already be fixed if this is a bug.


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

Moderators: beatnantkrileon
Time to create page: 0.193 seconds

Facebook Twitter LinkedIn