Separate memberlists by membergroup (for 1.1.x)

[Antechinus]

This one has me stumped. I can set up a custom action or sub action easily enough, and the sub templates to go with it. That bit is no problem.

What I want to do now is call a separate sub template for the memberlist that only lists the members of one particular group, called by ID_GROUP. I've tried various conditionals in both the template and sources files but can't get it right. I assume this bit would be the one to nail:

Code Select Expand

// Select the members from the database.
$request = db_query("
SELECT mem.ID_MEMBER
FROM {$db_prefix}members AS mem" . ($_REQUEST['sort'] === 'isOnline' ? "
LEFT JOIN {$db_prefix}log_online AS lo ON (lo.ID_MEMBER = mem.ID_MEMBER)" : '') . ($_REQUEST['sort'] === 'ID_GROUP' ? "
LEFT JOIN {$db_prefix}membergroups AS mg ON (mg.ID_GROUP = IF(mem.ID_GROUP = 0, mem.ID_POST_GROUP, mem.ID_GROUP))" : '') . "
WHERE mem.is_activated = 1" . (empty($where) ? '' : "
AND $where") . "
ORDER BY " . $sort_methods[$_REQUEST['sort']][$context['sort_direction']] . "
LIMIT $limit, $modSettings[defaultMaxMembers]", __FILE__, __LINE__);
printMemberListRows($request);
mysql_free_result($request);

Anyone have any suggestions on this?

[Arantor]

Well, that function just gets the data itself. The key is in the $where which is primarily set for grabbing data off searches.

To select the data for all the members of a group, the $where needs to be declared as "ID_GROUP = {$group}" for primary groups only, or if you need it to consider secondary groups, "(ID_GROUP = {$group} OR FIND_IN_SET({$group}, additionalGroups))"

That'll get the member rows for just those groups.

[Antechinus]

Ok so in English? Are you saying I need to target these two lines?

Code Select Expand

      WHERE mem.is_activated = 1" . (empty($where) ? '' : "
         AND $where") . "

And exactly how do I add in the member group ID number? Say I want admins, as an example. So that is group 1.

[Arantor]

Is this code actually going into the main memberlist or in your own action?

[Antechinus]

I'll test it in the main memberlist on a local site, then set it up in either a complete new template or a sub action in the same template once it works. Can do it either way. Not fussed. Whatever is easiest and works.

[Arantor]

If you're using a new template I'd be inclined to distance it a little from the main memberlist and put it into own subaction, doing its own queries etc - that way you're not dealing with the fairly intertwined process that is memberlist generation and output.

The query you'd be wanting would become:

Code Select Expand

// Select the members from the database.
$request = db_query("
SELECT mem.ID_MEMBER
FROM {$db_prefix}members AS mem" . ($_REQUEST['sort'] === 'isOnline' ? "
LEFT JOIN {$db_prefix}log_online AS lo ON (lo.ID_MEMBER = mem.ID_MEMBER)" : '') . ($_REQUEST['sort'] === 'ID_GROUP' ? "
LEFT JOIN {$db_prefix}membergroups AS mg ON (mg.ID_GROUP = IF(mem.ID_GROUP = 0, mem.ID_POST_GROUP, mem.ID_GROUP))" : '') . "
WHERE mem.is_activated = 1
AND (ID_GROUP = 1 OR FIND_IN_SET(1, additionalGroups))" . (empty($where) ? '' : "
AND $where") . "
ORDER BY " . $sort_methods[$_REQUEST['sort']][$context['sort_direction']] . "
LIMIT $limit, $modSettings[defaultMaxMembers]", __FILE__, __LINE__);


That'll give you a list of member ids, printMemberListRows shows what it does with it (step through the array results, build a single list of members, call loadMemberData on that to get everything, then hand it over to the template)

[Antechinus]

Ok that looks pretty straightforward. So basically change the query to that, dump any bits I don't want (like search and sorting probably aren't needed), rename any functions I want to keep so there are no conflicts, and wrap the whole shebang up in another sources file and template. Cool. I can do that.

[Arantor]

I'm curious to see what you come up with as a result, actually...

[Antechinus]

It'll be ugly and smell bad, but it'll work.   Thanks for the tips. The query certainly sorts it on local so rearranging the various bits should be easy enough. I'll let you check the files and a demo once I figure it all out.

[Arantor]

You should see some of the code I've written, that positively reeks at times

Let me know if there's anything else I can help with.

[Antechinus]

Ok it works sort of, but I still have a bit of debugging to do. It's still all in the main template (I were lazy last night, I were ).

Basically I copied and renamed function ExMLAll() and altered the calls inside it for sa, then did the same up the top for the linktree and sub actions arrays. What's happening is that effectively it is sorting by the desired membergroup instead of only calling that group to the template. This will be due to one of the other queries that has defaultMaxMembers or whatever in it.

I'll import a complete memberlist from one of my backups and hack it around some more. Shouldn't test late at night on a fresh local site with only two members.

[Arantor]

Sounds like a fun little project

[Antechinus]

Ok, I am officially screwed here. Markup, css and gfx I'm good at. This stuff not so much.

It runs, and it runs without generating any errors in the admin log. That bit is good. Problem is that I'm not knowledgeable enough in this particular area to get it behaving how I want it.

What happens is that if I select the custom sub action on the memberlist template it will generate the first page correctly: all members listed on the first page will be from the desired group and in alphabetical order.

The problem is what happens after that. On the second page it reverts to a normal memberlist of every sod and his dog in alphabetical order. This continues until the end of the memberlist with all members listed. Not quite the desired result.

When you, or anyone else who understands this stuff better than I do, has a bit of time please cast your eye over the attached file. The relevant bit is function ExMLAll()

Cheers.

[Arantor]

This one's subtle enough. Reason it's doing that is because the pagination links aren't referencing your subaction.

In your new function, you'll see this:

Code Select Expand

$context['page_index'] = constructPageIndex($scripturl . '?action=mlist;sort=' . $_REQUEST['sort'] . (isset($_REQUEST['desc']) ? ';desc' : ''), $_REQUEST['start'], $context['num_members'], $modSettings['defaultMaxMembers']);

This is responsible for building the [1] [2] etc bit, and is the one that needs fixing. If you put in sa=exm; before sort it should work:

Code Select Expand

$context['page_index'] = constructPageIndex($scripturl . '?action=mlist;sa=exm;sort=' . $_REQUEST['sort'] . (isset($_REQUEST['desc']) ? ';desc' : ''), $_REQUEST['start'], $context['num_members'], $modSettings['defaultMaxMembers']);

[Antechinus]

Ok I sorted that and it works now. Except for one tiny glitch of course (as there always is). The only thing stopping it being perfect now is the sorting by position option. That returns an sql error.

Code Select Expand

URL http://127.0.0.1/CEMB_mirror/index.php?action=mlist;sa=exm;sort=ID_GROUP;start=0

Database Error: Column 'ID_GROUP' in where clause is ambiguous
File: G:\VertrigoServ\www\CEMB_mirror\Sources\Memberlist.php
Line: 564

and line 564 is the last one in this block:

Code Select Expand

   // Select the members from the database.
   $request = db_query("
      SELECT mem.ID_MEMBER
      FROM {$db_prefix}members AS mem" . ($_REQUEST['sort'] === 'isOnline' ? "
         LEFT JOIN {$db_prefix}log_online AS lo ON (lo.ID_MEMBER = mem.ID_MEMBER)" : '') . ($_REQUEST['sort'] === 'ID_GROUP' ? "
         LEFT JOIN {$db_prefix}membergroups AS mg ON (mg.ID_GROUP = IF(mem.ID_GROUP = 0, mem.ID_POST_GROUP, mem.ID_GROUP))" : '') . "
      WHERE mem.is_activated = 1
         AND (ID_GROUP = 14 OR FIND_IN_SET(14, additionalGroups))" . (empty($where) ? '' : "
         AND $where") . "
      ORDER BY " . $sort_methods[$_REQUEST['sort']][$context['sort_direction']] . "
      LIMIT $limit, $modSettings[defaultMaxMembers]", __FILE__, __LINE__);

[Arantor]

Blah, I forgot about that. It's complaining that it doesn't know which table ID_GROUP is supposed to be from. In this case it's supposed to be from the members table.

Code Select Expand

   // Select the members from the database.
   $request = db_query("
      SELECT mem.ID_MEMBER
      FROM {$db_prefix}members AS mem" . ($_REQUEST['sort'] === 'isOnline' ? "
         LEFT JOIN {$db_prefix}log_online AS lo ON (lo.ID_MEMBER = mem.ID_MEMBER)" : '') . ($_REQUEST['sort'] === 'ID_GROUP' ? "
         LEFT JOIN {$db_prefix}membergroups AS mg ON (mg.ID_GROUP = IF(mem.ID_GROUP = 0, mem.ID_POST_GROUP, mem.ID_GROUP))" : '') . "
      WHERE mem.is_activated = 1
         AND (mem.ID_GROUP = 14 OR FIND_IN_SET(14, mem.additionalGroups))" . (empty($where) ? '' : "
         AND $where") . "
      ORDER BY " . $sort_methods[$_REQUEST['sort']][$context['sort_direction']] . "
      LIMIT $limit, $modSettings[defaultMaxMembers]", __FILE__, __LINE__);

[Antechinus]

Awesome. We have a winner. All functions works and there are no errors in the log. Thanks.

[Arantor]

Good to hear it's working.

Now if only I could work similar magic on the delete/recycling for SimpleDesk...

[Antechinus]

Oh this is the final file for anyone who wants to do something similar.