XF 2.1 Get avatar when not using finder

[AndrewSimm]

I have a query that I am doing some aggregation through and therefore not using finder. I would also like to retrieve the avatars of the users that are returned. The problem I have is when I loop through the variable in the template I am now in user order and not the order defined in the query. For example, below the data does not return in descending order. Is there a different way I should be doing this?

        $db = \XF::db();
        $values = $db->fetchAllKeyed("
                                select
                                 xf_user.user_id
                                ,count(case when xf_reaction.reaction_score > 0 then 1 end) as positive_reactions
                                ,count(case when xf_reaction.reaction_score = 0 then 1 end) as neutral_reactions
                                ,count(case when xf_reaction.reaction_score < 0 then 1 end) as negative_reactions
                                from xf_reaction
                                inner join xf_reaction_content on xf_reaction.reaction_id = xf_reaction_content.reaction_id
                                inner join xf_user on xf_reaction_content.reaction_user_id = xf_user.user_id
                                where xf_reaction_content.content_user_id = ?
                                and xf_reaction_content.is_counted = 1
                                group by xf_user.user_id
                                order by positive_reactions desc
                                limit 50","user_id", $userId);

        $users = \XF::em()->findByIds('XF:User', array_keys($values));

                            <xf:foreach loop="$users" value="$user" key="$userId">
                                <tr class="dataList-row">
                                    <td class="dataList-cell">
                                        <div class="contentRow contentRow--alignMiddle">
                                            <div class="contentRow-figure">
                                                <xf:avatar user="$user" size="xs" />
                                            </div>
                                            <div class="contentRow-main">
                                                <a href="{{ link('members/', $user) }}">{$user.username}</a>
                                            </div>
                                        </div>
                                    </td>
                                    <td class="dataList-cell">
                                        {$values.{$userId}.positive_reactions}
                                    </td>
                                    <td class="dataList-cell">
                                        {$values.{$userId}.neutral_reactions}
                                    </td>
                                    <td class="dataList-cell">
                                        {$values.{$userId}.negative_reactions}
                                    </td>
                                </tr>
                            </xf:foreach>

 

[Lukas W.]

Use the sortByList() function of the array collection class to sort the collection that is returned by the entity manager.

 

[AndrewSimm]

Use the sortByList() function of the array collection class to sort the collection that is returned by the entity manager.

Thanks Lukas. I am still not having much luck. I guess I don't understand how "sortByList" works. How am I telling it to sorty by the column I used in the query. Looking at some of the other usages, it looks like they sort by "ids"; whereas, I am wanting to sort by "positive_reactions desc".

$db = \XF::db();
$values = $db->fetchAllKeyed("
                        select
                         xf_user.user_id
                        ,count(case when xf_reaction.reaction_score > 0 then 1 end) as positive_reactions
                        ,count(case when xf_reaction.reaction_score = 0 then 1 end) as neutral_reactions
                        ,count(case when xf_reaction.reaction_score < 0 then 1 end) as negative_reactions
                        from xf_reaction
                        inner join xf_reaction_content on xf_reaction.reaction_id = xf_reaction_content.reaction_id
                        inner join xf_user on xf_reaction_content.reaction_user_id = xf_user.user_id
                        where xf_reaction_content.content_user_id = ?
                        and xf_reaction_content.is_counted = 1
                        group by xf_user.user_id
                        having count(case when xf_reaction.reaction_score < 0 then 1 end) > 0
                        order by positive_reactions desc
                        limit 50","user_id", $userId);

$users = \XF::em()->findByIds('XF:User', array_keys($values));
$users->sortByList($values);

 

[Lukas W.]

You put your $values array into that method and it'll resort your array collection based on that.