Advanced Filter Nested SQL Help

9 years 5 months ago #131478 by zapacoman
I am no PHP/SQL coder, but I'm trying to fumble my way through an advanced filter for CB Lists. What I am trying to accomplish in words is a list that shows all of the gyms which are of equal or lesser value to the person's home gym. I have it set up so that each member can belong to one groupjive group which is for that home gym - moderated by the gym owner. I want the query to 1. go into the table of group members & pull out the id of the group they belong to 2. go into the table of groups & pull out the moderator ID for that group. 3. Go to the list of CB members and pull out the Cost for that member. 4. Only show the members (gyms) which are =< that value.

The SQL query I came up with looks like this, but it produces an error:

[code:1]{'cb_cost' <= (SELECT ('cb_cost') FROM '#__comprofiler' WHERE 'user_id' = (SELECT ('user_id') FROM '#__gj_groups' WHERE 'id' = (SELECT ('id_group') FROM '#__gj_users' WHERE 'id_user' = '[user_id]' )))}
[/code:1]
If you could tell me where I'm going wrong, I'd be eternally in your debt!

Post edited by: zapacoman, at: 2010/04/27 20:01

Post edited by: zapacoman, at: 2010/04/27 20:06

Post edited by: zapacoman, at: 2010/04/27 20:13

Post edited by: zapacoman, at: 2010/04/27 21:20
9 years 5 months ago #131486 by zapacoman
I also tried the "IN" method of nesting SQL statements to no avail!

[code:1]{'cb_cost' <= (SELECT ('cb_cost') FROM '#__comprofiler' WHERE 'user_id' IN (SELECT ('user_id') FROM '#__gj_groups' WHERE 'id' IN (SELECT ('id_group') FROM '#__gj_users' WHERE 'id_user' = '[user_id]' )))}[/code:1]
9 years 5 months ago #131721 by zapacoman
In case anyone is reading this, and willing to help, but perhaps I didn't supply enough info before, I turned on the site debug and recieved the following 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 '#__comprofiler' WHERE 'user_id' IN (SELEC' 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 ({'cb_cost' <= (SELECT ('cb_cost') FROM '#__comprofiler' WHERE 'user_id' IN (SELECT ('user_id') FROM '#__gj_groups' WHERE 'id' IN (SELECT ('id_group') FROM '#__gj_users' WHERE 'id_user' = '69' )))})


This is based on the following filter code:

{'cb_cost' <= (SELECT ('cb_cost') FROM '#__comprofiler' WHERE 'user_id' IN (SELECT ('user_id') FROM '#__gj_groups' WHERE 'id' IN (SELECT ('id_group') FROM '#__gj_users' WHERE 'id_user' = '[user_id]' )))}


Any suggestions?
9 years 5 months ago #131722 by zapacoman
helping you help me...

Exact PHP version. 5.2.9
Exact CB version. 1.2.2
Exact Joomla version. 1.5.15
Detailed description of problem. (screenshots? link?) Described below
Any error messages reported with Joomla debug mode on and error reporting set to maximum? Posted below
Any error messages reported with your hosts error log? No
Any ACL modification components? (JACL, etc..) No
Any SEF components? No
Move or encrypt configuration.php? No
Upgrade from CB version ?? or fresh install? Fresh
Migrated from Joomla version ?? or fresh install? Fresh
What browser is issue present in? All

...please help me! ;)
9 years 5 months ago #131735 by krileon
Query looks fine; you've simply some illegal characters within it. First you've surrounded the entire query with "{" and "}" these are not valid within a query. Replaced with "(" ")" as used elsewhere. Unsure if was intentional or not, but rest of your surrounds are correct.

You've also used single quotes ' to surround a column, this is not valid either. Single and double quotes should only be used on values.

I've also changed your column from user_id to id when querying the _comprofiler table as id is actually indexed (much much better performance).

I've reviewed your query please try the following. It may require tweaking still to receive the results you desire, but should be a good example to get you going in the right direction.

[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]

Post edited by: krileon, at: 2010/04/29 21:22


Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Templates - CBSubs - Hosting - Forge - Incubator - GroupJive
--
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 EST to 4:00 PM EST. 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.
9 years 5 months ago #131745 by zapacoman
4 words: you are the man!

Seriously though, muchos gracias! I'm sure several future newbies will thank this thread because it explains how to nest an advanced user list filter which is a powerful ability to have!

Thanks again.
Moderators: beatnantkrileon
Time to create page: 0.401 seconds
Facebook Twitter Google LinkedIn