Toggle Submenu
  1. Forums
  2. Recent
  3. Categories
  4. Tags
  5. Users
  6. Badges
Support is currently Offline
Support Offline
It's past support hour. We are taking some rest and will come back stronger tomorrow. Promise!
Official support hours:
Monday To Friday except Saturday and Sunday
From 09:30 To 17:00
Our current time: Friday 3:33 am
Add a reply View Replies (3)

Resolved View options for user list module

  1. blocdaddy
    blocdaddy
  2. Support
  3. Sunday, November 03 2019, 02:57 AM
Right now I have two user list modules on the front page of my website, one uses SQL Where parameters to display the newest users in a specific user group. The other module shows the 4 newest members of all user groups. Both lists automatically update when a new user registers since the lists are set to show users by registration date in descending order.

However, I am trying to create a few other user list modules, but I can't get the SQL Where parameters to work (assuming that's what I should be using):

1. In one user list, I would like to hide ONLY the newest registered user and start the user list by showing the second newest registered user and then the rest of the user list in descending order and have it auto-update upon new user registration like my other lists. I would also similarly like to be able to hide the 2 newest registered users, hide the 3 newest registered users, etc.

2. I would like to show ONLY the second newest user and hide the newest registered user and the rest of the list after the second newest member and have it auto-update upon new user registration like my other lists. I would also similarly like to be able to show only the 3rd newest registered user, only the 4th newest registered user, etc.

It’s of course easy to auto show just the newest registered user by selecting only one article and one user and selecting registration date in descending order on the module options. And I know I could use SQL Where commands to hide specific user IDs but I don't want to have to update the lists manually every time someone new registers.

Are there different SQL Where parameters or some other way to accomplish what I'm looking for? Thanks!
References
  1. https://www.premierlawyers.org
Accepted Answer
admin
admin Accepted Answer
Admin
Hi,
sorry but some SQL server does not accept LIMIT directive with this way, you can try with this:
user_id NOT IN (SELECT * FROM (SELECT id FROM #__users ORDER BY registerDate DESC LIMIT 1) as tmp)

and this 
user_id NOT IN (SELECT * FROM (SELECT id FROM #__users as t1,#__user_usergroup_map as t2 WHERE  t2.group_id = 11 AND t1.id = t2.user_id GROUP BY t1.id ORDER BY registerDate DESC LIMIT 1) as tmp)


I was also wondering about the second part of my question too. Is there a way to show ONLY the second newest user (or third, etc.) and hide the rest of the list?
Yes, you can use above Custom WHERE and limit to only 1 result (the is a parameter in a module option for this), if you need to skip more than 1 user you can change the LIMIT statement, for example with LIMIT 3 then the query will skip 3 users.
This way exclude users from result

Alternatively you can use a SQL Where to include only specific user with something like this: 
user_id IN (SELECT * FROM (SELECT id FROM #__users ORDER BY registerDate DESC LIMIT 1,1) as tmp)
you can use LIMIT offset,1 so for example:
for second user LIMIT 1,1
for third user LIMIT 2,1
for third user LIMIT 2,1
for fourth user LIMIT 3,1
......
  1. more than a month ago
  2. Support
  3. # Permalink
Responses (3)
admin
admin Accepted Answer
Admin
Hi,
you can use a SQL where like this:
user_id NOT IN (SELECT id FROM #__users ORDER BY registerDate DESC LIMIT 1)
this WHERE statement should remove first user from the recent users

user_id NOT IN (SELECT id FROM #__users as t1,#__user_usergroup_map as t2 WHERE  t2.group_id = 11 AND t1.id = t2.user_id GROUP BY t1.id ORDER BY registerDate DESC LIMIT 1)
this WHERE statement should remove first user from the recent users in a specific usergroup (in this case group 11)
  1. more than a month ago
  2. Support
  3. # 1
blocdaddy
blocdaddy Accepted Answer
Thanks for the quick reply!

When I insert the first SQL where parameter in the "Newest Members" module (which shows 4 newest users in one row), it hides the entire module.

I was also wondering about the second part of my question too. Is there a way to show ONLY the second newest user (or third, etc.) and hide the rest of the list?
  1. more than a month ago
  2. Support
  3. # 2
blocdaddy
blocdaddy Accepted Answer
Works great - thanks as always for the quick responses and help!
  1. more than a month ago
  2. Support
  3. # 3
  • Page :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.