smd_lately: recently viewed site articles

Bloke · 2010-05-28 09:07:11

maniqui wrote:

I put my programmer-wannabe hat on, and found a way to let smd_lately work with both type of URLs

Genius find, thanks maniqui. Implemented in v0.22, along with a fix for my daft class oversight.

P.S. If in doubt, there’s almost always an undocumented debug attribute in my plugins :-)

Last edited by Bloke (2010-05-28 09:14:35)

pieman · 2010-06-10 14:21:02

A client would like to show the most popular articles in the last 30 days out of those published in the last 30 days.

I set within="30 days" to limit log range, but can’t figure out how to define the date-posted limits using from.

In the smd_lately help:

from : date / time stamp (written in English) of the earliest date to consider in the logs. You may specify ?month and/or ?year to have the plugin replace it with the current month/year. Default: unset

What are the valid values we can enter here? Is there a way to do what I’m looking for without hackery?

maniqui · 2010-06-10 19:05:44

Hi pieman,

Currently, it doesn’t seem to be possible, using smd_lately, to filter the list of most popular articles, just including those published on the last 30 days.
Thinking a little about it, I think this is the correct reasoning:

1) get a list of IDs for articles published on last 30 days.
2) Sort them by popularity, based on TXP log.

I would try this approach:

1) generate a comma-separated list of IDs for articles published on the last 30 days, using smd_query or article_custom (probably not doable with article_custom)
2) generate a comma-separated list of IDs for most popular articles in the last 30 days, using smd_lately. Use a high “limit” value, so your comma-separated list of IDs is a “pool” of article ids already sorted by popularity.
3) use article_custom, and feed its “id” attribute with the list generated in 1, and the “sort” attribute, with the list generated in 2.

Like this:

<txp:article_custom id="list_of_ids_for_articles_published_in_last_30_days" sort="FIELD(ID,already_sorted_list_of_ids_for_most_popular_articles_in_last_30_days)" />

I’m not sure if the sort="FIELD(ID,...)" trick will work if you feed it with a list of IDs larger than the ones being feed to id attribute.
In that case, you may have to do some pre-processing of the already_sorted_list_of_ids_for_most_popular_articles_in_last_30_days, to remove any id that is not in the list_of_ids_for_articles_published_in_last_30_days, but at the same time, keeping the sorting.

So, if you want to test, first, I would suggest you to manually try if something like this works:

<txp:article_custom id="5,7,13,20,22" sort="FIELD(ID,54,20,7,80,13,22,33,5)" />

That is, manually pick up a list of articles, and manually/randomly sort them.
Check if the output is correct. Following the example, the articles should be output in this order: 20,7,13,22,5.

You may also like to test what happens if some article id from the last published articles, doesn’t appear in the list of most popular articles. That’s a possible scenario, if one of articles published on the last 30 days isn’t popular at all.
In other words, and similar to the test above, you may want to try something like this:

<txp:article_custom id="5,7,13,20,22" sort="FIELD(ID,54,20,7,80,13,33)" />

As you can see, the sort="FIELD(ID,...)" doesn’t include some of the ids (5, 22) that are included in the id attribute.
No idea what would happen on that case.

Well, this are some ideas off the top of my head. Hope they make some sense!

maniqui · 2010-06-26 15:14:00

Hi Bloke,

A client’s site has been deactivated by his hosting company. The hosting support informed the client that they have deactivated the website because it was taking up too many database resources.
They also informed that the offending query is this:

Here are the mysql logs traced on the server :-

Database User: dummy_user for database dummy_txp 
=================================================== 
| 39992 | dummy_user | localhost | dummy_txp | Query | 117 | closing tables | SELECT count(page) as popularity, page, MAX(time) as time FROM txp_log WHERE 1=1 AND page NOT LIKE ' | 
| 39995 | dummy_user | localhost | dummy_txp | Query | 117 | closing tables | SELECT count(page) as popularity, page, MAX(time) as time FROM txp_log WHERE 1=1 AND page NOT LIKE ' | 
| 40008 | dummy_user | localhost | dummy_txp | Query | 117 | closing tables | SELECT count(page) as popularity, page, MAX(time) as time FROM txp_log WHERE 1=1 AND page NOT LIKE ' | 
| 40009 | dummy_user | localhost | dummy_txp | Query | 117 | closing tables | SELECT count(page) as popularity, page, MAX(time) as time FROM txp_log WHERE 1=1 AND page NOT LIKE ' | 
| 40011 | dummy_user | localhost | dummy_txp | Query | 117 | closing tables | SELECT count(page) as popularity, page, MAX(time) as time FROM txp_log WHERE 1=1 AND page NOT LIKE ' | 
| 40014 | dummy_user | localhost | dummy_txp | Query | 104 | Copying to tmp table | SELECT count(page) as popularity, page, MAX(time) as time FROM txp_log WHERE 1=1 AND page NOT LIKE ' | 
| 40015 | dummy_user | localhost | dummy_txp | Query | 93 | Copying to tmp table | SELECT count(page) as popularity, page, MAX(time) as time FROM txp_log WHERE 1=1 AND page NOT LIKE ' | 
| 40018 | dummy_user | localhost | dummy_txp | Query | 107 | closing tables | SELECT count(page) as popularity, page, MAX(time) as time FROM txp_log WHERE 1=1 AND page NOT LIKE ' | 
| 40047 | dummy_user | localhost | dummy_txp | Query | 107 | closing tables | SELECT count(page) as popularity, page, MAX(time) as time FROM txp_log WHERE 1=1 AND page NOT LIKE ' | 
| 40049 | dummy_user | localhost | dummy_txp | Query | 100 | Copying to tmp table | SELECT count(page) as popularity, page, MAX(time) as time FROM txp_log WHERE 1=1 AND page NOT LIKE ' | 
| 40050 | dummy_user | localhost | dummy_txp | Query | 17 | closing tables | select *, unix_timestamp(Posted) as uPosted, unix_timestamp(Expires) as uExpires, unix_timestamp(Las | 
| 40053 | dummy_user | localhost | dummy_txp | Query | 100 | Copying to tmp table | SELECT count(page) as popularity, page, MAX(time) as time FROM txp_log WHERE 1=1 AND page NOT LIKE ' | 
| 40065 | dummy_user | localhost | dummy_txp | Query | 107 | closing tables | SELECT count(page) as popularity, page, MAX(time) as time FROM txp_log WHERE 1=1 AND page NOT LIKE ' | 
| 40069 | dummy_user | localhost | dummy_txp | Query | 100 | Copying to tmp table | SELECT count(page) as popularity, page, MAX(time) as time FROM txp_log WHERE 1=1 AND page NOT LIKE ' | 
| 40072 | dummy_user | localhost | dummy_txp | Query | 100 | Copying to tmp table | SELECT count(page) as popularity, page, MAX(time) as time FROM txp_log WHERE 1=1 AND page NOT LIKE ' | 
| 40082 | dummy_user | localhost | dummy_txp | Query | 107 | closing tables | SELECT count(page) as popularity, page, MAX(time) as time FROM txp_log WHERE 1=1 AND page NOT LIKE ' | 
| 40089 | dummy_user | localhost | dummy_txp | Query | 100 | Copying to tmp table | SELECT count(page) as popularity, page, MAX(time) as time FROM txp_log WHERE 1=1 AND page NOT LIKE ' | 
===================================================

The plugin usage is the following:

<txp:smd_lately by="" sort="popularity" within="30 days" limit="10" wraptag="ul" break="li">
  <h3><txp:permlink><txp:title /></txp:permlink></h3>
  <p><txp:rvm_substr length="100"><txp:etz_striptags><txp:excerpt /></txp:etz_striptags></txp:rvm_substr></p>
</txp:smd_lately>

Do you have any idea of what could be going on with this and if there is any measure that could be taken to avoid this issue?

Thank you.

Bloke · 2010-06-27 08:41:00

maniqui wrote:

Do you have any idea of what could be going on with this and if there is any measure that could be taken to avoid this issue?

Hmmm, short answer: not sure. Granted the smd_lately tag can be a little heavy because of all the regex stuff going on — it has to exclude a lot of stuff like any potential landing pages, plus it uses 1 query for each result to grab the corresponding article info to allow you to use article tags.

In this case you only use 10 article queries and have 30 days’ worth of results to consider. To dig further we’d have to look at a few more bits of info:

How big the log table is — total number of entries — and roughly how many of those fall in the last 30 days. Consider reducing the size of the log window if possible
How often the smd_lately tag is called — is it every page or only on certain pages? Again, thinning out the density of calls might help
Your tag trace — or at least the query execution time and memory usage info. That might give us a clue of the load on certain pages
Consider wrapping the smd_latey tag in rah_runtime to find out if the lion’s share of total page execution time is actually that tag or something else is contributing
PHP/MySQL version — just for the sake of completeness

One way to circumvent this, which I could perhaps look at implementing, is a cache. If you could somehow control the freshness of the cache using some timeout option — say, 5 minutes — then you could reduce server load at the expense of less up-to-date information and balance it as you see fit. Because of the potentially large nature of the result set I’d probably cache it in a file in the tmp directory instead of the database.

In the meantime you could implement a poor-man’s cache yourself. Untested but something like this in pseudocode:

if ((current time - qui_stored_time ) > 300)
do:
   <txp:smd_lately ... /> : store result in prefs as qui_lately_tag
   Store time() in prefs as qui_stored_time
else: Retrieve qui_lately_tag

Just set qui_stored_time manually with the current time before you start. In fact I’ve just had a thought that perhaps the smd_prefalizer plugin could have a <txp:smd_set_pref> and <txp:smd_get_pref> public-side tags which would facilitate setting this up very easily. I might just do that if it’d help you out (with suitable controls to disallow clobbering TXP’s built-in prefs!) Let me know if this’d be useful.

Alternatively, if you didn’t want to mess about with the prefs directly, you could use smd_vars to stash the stored_time.

Last edited by Bloke (2010-06-27 08:41:44)

maniqui · 2010-06-27 15:07:12

Hi Bloke,
many thanks for the thoroughly reply. I’ll investigate the options, probably implementing a cache-based solution (aks_cache) as a quick solution.
BTW, Is there a release of smd_vars?

Bloke · 2010-06-27 15:10:56

maniqui wrote:

Is there a release of smd_vars?

Not officially. Could do with one really. One fine day… but hold fire for an hour or so: I’ve updated smd_prefalizer to insert/query prefs from the public side so you can be my guinea pig for that. I’ll drop you a mail once it’s documented.

jelle · 2010-09-09 12:37:03

Hi stef,

Sounds like a great plugin but I just can’t get it to work. Tried regular and zipped version, tried different tags and setting…it just won’t output anything for me.

smd_lately 0.22
txp 4.2
php 5.2

Bloke · 2010-09-09 12:39:18

jelle wrote:

Sounds like a great plugin but I just can’t get it to work. Tried regular and zipped version, tried different tags and setting…it just won’t output anything for me.

Can you please post the tag you’re using? And check you have logging set to All Results and that there are some entries in your Visitor Logs.

Last edited by Bloke (2010-09-09 12:39:41)

jelle · 2010-09-09 15:16:17

Check, check, check
Using just <txp:smd_lately/>
Logging is set to All Results.
Got some entries.

Shall I pm you a login….I’m probably doing something stupid. :)

Bloke · 2010-09-09 16:36:58

jelle wrote:

Check, check, check

OK, cool. Just checking :-)

Using just <txp:smd_lately/>

Right, so that’s only showing results per user. If you’re testing it yourself it should only show articles you’ve visited. It might be worth tring to add by="" just to see if it’s working at all. Also adding debug="2" will show you if there’s any output. If that doesn’t help give some insight into what’s going on, by all means send me a login and I’ll see if I can find out what’s going on.

jelle · 2010-09-10 11:45:06

Hi bloke.

Thanks for pointing out that debug attribute. Gave me some insight to the problem.

This morning, without any changed to my test setup, smd_lately started spitting out articles where yesterday it would just sit there in silence. I’m guessing I have it’s a date/time issue somewhere.

To reproduce I wrote some articles. Clicked on them on the frontend…without result by smd_lately. After pushing back the publish date for the articles with 1 day smd_lately shows them.

My settings for timezone and daylight saving time are all correct. Setting the ‘to’ attribute to a future day didn’t work.
Here’s my debug code

select name from txp_section where 1=1

++ smd_lately RULES ++

array (
  0 => 'page NOT LIKE \'/\'',
  1 => 'page NOT LIKE \'%q=%\'',
  2 => 'page NOT LIKE \'%c=%\'',
  3 => 'page NOT LIKE \'%pg=%\'',
  4 => 'page NOT LIKE \'%category=%\'',
  5 => 'page NOT LIKE \'/category/%\'',
  6 => 'page NOT LIKE \'%author=%\'',
  7 => 'page NOT LIKE \'/author/%\'',
  8 => 'page NOT REGEXP \'^/(aaa|default)/?$\'',
)

SELECT count(page) as popularity, page, MAX(time) as time FROM txp_log WHERE 1=1 AND ip='127.0.0.1' AND page NOT LIKE '/' AND page NOT LIKE '%q=%' AND page NOT LIKE '%c=%' AND page NOT LIKE '%pg=%' AND page NOT LIKE '%category=%' AND page NOT LIKE '/category/%' AND page NOT LIKE '%author=%' AND page NOT LIKE '/author/%' AND page NOT REGEXP '^/(aaa|default)/?$' AND status = 200 GROUP BY page ORDER BY time desc

array (
  0 => 
  array (
    'popularity' => '1',
    'page' => '/textpattern-4.2.0/index.php?id=3',
    'time' => '2010-09-10 13:06:05',
  ),
  1 => 
  array (
    'popularity' => '4',
    'page' => '/textpattern-4.2.0/aaa/test-3',
    'time' => '2010-09-10 12:51:49',
  ),
  2 => 
  array (
    'popularity' => '2',
    'page' => '/textpattern-4.2.0/aaa/test-1',
    'time' => '2010-09-10 12:50:04',
  ),
  3 => 
  array (
    'popularity' => '1',
    'page' => '/textpattern-4.2.0/aaa/test-5',
    'time' => '2010-09-10 12:49:51',
  ),
  4 => 
  array (
    'popularity' => '1',
    'page' => '/textpattern-4.2.0/aaa/test-2',
    'time' => '2010-09-10 12:49:48',
  ),
  5 => 
  array (
    'popularity' => '1',
    'page' => '/textpattern-4.2.0/',
    'time' => '2010-09-10 12:49:34',
  ),
)

select *, unix_timestamp(Posted) as uPosted, unix_timestamp(Expires) as uExpires, unix_timestamp(LastMod) as uLastMod from textpattern where url_title='index.php?id=3' AND Posted < CURDATE()

select *, unix_timestamp(Posted) as uPosted, unix_timestamp(Expires) as uExpires, unix_timestamp(LastMod) as uLastMod from textpattern where url_title='test-3' AND Posted < CURDATE()

select *, unix_timestamp(Posted) as uPosted, unix_timestamp(Expires) as uExpires, unix_timestamp(LastMod) as uLastMod from textpattern where url_title='test-1' AND Posted < CURDATE()

select *, unix_timestamp(Posted) as uPosted, unix_timestamp(Expires) as uExpires, unix_timestamp(LastMod) as uLastMod from textpattern where url_title='test-5' AND Posted < CURDATE()

select *, unix_timestamp(Posted) as uPosted, unix_timestamp(Expires) as uExpires, unix_timestamp(LastMod) as uLastMod from textpattern where url_title='test-2' AND Posted < CURDATE()

select *, unix_timestamp(Posted) as uPosted, unix_timestamp(Expires) as uExpires, unix_timestamp(LastMod) as uLastMod from textpattern where url_title='textpattern-4.2.0' AND Posted < CURDATE()

In the above code all the articles are dated 10th of september except “222” which is dated 9th of september. Only 222 is output by smd_lately. Don’t know how to go on. Is it a bug or maybe a server config issue? Any help is greatly appreciated, really like the functionality of this plugin.

^{Edited for code display — Stef}

Last edited by Bloke (2010-09-10 11:55:02)

Textpattern CMS

Textpattern CMS support forum

#37 2010-05-28 09:07:11

Re: smd_lately: recently viewed site articles

#38 2010-06-10 14:21:02

Re: smd_lately: recently viewed site articles

#39 2010-06-10 19:05:44

Re: smd_lately: recently viewed site articles

#40 2010-06-26 15:14:00

Re: smd_lately: recently viewed site articles

#41 2010-06-27 08:41:00

Re: smd_lately: recently viewed site articles

#42 2010-06-27 15:07:12

Re: smd_lately: recently viewed site articles

#43 2010-06-27 15:10:56

Re: smd_lately: recently viewed site articles

#44 2010-09-09 12:37:03

Re: smd_lately: recently viewed site articles

#45 2010-09-09 12:39:18

Re: smd_lately: recently viewed site articles

#46 2010-09-09 15:16:17

Re: smd_lately: recently viewed site articles

#47 2010-09-09 16:36:58

Re: smd_lately: recently viewed site articles

#48 2010-09-10 11:45:06

Re: smd_lately: recently viewed site articles

Board footer