Sort articles by seq() value

Question

Im trying to create a view for most viewed articles. I tried with entryCount plugin as recommended here

but unfortunately, when you have more than 500 articles in the last 3 weeks that I'm checking, the plugin becomes really slow.

So i had an idea to do something like this:

With each entry view, I'm incrementing the counter

seq('views:' ~ entryId, next=true)

and then i have partial where im querying posts like this

{% set lastWeek = date('3 days ago')|atom %}
{% set limit, eager = 6, [['entryCover', {withTransforms: ['cover', 'desktopCover']}]] %}

{% set entries = craft.entries.section('vijesti').dateUpdated(">= #{lastWeek}").with(eager).orderBy('views ASC').limit(limit).collect() %}

I need to introduce an additional parameter (seq) to query and sort by highest value.

Thanks in advance

Answer 1

You can't order your entry query by view count directly if you use seq(), because there's no easy way to join the sequences table to an entry query. You can, however, query the sequences table directly to get the list of entries that have the most views, and then use that to find the corresponding entries. Whether this is a good idea is for history to decide.

{# Get the most viewed entries from the `sequences` table. #}
{% set highestViews = craft.query()
    .select(['name', 'next'])
    .from(constant('craft\\db\\Table::SEQUENCES'))
    .where(['like', 'name', 'views:%', false])
    .orderBy('next DESC')
    .pairs()
%}

{# Extract the entry IDs from the `name` column. #}
{% set entryIds = highestViews|keys|map(name => name|split(':')|last) %}

{# Get the corresponding entries in fixed order. #}
{% set entriesWithHighestViews = craft.entries()
    .id(entryIds)
    .fixedOrder()
    .limit(5)
    .all()
%}

A couple of caveats:

• You can add more conditions to the entry query and this will still work. You could also use different formats for the view count based on the current section (e.g. views:{sectionId}:{entryId}) or something similar, if you want to track those separately.
• If entries are removed or no longer relevant, their view count is kept around. This is why the first query doesn't include a limit. This will be slow if you have thousands or millions of entries. To address this, add a reasonably high limit that accounts for the fact that older entries with higher view counts might not be available any more, so the second query will return fewer results. You could also address this issue by using hooks to delete the view count from the sequences table as soon as the entries are deleted, or use a cron job to regularly remove view counts for elements that are no longer relevant. In any case, your templates should gracefully handle that case that entriesWithHighestViews might be an empty array, or include fewer entries than expected.
• You technically don't need to select the next value in the query, but I've added it if you want to display the actual count. If not, remove it from the select statement, execute the query with column() instead of pairs() and drop the |keys filter.
• This might need some adjustments if you need this to work in a multi-site setup.