Support

  1. blocdaddy
  2. Support
  3. Sunday, November 03 2019, 02:57 AM
  4.  Subscribe via email
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 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. one week ago
  2. Support
  3. # Permalink
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. one week ago
  2. Support
  3. # 1
blocdaddy Accepted Answer
Pro
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. one week ago
  2. Support
  3. # 2
blocdaddy Accepted Answer
Pro
Works great - thanks as always for the quick responses and help!
  1. one week 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.

Request Support

Support is currently Offline

Support Availability

Working days: Monday to Friday. The support staff is not available on weekends; in the most of cases tickets will not be answered during that time.

Reply time: Depending on the complexity of your support issue it's usually between a few minutes and 24 hours for paid members and about one week for free members. When we expect longer delays we will notify you.

Guidelines

Before you post: read the documentation and search the forums for an answer to your question.

When you post: include Site Details if you request a support (you can use the form below the reply in Site Details tab).

Auto Solved Question: If after a week the author of the post does not reply to a request by moderator, the question will be marked as resolved.

Language: only English

Search Users

Easy Profile® is not affiliated with or endorsed by Open Source Matters or the Joomla Project. Joomla is Free Software released under the GNU/GPL License.