Advanced Filter Nested SQL Help

13 years 10 months ago #131831 by zapacoman
Replied by zapacoman on topic Re:User List Filter Nested SQL Help
Since I got this advanced filter set up - it worked right up until I selected the following search settings and resaved:

Searchable Fields: Searchable fields, displayed ones only
Criteria Search: Simple any word match

Then when I went to the list it displayed an sql error message. (i didn't debug at that time)

So, I went back into the List setup page and found that the filter had switched itself back to simple, and when I clicked advanced my Advanced filter box was back, but i found my code garbled [the first ( had been replaced with a 20 and the final ) was removed.

I put "no" for searchable fields. Resaved.

So, I:
  1. re-copied krileon's code from this thread.
  2. Added back in my columns (which had disappeared ? sometime in the past 2 steps)
  3. chosen the searchable fields and criteria search as said above in this post.
  4. resaved again

What I found was a list page that was now working again.

To be thorough, I wanted to get the sql error for you, so I tried to duplicate the error. To my surprise, as soon as I switched the search type back to "no" and resaved....it produced another error! and the following advanced filter resulted (an 8 this time instead of 20) and a deleted final ).

8`cb_cost` <= ( SELECT `cb_cost` FROM `#__comprofiler` WHERE `id` = ( SELECT `user_id` FROM `#__gj_groups` WHERE `id` = ( SELECT `id_group` FROM `#__gj_users` WHERE `id_user` = [user_id] ) ) )


The debug showed the following error this time:

500 - JDatabaseMySQL::query: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`cb_cost` <= ( SELECT `cb_cost` FROM `jos_comprofiler` WHERE `id` = ( SELECT `us' at line 7 SQL=SELECT COUNT(*) FROM jos_comprofiler ue, jos_users u WHERE u.block = 0 AND ue.approved = 1 AND ue.confirmed = 1 AND ue.banned = 0 AND u.gid IN (19) AND u.`id` = ue.`id` AND (8`cb_cost` <= ( SELECT `cb_cost` FROM `jos_comprofiler` WHERE `id` = ( SELECT `user_id` FROM `jos_gj_groups` WHERE `id` = ( SELECT `id_group` FROM `jos_gj_users` WHERE `id_user` = 70 ) ) )


So, in conclusion, I've determined that if the search settings are changed after the advanced filter is set-up it messes up the filter code. Any ideas? I can provide you (someone from Joomlapolis) with my login info if needed to debug.

Derek

Post edited by: zapacoman, at: 2010/04/30 16:52

Post edited by: zapacoman, at: 2010/04/30 20:34

Please Log in to join the conversation.

13 years 10 months ago #131859 by krileon
Replied by krileon on topic Re:User List Filter Nested SQL Help
Looks like it's just first set of quotes that is distorting; not a problem, simply remove. Are you using Simple filters as well as Advanced filters? At times they can conflict. Please try the below Advanced Filter.

[code:1]
`cb_cost` <= ( SELECT `cb_cost` FROM `#__comprofiler` WHERE `id` = ( SELECT `user_id` FROM `#__gj_groups` WHERE `id` = ( SELECT `id_group` FROM `#__gj_users` WHERE `id_user` = [user_id] ) ) )
[/code:1]

Please keep in mind filters are just additions to the query, so standard query "laws" apply in a sense that it's easy to mess up the filter. If using simply files AND advanced filters. I recommend removing all simple filters and using strictly advanced filters (separate with an OR or AND).

Post edited by: krileon, at: 2010/04/30 20:46


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.

13 years 10 months ago #131875 by zapacoman
Replied by zapacoman on topic Re:User List Filter Nested SQL Help
No, no simple filters are being used. The only change made was turning the search on/off.

When I updated the code to your latest string I got an error

500 - JDatabaseMySQL::query: 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`cb_cost` <= ( SELECT `cb_cost` FROM `jos_comprofiler` WHERE `id` = ( SELECT `us' at line 7 SQL=SELECT COUNT(*) FROM jos_comprofiler ue, jos_users u WHERE u.block = 0 AND ue.approved = 1 AND ue.confirmed = 1 AND ue.banned = 0 AND u.gid IN (19) AND u.`id` = ue.`id` AND 28 `cb_cost` <= ( SELECT `cb_cost` FROM `jos_comprofiler` WHERE `id` = ( SELECT `user_id` FROM `jos_gj_groups` WHERE `id` = ( SELECT `id_group` FROM `jos_gj_users` WHERE `id_user` = 70 ) ) ) )


Then when I went back into the list setup page the simple filter radio button was selected and when I click the advanced filter button nothing happens - it stays on the simple filter. Also, the columns were preserved but the field values were cleared. But I save and reopen the list setup page.

This time it defaults to simple, but when i click advanced it shows:

20`cb_cost` <= ( SELECT `cb_cost` FROM `#__comprofiler` WHERE `id` = ( SELECT `user_id` FROM `#__gj_groups` WHERE `id` = ( SELECT `id_group` FROM `#__gj_users` WHERE `id_user` = [user_id] ) ) )


Now I delete the 20, associate the field values back to their respective columns, and save. We're back in business.

I just also realized that if you change field values around once you have a working advanced filter it errors out as well - go out - back in - delete the garbage and re-save. In general you can get the lists to work, but it's buggy.

Please Log in to join the conversation.

13 years 10 months ago #132080 by krileon
Replied by krileon on topic Re:User List Filter Nested SQL Help
Never experienced such an issue, possible from an integration of some sort. Could not duplicate your results with the steps provided. Impossible to check how the query is built without reviewing in detail the debug query information unfortunately, but at any rate would configure to working condition and leave as is.


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.186 seconds

Facebook Twitter LinkedIn