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.
Code:
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.
Code:
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.
krileon
