Error

Please login first

[SOLVED] Userlist with cbqueryfield

gtrennert
OFFLINE
Premium Member
Posts: 133
Thanks: 4
Karma: 0

8 years 11 months ago - 8 years 11 months ago #265144 by gtrennert

[SOLVED] Userlist with cbqueryfield was created by gtrennert

hello

cb2.0.9
J3.1.4

I have defined a cbquery field with this query :

select compatibility from #__comprofiler as a, #__comprofiler as b, #__calccompatibility as c
where c.userelement = a.cb_elementint AND c.viewerelement = b.cb_elementint AND c.usersign = a.cb_signint AND c.viewersign = b.cb_signint AND a.id = [user_id] AND b.id = [cb:userdata field="user_id" user="#me" /]

and added it to my userlist as a new column
Problem is that I try to sort by this column and it does just not work - no error
Why is this and what can I do ?

Last edit: 8 years 11 months ago by krileon.

Please Log in to join the conversation.

krileon
Team Member
OFFLINE
Posts: 68602
Thanks: 9108
Karma: 1434

8 years 11 months ago #265188 by krileon

Replied by krileon on topic Userlist with cbqueryfield

You can't sort by or filter by a query field value. You need to add your custom query directly to the Userlist by toggling Advanced Sort By, which lets you add a query directly to the Userlist query ORDER BY statement.

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.

gtrennert
OFFLINE
Premium Member
Posts: 133
Thanks: 4
Karma: 0

8 years 11 months ago #265207 by gtrennert

Replied by gtrennert on topic Userlist with cbqueryfield

I think I did as you said :
In the userlist, sorting tab, I toggled to advanced and entered this :
ue.`cb_compatibility2`

But its not sorting.

If I enter another field (a normal one and not a CB Query Field) it works.

But perhaps I did not understand what you asked me to do ?

Please Log in to join the conversation.

krileon
Team Member
OFFLINE
Posts: 68602
Thanks: 9108
Karma: 1434

8 years 11 months ago #265215 by krileon

Replied by krileon on topic Userlist with cbqueryfield

You can't sort by a CB Query Field. It has no column in the database to sort by. You need to instead edit your userlist and under Sort By click Advanced then supply a custom query there to be used in the Order By of the userlist query.

Please Log in to join the conversation.

gtrennert
OFFLINE
Premium Member
Posts: 133
Thanks: 4
Karma: 0

8 years 11 months ago #265221 by gtrennert

Replied by gtrennert on topic Userlist with cbqueryfield

I don't know this way to make oder by with a select - when googling I have the impression that I have to integrate a function like max to make it work ?

I need some help to make my query :
I know how I can make my where clause to get fields of the searched users (ue.xxx) but how can I can get the fields of the current (logged in) user here ?

Please Log in to join the conversation.

krileon
Team Member
OFFLINE
Posts: 68602
Thanks: 9108
Karma: 1434

8 years 11 months ago #265222 by krileon

Replied by krileon on topic Userlist with cbqueryfield

You can use subqueries in an Order By statement, but it won't index so depending on how many users you have it's probably going to be slow. The below is an example of what the userlist query would look like.

SELECT DISTINCT ue.*, u.*, '' AS 'NA' 
  FROM jos_users u
 JOIN jos_user_usergroup_map g 
  ON g.`user_id` = u.`id`
 JOIN jos_comprofiler ue 
  ON ue.`id` = u.`id`
 
  WHERE u.block = 0
 
  AND ue.banned = 0
 
  AND ue.approved = 1
 
  AND ue.confirmed = 1
 
  AND g.group_id IN  (1, 6, 7, 2, 3, 4, 5, 10, 12, 8)   
 
  ORDER BY YOUR_SQL_WOULD_BE_HERE
  LIMIT 0, 15

In the above for example YOUR_SQL_WOULD_BE_HERE is where the Advanced textarea of Sort By would be. It directly adds to the query.

If you need help with your query you should consult a contract developer. We do not provide custom coding. We only provide simple example code when possible. Your query involves a non-core table so we are not familiar with its structure and can not help you. The best I can suggest is the following based off your existing query.

SELECT c.`compatibility` FROM `#__calccompatibility` AS c WHERE c.`userelement` = u.`id` AND c.`viewerelement` = '[user_id]' AND c.`usersign` = ue.`id` AND c.`viewersign` = '[cb_signint]'

In the Advanced usages of a userlist the substitutions are always of the viewing user.

Please Log in to join the conversation.

Moderators: beat, nant, krileon

Time to create page: 0.212 seconds

Error

[SOLVED] Userlist with cbqueryfield

Search

Login / Logout

Menu

User Menu