Similar articles list

23 October 2016

Keywords: related, group

Like most of my tips, this one is inspired by a forum question:

basically I am trying to show a list of texts in the sidebar by the same author … and augment that list with other texts. For the authors I use the id of the entries created in the links tab and I unfortunately use the venue custom field to call their names.

To make the issue more complex there are times that there are two authors who are called by their ids and a comma.

Since there is no multi-author articles on this site, for demonstration purposes we will use custom_1 field (named files) that stores the numeric ids of all files used in the article. We will output a list of (at most) five articles sharing (some of) the files of this one, namely 1,17, and complete this list with five random (unrelated) articles.

We will need to query textpattern table directly, with etc_query plugin. First of all, we construct a SQL query that will match any article that has the value(s) 1 or 17 in its files (i.e. custom_1) custom field:

<txp:etc_query name="find" data='<txp:custom_field name="files" />' markup="list" break=" OR ">
	FIND_IN_SET({?}, custom_1)
</txp:etc_query>

will loop through 1,17 and store in <txp:variable name="find" /> the following query

FIND_IN_SET(1, custom_1) OR FIND_IN_SET(17, custom_1)

Now, we will use this query to retrieve the matching articles ids and display five of them. All the ids will be stored in <txp:variable name="relatedtexts" />, initially set to <txp:article_id />:

<txp:variable name="relatedtexts" value='<txp:article_id />' />
<txp:etc_query data='(<txp:variable name="find" />) AND ID != <txp:article_id />' markup="db" populate="article"  wraptag="ul" label="More texts by the author" labeltag="h4">
	{$<({#row}|6).?(<li><txp:permlink><txp:title /></txp:permlink></li>)}
	<txp:variable name="relatedtexts" value='<txp:variable name="relatedtexts" />,<txp:article_id />' />
</txp:etc_query>

Few explanations: the database query we used is thus

(FIND_IN_SET(1, custom_1) OR FIND_IN_SET(17, custom_1)) AND ID != 49

since we don’t want to output the current article itself. The etc_query instruction

{$<({#row}|6).?(<li><txp:permlink><txp:title /></txp:permlink></li>)}

says “output linked article titles of first five rows”. Finally,

<txp:variable name="relatedtexts" value='<txp:variable name="relatedtexts" />,<txp:article_id />' />

appends each retrieved articles ids to <txp:variable name="relatedtexts" />, resulting in

49,34,38,5,3,23,18,6,7,30,4,51,55,16,47,50,11,41,31,12,9,40,42,36,8,45,14

The rest is straightforward. The final code is

<txp:variable name="relatedtexts" value='<txp:article_id />' />
<txp:etc_query name="find" data='<txp:custom_field name="files" />' markup="list" break=" OR ">FIND_IN_SET({?}, custom_1)</txp:etc_query>
<txp:if_variable name="find">
	<txp:etc_query data='(<txp:variable name="find" />) AND ID != <txp:article_id />' markup="db" populate="article"  wraptag="ul" label="More texts by the author" labeltag="h4">
		<txp:variable name="relatedtexts" value='<txp:variable name="relatedtexts" />,<txp:article_id />' />
		{$<({#row}|6).?(<li><txp:permlink><txp:title /></txp:permlink></li>)}
	</txp:etc_query>
</txp:if_variable>
<txp:article_custom sort="rand()" exclude='<txp:variable name="relatedtexts" />' limit="5" break="li" wraptag="ul" label="Other Texts" labeltag="h4">
	<txp:permlink><txp:title /></txp:permlink>
</txp:article_custom>

and here is the result:

More texts by the author

Other Texts

File(s)