List all articles for a dynamic time period: limit 1 year?

johnstephens · 2013-10-24 15:26:03

I have a site for a publication, where the articles are compiled into issues by a custom field, and there are usually three issues per year, with a variable number of articles per issue. My archive page has an article list sorted by posted desc, and I use txp:if_different to insert headings with the issue number. So far so good.

But as the site grows, so does the article list. I want to paginate the article list, so that I don’t have to load all the articles in one view. The normal way to do this with an article tag is with the limit attribute, but with a variable number of articles per issue, any numeric limit on articles per page would result in page breaks within an issue.

Is there any way to set a limit for pagination based on the current date, so that a given page can show all articles published in the last twelve months or so?

Thanks!

uli · 2013-10-24 15:52:19

You can link to a year’s page by setting up links like this: yoursite.tld/?month=2012.

etc · 2013-10-24 18:05:42

If it’s ok to do it on the yearly basis, as suggested by Uli, you can construct a pagination bar like this:

<txp:etc_query data="SELECT DISTINCT YEAR(Posted) AS year FROM textpattern ORDER BY Posted desc">
	<a href="?month={year?}">{year?}</a>
</txp:etc_query>

You can also do it natively, with <txp:if_different /> and <txp:posted format="%Y" />, firstly retrieving all the articles, but the performance will be awful for large number of articles.

You could also paginate articles in groups of 3 issues (a bit more tricky, but feasible).

johnstephens · 2013-10-24 20:43:33

Thanks! Month-based listing was actually the first thing I looked at, but I really wanted to show since X months ago from “today”. Using Oleg’s pagination and a little bit of txp:variable action got me a tolerable workaround, though, and I’m happy with it. Here’s some of the extra stuff for the archive landing page:

<txp:variable name="omit">

<!--
    If this is a ?month page, get the URL year to determine articles to list
    and to mark which year is active in the nav.
-->
<txp:variable name="archive_year"><txp:page_url type="month"/></txp:variable>

<!--
    If this is the landing page without a ?month in the URL, get the current
    year for the same.
-->
<txp:if_variable name="archive_year" value=""><txp:variable name="archive_year"><txp:php>echo date('Y');</txp:php></txp:variable></txp:if_variable>

<!-- What if the current year doesn't have articles yet? Need to check. -->
<txp:variable name="archive_year_has_articles"><txp:article_custom
  limit="1"
  month='<txp:variable name="archive_year"/>'
  section="articles"
  sort="posted desc"><txp:article_id/></txp:article_custom></txp:variable>

<!-- If the current year doesn't have articles yet, get the previous year. -->
<txp:if_variable name="archive_year_has_articles" value=""><txp:adi_calc name="archive_year" subtract="1"/></txp:if_variable>

</txp:variable>

<!--
    txp:article_custom will use the <txp:variable name="archive_year"/>
    in the month attribute to list articles from the given year.
-->

<!--
    Finally, create some pagination links, with an "active" class on the given year.
-->
<ul><txp:etc_query data="SELECT DISTINCT YEAR(Posted) AS year FROM textpattern WHERE section='articles' AND status = 4 ORDER BY Posted desc">
<li<txp:if_variable name="archive_year" value="{year?}"> class='active'><txp:else/>><a href='/<txp:section/>/?month={year?}'></txp:if_variable>{year?}<txp:if_variable name="archive_year" value="{year?}"><txp:else/></a></txp:if_variable></li>
</txp:etc_query></ul>

Last edited by johnstephens (2013-10-24 20:49:25)

jstubbs · 2013-10-25 00:53:49

Hi John, if you ever get time, that would make for a good TXP Tip…!

etc · 2013-10-25 10:05:06

Let’s shorten it a bit:

<txp:etc_query
	data="SELECT DISTINCT YEAR(Posted) AS year FROM textpattern WHERE section='articles' AND status = 4 ORDER BY year DESC"
	query="//year[text() <= {?month|9999|intval}]" limit="1" globals="_GET"
>
	<!-- articles output -->
	<txp:article_custom section="articles" month="{?}" form="your_form" limit="999" />

	<!-- Navigation list -->
	<ul>
	{../preceding-sibling::row/year&=<li><a href='/<txp:section/>/?month={?}'>{?}</a></li>}
	{.&=<li class="active">{?}</li>}
	{../following-sibling::row/year&=<li><a href='/<txp:section/>/?month={?}'>{?}</a></li>}
	</ul>
</txp:etc_query>

Last edited by etc (2013-10-25 14:29:36)

johnstephens · 2014-04-02 18:29:17

Dear friends, I found a “glitch”. I’ve been using the code from this post.

I have an article with a timestamp of 2006-01-01 at 02:00 in Textpattern’s article editor, but in the database, it’s Posted field has “2005-12-31 23:00:00”. I suppose it has to do with my time zone setting—I’m in the “New York” time zone with “Automatically adjust DST setting” enabled in my prefs. The result is that the above code picks up the 2005 timestamp from the database and lists it with the 2005 articles, even though it prints a date in January 2006.

I’m not sure what to do about it. I can fix it for this article by adjusting the timestamp to “2006-01-01 at 03:00” (which translates to “2006-01-01 00:00:00” in the database), but I’d prefer not to pass confusing instructions like that on to the site’s editorial team. What would you do?

johnstephens · 2014-04-02 18:50:24

Would it help if I changed my time zone to U.S. Pacific? Or is there a way to modify my code above to make it apply the time zone setting?

I’m puzzled about the discrepancy between the article’s time setting and that in the database. I would have guessed that the database might store dates in Greenwich time, but not in Pacific.

etc · 2014-04-02 20:19:32

johnstephens wrote #280020:

Would it help if I changed my time zone to U.S. Pacific? Or is there a way to modify my code above to make it apply the time zone setting?

John, you can try (untested)

SELECT DISTINCT YEAR(CONVERT_TZ(Posted,'+00:00','+03:00')) AS year ...

An even better option would be extract timezones from txp settings (should be easy), but I don’t know exactly where to search them (someone?)

Bloke · 2014-04-03 00:35:43

johnstephens wrote #280020:

I’m puzzled about the discrepancy between the article’s time setting and that in the database. I would have guessed that the database might store dates in Greenwich time, but not in Pacific.

The main problem is that Txp doesn’t store information about which timezone the datetime relates. It just stores it at whatever time the server happens to be at publication time. If your site moves servers to a different timezone, all your articles will be ‘off’ by a fixed amount compared to how they were before the move. Similarly, it doesn’t record the DST information so if you start looking forwards or backwards at articles that cross a DST boundary, they’re offset. We have fudges in core to try and mitigate the effects of such things (safe_strftime() for example) but they don’t solve the root cause.

smd_calendar hit these issues. I believe WordPress also has the same thing (not checked recent versions: might be fixed now) when it comes to displaying /yyyy/mm/dd URLs, and Txp is no exception: depending on the time of year, the server, the visitor’s browser, the publication datetime of articles, and wind direction, your year-month-day URL scheme may or may not include articles that aren’t quite published in the desired range.

Jukka has a raft of timezone issues assigned to fix. I’ve unionised date generation in PHP to rid us of the blessed MySQL now() problem as part of the custom field improvements. If someone can just figure out how to preserve the existing article timestamps while converting the database dates to a more universal representation to sidestep this problem altogether, that’d be terrific.

johnstephens · 2014-04-03 19:02:37

Thank you both!

@etc— I tried your code suggestion, and it didn’t affect the output. Looking at it more closely, I see that my code used etc_query with the SELECT DISTINCT YEAR(... pattern only when outputting the pagination links list. The article data is generated by a separate article_custom tag with a month attribute set to the currently-viewed archive year.

Based on Stef’s expert testimony, this issue is based on the way Textpattern works, not my implementation of etc_query. Please let me know if I’m wrong!

If that’s the case, I think I’ve reached an impasse in solving this issue with code. It looks like my best option at this point is to change the site’s time zone preference to match the current server location (which is indeed Pacific). If anyone can suggest other options, I’d be very grateful! I’ll contact the hosting provider before changing the time zone pref to find out the likelihood of the site being migrated to a server in a different time zone. (I doubt it, but it seems prudent.)

Thank you again! Stef, your comments on the inner workings are illuminating. It makes sense to me that storing the timestamp in server time “made sense” in 2003, and I certainly understand how complicated your work must be in weighing the tradeoffs between correcting or supporting such quirks.

Last edited by johnstephens (2014-04-03 19:02:58)

etc · 2014-04-03 20:19:57

johnstephens wrote #280047:

Based on Stef’s expert testimony, this issue is based on the way Textpattern works, not my implementation of etc_query. Please let me know if I’m wrong!

I have misunderstood the issue, sorry. A quick fix could be to extract correct articles IDs with

<txp:etc_query name="archive_ids" break=","
	data="SELECT ID FROM textpattern WHERE Section='articles' AND Status=4 AND YEAR(CONVERT_TZ(Posted,'+00:00','+03:00'))='{?archive_year}'" />

and then plug it into <txp:article_custom />:

<txp:article_custom id='<txp:variable name="archive_ids" />' ... />

… unless I’m talking nonsense again :)

Textpattern CMS

Textpattern CMS support forum

#1 2013-10-24 15:26:03