XF 2.2 Find $total from fetchAllKeyed

[AndrewSimm]

I have been trouble using fetchAllKeyed and getting pagination to work. The problem is I don't have a finder to get total from/

Here is the start of my code, and this works fine.

$page = $this->filterPage();
$perPage = 40;
$offset = ($page - 1) * $perPage;

$db = \XF::db();

$values = $db->fetchAllKeyed("select xf_user.user_id, xf_user.username, xf_user.message_count, xf_user.warning_points, sum(xf_report.report_count) as report_count
                                    from xf_user
                                    inner join xf_report on xf_user.user_id = xf_report.content_user_id
                                    group by xf_user.user_id, xf_user.username, xf_user.message_count, xf_user.warning_points
                                    having sum(xf_report.report_id) > 0
                                    order by report_count DESC
                                    limit ?,?", 'user_id', [$offset, $perPage]);

Here is the only one I know to get total.

$total  = $db->fetchAll("select xf_user.user_id, xf_user.username, xf_user.message_count, xf_user.warning_points, sum(xf_report.report_count) as report_count
                                    from xf_user
                                    inner join xf_report on xf_user.user_id = xf_report.content_user_id
                                    group by xf_user.user_id, xf_user.username, xf_user.message_count, xf_user.warning_points
                                    having sum(xf_report.report_id) > 0
                                    order by report_count DESC");

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

I imagine I should be doing this a different way. What am I missing?

 

[Mike]

So as an initial comment, I don't think you need the HAVING clause. Summing the report_id doesn't really make sense (presumably you meant report_count?), but the presence of a row in the report table should indicate that there is a report.

I think to get the total you want, you could run this query:

SELECT COUNT(DISTINCT report.content_user_id)
FROM xf_report AS report
INNER JOIN xf_user AS user ON (user.user_id = report.content_user_id)
WHERE report.content_user_id > 0

Dropping the join would make this more performant with a potential for some edge cases if there are entries with invalid user IDs. I'd probably accept that.

Similarly, for the base query, I'd probably do: (Without the user join as just mentioned)

SELECT content_user_id, SUM(report_count) AS total
FROM xf_report
WHERE content_user_id > 0
GROUP BY content_user_id
ORDER BY total DESC
LIMIT x, y

And then I'd do a separate query to load proper user entities based on the fetched user IDs. You'd have 2 arrays you'd have to deal with but I think it'd be more performant and since you have full user entities, you'd have access to all the tools you'd normally need.