Optimized way to retrieve most-liked posts by category and time period

Resolved kasra naraghi
(@gamefa)

1 month ago
Hello,

I would like to implement a feature on my website using WP ULike.

I want to be able to retrieve the most-liked posts, grouped by category, and identify the most-liked posts on the site based on the number of likes within different time periods, such as:
- Weekly
- Monthly
- Quarterly (3 months)
For example, I would like to retrieve the top 10 most-liked posts in each category during the last 7 days, last 30 days, or last 3 months.

My main concern is database performance. Since my website has a large amount of content and likes, I want to implement this in the most optimized way possible with the lowest possible database load.

What approach would you recommend for this?

Specifically, would you recommend querying the WP ULike tables directly, using an existing WP ULike feature/API, creating a custom database query, or maintaining some kind of pre-calculated/aggregated data?

I would also appreciate your recommendation for the most efficient database structure or indexing strategy for this use case, especially if the rankings need to be generated regularly.

What would be the best and most optimized solution to achieve this while minimizing database queries and server load?

Thank you.

Viewing 2 replies - 1 through 2 (of 2 total)

Plugin Author Alimir
(@alimir)

4 weeks ago
Hi @gamefa ,

Thank you for the detailed analysis and for using WP ULike at this scale.

You are right that device, OS, and browser labels add up on a table with millions of rows. We agree this should be handled with a safe cleanup, not a risky schema rewrite.

We are working on this now and it will be released soon. The first version will:
- Clear only device, os, and browser on older rows
- Keep votes, counts, fingerprints, IPs, and identity fields intact
- Be off by default, with a setting and a WP-CLI command for large-table catch-up
Larger ideas (numeric like/dislike, IPv4 INT, lookup tables, splitting audit data) need a real migration, so they are not part of this first step.

We will share details in the changelog when it ships.
Plugin Author Alimir
(@alimir)

4 weeks ago

Hi @gamefa ,

You do not need a custom table or raw queries on the WP ULike logs. The plugin already has an optimized API for this.

Last 7 / 30 / 90 days, one category:

[wp_ulike_top limit="10" cat="news" days="7"] [wp_ulike_top limit="10" cat="news" days="30"] [wp_ulike_top limit="10" cat="news" days="90"]

Or in PHP:

echo wp_ulike_get_top_content( array( 'limit' => 10, 'days' => 7, 'cat' => 'news', ) );

The same options are in the Top List block. days=”7″ is a rolling 7 days. period=”week” / period=”month” are the current calendar week/month.

Top 10 in every category: there is no single “group by category” query — that would be heavier on a large site. Loop categories and call the same API once per category. If you show many categories or several periods on one page, store the result in a transient (or a daily cron) instead of running it on every request.

All-time rankings already use pre-aggregated counters. Period rankings use the Pulse table with date filters and are cached. Please use this API rather than querying the engagement tables directly.

Viewing 2 replies - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.