Question

Photo of DJ Grick

0

Small Group List by Parent Group

5

Our small group coordinator asked me to create a page for each of our small groups hubs which lists all the groups in that hub, a description of the group, leaders names, leaders pictures(pulled from their profile), and meeting time/location.

I have played with this some but am having group coming up with a best approach.  Our small groups are all the same group type (small groups) and the hubs over them are all the same group type (Small Group Hubs). 

The issue with the Dynamic Data block for me was that most groups have multiple leaders so the query was returning different lines for each. I am new to SQL but couldn't figure out how I would list each leader under the correct group.

select g.Name, g.description, m.Personid, m.GroupRoleId, p.nickname, p.LastName, p.PhotoId

from [group] g

join [groupMember] M on g.id=m.groupid

join [person] P on m.PersonId=p.id

where g.parentgroupid=4758 and m.GroupRoleId=36


The Group List block was out because it won't let me pull child groups of a parent and didn't allow formatting for the public site. The Group List Personalized Lava would one produce groups I am in. 

What eventually seemed most promising was using the Group Detail Lava, setting the group to the Small group hub, and including the child group but i could never get the Lava to work 

{% for groups in Group.Groups %} {{ Group.Groups.Name }}<br>   {{ Group.Groups.Description}} {% endfor %}

The other issue I know I will run into with the Group Detail Lava is trying to figure out how to get Leaders pictures...but there's got to be a way right? :-) 

Has anyone created anything like this or have any suggestions? 


  • Photo of Michael Garrison

    0

    OK, I've created completely functional version of this hub at https://rock.rocksolidchurchdemo.com/Page/5408 (of course this link will stop working in a few days when the site resets).

    That first page is an "index" of all of the groups in the system of type "Small Group Section" (I'm presuming you have this organizing group type for each "hub" you want).

    It uses the following code in an HTML block with rockEntity turned on and cache set to 0:

    <div class="panel panel-default list-as-blocks clearfix">
    <div class="panel-body">
        <ul>
            {% group where:'GroupTypeId == 24' %}
                {% for group in groupItems %}
                    {% grouptype where:'Id == {{ group.GroupTypeId }} && IsActive == True && IsPublic == True' %}
                        {% for grouptype in grouptypeItems %}
                            {% assign theIcon = grouptype.IconCssClass %}
                        {% endfor %}
                    {% endgrouptype %}
                    <li>
                        <a title="" href="/page/5409?ParentGroupId={{ group.Id }}">
                            <i class="{{ theIcon }}"></i>
                            <h3>{{ group.Name }}</h3>
                        </a>
                    </li>
                {% endfor %}
            {% endgroup %}
        </ul>
    </div>
</div>

    If you're using the stock small group section and small group group types, the only thing you should have to change is the href - that page number should be set to a child page that you want to use to actually show each hub. In the example, the index page is /Page/5908 and the hub display page is /Page/5409, so replace 5409 in the above code with your sub-page's ID. This looks up ALL of the requested grouptype groups, and displays their name together with the grouptype icon assigned to them.

     

    The hub display (child page) has the following in an HTML block with rockEntity turned on and cache set to 0. It's very similar to what I posted before, except it includes the IsActive and IsPublic requirements. I also included an extra height specification on one div and one more bit of javascript at the end:

    {% group dynamicparameters:'ParentGroupId' where:'IsActive == True && IsPublic == True' %}
    {% for group in groupItems %}
        <div class="col-md-4" style="padding:1em; text-align:center;">
            <div style="padding:0.5em; background-color:white; border:1px solid black; box-shadow:-5px 5px 5px #999; height:100%;">
                <h3>{{ group.Name }}</h3>
                <div class="row">
                    {% groupmember where:'GroupId == {{ group.Id}} && GroupRoleId == 24' %}
                        {% for member in groupmemberItems %}
                            {% person where:'Id == {{ member.PersonId }}' %}
                                {% for person in personItems %}
                                    <div class="col-md-6" style="text-align:center;"><img src="{{ person.PhotoUrl }}" alt="{{ person.FullName }}" style="height:60px;width:60px;border-radius:30px;" /><h4>{{ person.FullName }}</h4></div>
                                {% endfor %}
                            {% endperson %}
                        {% endfor %}
                    {% endgroupmember %}
                </div>
                <p style="font-weight:bold;">{{ group.Description }}</p>
                Meeting Time: {{ group.Schedule.FriendlyScheduleText }}<br />
                Meeting Location:<br />
                {% assign theLocation = group.GroupLocations | First %}{{ theLocation.Location.Street1 }}<br />
                {% if theLocation.Location.Street2.Size > 0 %}
                    {{ theLocation.Location.Street2 }}<br />
                {% endif %}
                {{ theLocation.Location.City }}, {{ theLocation.Location.State }} {{ theLocation.Location.PostalCode }}
            </div>
        </div>
    {% endfor %}
{% endgroup %}
<script>$( document ).ready(function() {
    var heights = $(".col-md-4").map(function() {
        return $(this).height();
    }).get(),

    maxHeight = Math.max.apply(null, heights);

    $(".col-md-4").height(maxHeight);
});</script>

    About that height specification and the script at the end: when I ran this on the Rock demo site, the groups wrapped oddly since some were taller than others. It looked messy. So I grabbed this snippet from http://stackoverflow.com/questions/19695784/how-can-i-make-bootstrap-columns-all-the-same-height and, once we set the inner DIVs to fill 100% of their parent's height, it looks nice and organized now.

    (Feel free to delete just the content between <script> and </script> and reload the page to see what I mean, perhaps this isn't a big deal on your own configuration, if all of the group content is roughly the same size for you).

    Of course, the demo site isn't a great example- most of the groups aren't "really" filled out with leaders, addresses and the like. And extra groups have been made by other users, some of the wrong type. But if the above screenshot that I posted from my own dev server looked like you want, this will match (but with full-height boxes). So hopefully this gets you even closer to a turnkey solution on your own server.

    grouphub2.JPG
    • Michael Garrison

      Michael Garrison

      OK, one last comment. On the demo site presently, there's a "small group section" group which is a child of another "small group section". The above code will show ALL child groups of the selected small group section, including other small group sections. If you want the display page to show ONLY actual small groups, you can add " && GroupTypeId == 25 " inside the single quote marks on the first line (after IsPublic == True ).

      • DJ Grick

        DJ Grick

        One other note from what Michael and I found in a slack conversation: this solution only works for users given securtiy access through Security/Entity Administration Rock.Model.Group.

      • Photo of Michael Garrison

        0

        DJ,

        I haven't published it yet because I'm working with our small group ministry to revise one of the ways we have for viewing our groups, but I've got a draft of a post describing how to set up a new small group view/navigation scheme. Check out the draft at http://shouldertheboulder.com/Article?id=458 to see if it helps any, but the gist is that I use DynamicData blocks extensively, and I think we can probably get at the SQL you need.

        I'm not sure exactly what you'd like to see on your page, but it sounds like your "hubs" are like our "Districts", so the page described in the second part of my post may work well- it's got buttons at the top to allow you to view a single group of groups, then below that you have a list of Groups, Leaders and Members within that "hub", with a note about which group(s) they're in:

        SmallGroupPortal-Pastors.png


        I'm trying to picture what you're going for though- are you wanting a single page per "hub" that has, perhaps, a box for each group in the hub, which lists the group name and leaders as headers for each box, and the members below?

      • Photo of Michael Garrison

        0

        DJ,

        If my guess of what you're looking for was correct, this DynamicData query may do the trick for you:

        SELECT
    g.[Id] "GroupId"
    ,g.[Name] "GroupName"
    ,gm.[PersonId] "PersonId"
    ,gm.[GroupRoleId] "RoleId"
FROM
    [Group] g
    LEFT JOIN [GroupMember] gm ON g.[Id]=gm.[GroupId]
    LEFT JOIN [Person] p ON gm.[PersonId]=p.[Id]
WHERE
    g.[ParentGroupId]=@ParentGroupId
    AND g.[IsActive]=1
ORDER BY
    g.[Order]
    ,g.[Id]
    ,CASE gm.[GroupRoleId]
      WHEN 24 THEN 1
      ELSE 2
   END
   ,p.[LastName]
   ,p.[FirstName]

        (If your Leader role is different than Rock's built-in Small Group Role (GroupRoleId=24) then you'll need to edit that number near the end of the query).

        Then you need to use ParentGroupId=0 in the Parameters box, and use the following Formatted Output (delete the {% raw %} and {% endraw %} tags at the beginning and the end- that's a by-product of these forums):

        {% raw %}{% assign CurrentGroup = 0 %}
<div class="row">
{% for row in rows %}
    {% if row.GroupId != CurrentGroup %}
        {% if CurrentGroup != 0 %}
            </div>
        {% endif %}
        {% assign CurrentGroup = row.GroupId %}
        <div class="col-md-4"><h3 style="text-align:center;">{{ row.GroupName }}</h3>
    {% endif %}
    {% assign CurrentPerson = row.PersonId | PersonById %}
    {% if row.RoleId == '24' %}
        <img style="height:3em;width:3em;" alt="{{ CurrentPerson.FullName }}" src="{{ CurrentPerson.PhotoUrl }}"><b>
    {% endif %}
    {{ CurrentPerson.FullName }}
    {% if row.RoleId == '24' %}
        </b>
    {% endif %}
    <br>
{% endfor %}</div>{% endraw %}

        Then load the page with a parameter at the end of the URL like ?ParentGroupId=4758. The SQL returns the group information for EVERY group member where the group has the specified parent, then the Lava in the Formatted Output loops through the results looking for where the group ID changes from one group to the next; only at those boundaries does it close the previous group "box" and start another.

        Is this perhaps close to what you want?

        SmallGroupPortal-hub.png

      • Photo of DJ Grick

        0

        Michael, 

        Thanks so much for all the time you spent on the reply. Sorry it's been two weeks already and I have not gotten back to this. I read everything over today but need to read again after getting a little more sleep and I will start trying to pull from it. I just wanted to reply before anymore time passed. 

        A really basic example of what we had in mind would be something like this for each group under a hub. 


        Smallgroups.png

        Oops: "Child care is privided"

      • Photo of Michael Garrison

        0

        DJ,

        Perhaps LavaEntity commands are going to be more approachable here; there are a LOT of joins you'd have to build into your query to get everything you want outputted from SQL. So instead of a DynamicData block, add an HTML block to a page, then turn on RockEntity commands in the block settings (this requires Rock v6.0 or later). While you're there, set the Cache Duration to 0 as well. Then use the following content in code editor mode of the "Edit HTML" dialogue

        {% group dynamicparameters:'ParentGroupId' %}
    {% for group in groupItems %}
        <div class="col-md-4" style="padding:1em; text-align:center;">
            <div style="padding:0.5em; background-color:white; border:1px solid black; box-shadow:-5px 5px 5px #999;">
                <h3>{{ group.Name }}</h3>
                <div class="row">
                    {% groupmember where:'GroupId == {{ group.Id}} && GroupRoleId == 24' %}
                        {% for member in groupmemberItems %}
                            {% person where:'Id == {{ member.PersonId }}' %}
                                {% for person in personItems %}
                                    <div class="col-md-6" style="text-align:center;"><img src="{{ person.PhotoUrl }}" alt="{{ person.FullName }}" style="height:60px;width:60px;border-radius:30px;" /><h4>{{ person.FullName }}</h4></div>
                                {% endfor %}
                            {% endperson %}
                        {% endfor %}
                    {% endgroupmember %}
                </div>
                <p style="font-weight:bold;">{{ group.Description }}</p>
                Meeting Time: {{ group.Schedule.FriendlyScheduleText }}<br />
                Meeting Location:<br />
                {% assign theLocation = group.GroupLocations | First %}{{ theLocation.Location.Street1 }}<br />
                {% if theLocation.Location.Street2.Size > 0 %}
                    {{ theLocation.Location.Street2 }}<br />
                {% endif %}
                {{ theLocation.Location.City }}, {{ theLocation.Location.State }} {{ theLocation.Location.PostalCode }}
            </div>
        </div>
    {% endfor %}
{% endgroup %}

        When you load the page with a parameter like ?ParentGroupId=4758, this outputs the groups within the parent you identify in a way that I based on your example- it comes reasonably close, I think-  at least close enough to be able to tweak to match what you want:

        SmallGroupSection.png

        (If the GroupRoleId of your group leaders is different than 24, you'll need to change that in the HTML block content. I think everything else should be pretty reliable though). 

        • DJ Grick

          DJ Grick

          Michael, this is awesome! I don't know if I would have ever figured it out! Thank you sooooo much. I know Lava is powerfully but keep struggling to wrap my mind around it.


          Anyway hopefully Thursday I'll have time to double back and add "IsPublic" and "IsActive" requirements...but can't get that working tonight.


          Thanks for all the work you did on this!