Is it possible to sort articles defined by date in the custom field?

Myusername · 2020-02-01 02:37:11

I need to create a loop with articles ordered by the date defined in the custom field. Something like:

<txp:article_custom section='mySection' sort="(custom_1+0) desc">
  ...
</txp:article_custom>

However, custom fields do not interpret it as a date. How can I change this? Some other easier way?

colak · 2020-02-01 06:23:04

Hi
I do not know how to do it, but those who might, would ask what is the date format you are using?

jakob · 2020-02-01 07:59:22

If you store your date in the custom field in the format YYYY-MM-DD, you can do something like this:

<txp:article form="event_info" listform="event_list" sort="custom_2 asc" limit="999" />

where custom_2 is the number of the custom field you’re using.

If you also have a custom field with the end date, e.g. custom_3 you can use the plugin msv_if_custom_article_date to filter out events that have already passed by wrapping your listform content in:

<txp:msv_if_custom_article_date is="greaterequal" customfield="3" dateseparator="-">
    <!-- your list form output -->
</txp:msv_if_custom_article_date>

——

And if you want to show an archive of previous events, just switch the sort order to custom_2 desc, e.g.:

<txp:article form="event_info" listform="event_list" sort="custom_2 desc" limit="999" />

and then in your listform use is="less":

<txp:msv_if_custom_article_date is="less" customfield="3" dateseparator="-">
    <!-- your list form output -->
</txp:msv_if_custom_article_date>

jakob · 2020-02-01 08:08:45

Looks like msv_if_custom_article_date is no longer supported by the author. Here’s my still-working version with the YYYY-MM-DD format modification and the tag registered for 4.6+

# Name: msv_if_custom_article_date (modified jcr) v1.1 (compressed)
# Type: Public plugin
# Conditional tag that compares a date set in custom field with a date set in the tag (default is today). Applicable in article form.
# Author: Martin Svihla
# URL: http://www.svihla.net/
# Recommended load order: 5

# .....................................................................
# This is a plugin for Textpattern CMS - http://textpattern.com/
# To install: textpattern > admin > plugins
# Paste the following text into the 'Install plugin' box:
# .....................................................................

H4sIAAAAAAAAA7VY627juBX+bQN5B1YNYhuwZcuZSWZl2YvtzC7aAmmBTv4MZgYBLdE2t7So
klQcY3eAPk0frE/Sc0jKkmI7e+muEVgWee6Xj4ehcRTFP+j4VRzkdMuCGfyM4mCrHx/46iEt
tZHbB6oMTwV7yKhhpL+VGV9xlpHvUzVAhps4oKXZSIUv0XUc3CFDTt4/8o2gdnFSkTyUiuPK
dBoHG2OKeDze7XahtqRhzswYd2/j4JEpzWWObyAxCiMrB0zLmE4VL4zfjK5h7a3MM44rVBBD
18RsqCGp3BZUMU0osYZrZghY5Xwi4IHIyI6bzbN9s2FWRj9jK1oKWNPEyIzuByH5pigET+lS
MKT0YSErqbahjVwcpDKzQZy+mryJA74i/VRQrR/YE9dG93uf7tmTKagxTOWf7un60z/YGnbU
vjcYkB8uup37pyKO18y8SDpaKPubqX7vfKp6g9lF98tF96K7KvMUw0POE/cvQZMekkuWp0Jq
lllzLroEPmshlxDZS7Ph2vPM3M4lz8i8tfExwBeeBZ9nFTtGgWvQpujeqQFfwTdFU+PfD7SX
zjSXnTnps21hgKmxCrxfkyAKSNwiBgmdS0hVzcO1I2X/Kqmw5FxbKnRXM6gNaqRqMLTWLW9v
1EO+1oYVAQXyDmumzWx5bDCDLAjbbGGQHy99CAaVfBuBznhMIPeHyvK1ivuoFYt5TjRdsQcl
d1BNPo+9sBmJIQlMXTnBMODZvAeJ6gWDKmtevncB5X6cfP54WpzLYweTCMUnWN43im/7TRmD
wWLiqtfaiE0zr9rP6mhRQ4358A2fhRbt61SajBRyBwWOeZzPg7ViQKoCcnVFKiULUqlFrxQz
pcoJyNJQzYcytjK/ECY0Oy1ZMK1bYpPfRKw32FcfiO83zCY//nhQN5+Dvt/IjxPakt9HW62p
Jfn/F5xLcyz7D79ANn55giDAJafNcb/AaZHSfkEXOjpNksmQwN9igihmjwmauOdy6F7B7dY7
pNe9gwxdMMDdRyb2YQOE253BH/R3XGnjGwJe37MUzrNGg7w/NAg6Adb9CWrrn/akcicXdGS+
1nAmGQnHWMEUrqIambPcaPQJOlAd9EB7sqdCwFlF+m0NzoIDIQYGOWuTfoq1pnSgfglGeXF7
xJqmGR+nCC4HijuZw2n8nCZq0XxgVB2RTBxGQWBe1GbJmkQvKGySndQ5/Xxwr3K50liHwKms
aZoKG1RRm6qhr0HkFXpH31KRlgLzYauArwBp8pQRuXIDzE7a0oDhJ8+qdsGNvNwumUIymGh0
6NSi/OivpQClgFprqTiFSvo+67cjNWyFd9gO0GBWy5qek9WIgmM/hK75Wom76HrDWzaOSFON
Jfvihy+zL+zwBRPhBJ+v40CqjCm/9tqvrQRd6yYdsG6YKOzc9jqCyTTZRIs/wwqOdi/MTHZO
fITZNBkDQ7fbSTbTxVuc92BATik2O+xMYScpFvcQ/YRtF+fFJWPYtjI5Dq3NoRbGv6FNJeRQ
MbCKOQJDeQ7pdNvAJARJ27MwLCrEgooUl0Jy74fcHQcO9sTS0ldSRZgBqIBJW4APRL72iEwJ
Apu7BLTGabDbH3tkTPA8gkfzHPSr1e/qWUF+hYzgpjw9lYfJuKgC/X4Ppj61A4w+bSAQSL6S
Ag4WAEaikRKwN0WkLFOoKXb1xzfT68nMiQPmBAf3xZUwMwMD+PkcXa3NjHjmvSyVDRgGCfPi
1wmKGf+0nGRslTZc+sYAki8hF9q51UlKAd+dRHBvIdeeC/rLrfj4Vstjv4xxfr7WTMUp+pMb
JxerjFXrh8sShiC6mdlN+/t2NkjGYH7LC1ftlR82z5BjvEfBvQ2b5AN8Rnd3o3fvXFMcX8aO
hTZm1lo22jCdzGrYo+cvgNC3MuXwy9Wfy5YVEM2OPIxe9u4w0x6ZAgZsKF59sBnxnupJAasz
xMGtPSOw1/d4Dth2a5zy5+wZPbMnGWPpuLL6O6KG7bITdfUXd2Kc72h7wRmeRQcLIUtWoUgW
NgPyzqf2WdT1RpbwWLbSbuO0h89oux1l2aE7jtz59oluC1H50u2QZHNdLcbkfl8A9ooqLkW5
FFxvSJkbLmwgfaBqoAQ5106OA5Hq9lV55kWA17WQRrw8OrUc9PX2CtLULm5vFakLvMpbBW3k
F6ARpOf5RaPRBfPgVWBxZqkW5L///k99rfSYhWu4+avwqh11yOjOh8e2VOPm6l1/HmVs87fO
ENfjx+GuRu6fFevoXKwx1OHdXViHGqt66U7Rvj2loA/zAYki8jf5yKy06WRy8/MF/urcubtn
K2dRQFoAMg9CtzIP0Kgwgnkj+h3zamcYiMeaCblutdgjcaMOpBFuYQ0IceRZM9uIoHWNEy0d
1nnVmqQ0x1zL0hQlQKBUiLrL/VF2W2hy0OOhr/4nDmhbupIa2WKq2MBMBA7/X8RtZkfAa5jw
bqdsmS6/ymj01WoVLdltSt/Qmyy6nWTZ6pbeBLMv/wN7wZnTGxUAAA==

Myusername · 2020-02-01 09:10:10

jakob wrote #321464:

If you store your date in the custom field in the format YYYY-MM-DD, you can do something like this:

Thank you for the response jakob, I managed to reach a satisfactory result.

Apparently, the only format that works is YYYY-MM-DD. I tried to think of some way to use the DD-MM-YYY format, convert it to YYYY-MM-DD and then use it to classify the posts, but I can’t think of how to do that. Any idea?

Anyway, not everything needs to be perfect. I will use the YYYY-MM-DD format for now. Again, thank you.

jakob · 2020-02-01 09:36:42

The msv plugin will work with other date formats (it was originally made that way) and if you look at the plugin code, you can see the old setup commented out (described here).

The problem is sorting via article_custom. Textpattern’s regular publish, modified and expired dates are stored in the database as a timestamp so sorting is accurate down to the second. But sorting via DD-MM-YYYY will order all first day of the month and year, then all second day of the month and year, etc. etc. You see the same thing when sorting your files on your computer.

What I do is use glz_custom_fields to make that custom field a date picker, so you get:

With glz_custom_fields you can also add a custom field “instruction hint” to remind your users to use YYYY-MM-DD format.

——

What you can also do is use smd_query like this:

<txp:smd_query query="SELECT *,
     unix_timestamp(Posted) as uPosted,
     unix_timestamp(LastMod) as uLastMod,
     unix_timestamp(Expires) as uExpires
     FROM textpattern
     WHERE Status IN (4)
     AND section = 'events'
     AND custom_3 >= CURDATE()
     ORDER BY custom_2 ASC
     LIMIT 30"
     wraptag="" break="" form="event_list"
     populate="article" />

(replacing the events, custom_3, custom_2, limit ## and form attribute as you need them)

This method is more efficient in that it gets only those articles that end in the future, i.e. the end date (custom_3) is after the current date. The method in the post above using msv_if_article_custom_date gets all the articles, then tests each one by one and knocks out those that don’t match.

You might be able to extend this principle if there is a way of converting the format of the custom fields to a date format using MySQL?

EDIT: Yes, check this stackoverflow thread which suggest you can ORDER BY STR_TO_DATE(custom_2, '%d/%m/%Y') ASC and presumably also AND STR_TO_DATE(custom_3, '%d/%m/%Y') >= CURDATE().

etc · 2020-02-01 11:53:55

Myusername wrote #321467:

I tried to think of some way to use the DD-MM-YYY format, convert it to YYYY-MM-DD and then use it to classify the posts, but I can’t think of how to do that. Any idea?

As jakob suggests, sorting by STR_TO_DATE should work:

<txp:article sort="STR_TO_DATE(custom_1, '%d-%m-%Y')" />

Myusername · 2020-02-01 21:02:06

jakob wrote #321468:

EDIT: Yes, check this stackoverflow thread which suggest you can ORDER BY STR_TO_DATE(custom_2, '%d/%m/%Y') ASC and presumably also AND STR_TO_DATE(custom_3, '%d/%m/%Y') >= CURDATE().

etc wrote #321469:

As jakob suggests, sorting by STR_TO_DATE should work:

<txp:article sort="STR_TO_DATE(custom_1, '%d-%m-%Y')" />...

That way, it worked perfectly. I am grateful for their efforts in responding.

Textpattern CMS

Textpattern CMS support forum

#1 2020-02-01 02:37:11