Related articles and tags

Hi Kevin, 

To me it sounds like the biggest issue will be the quantity of data that can be related. If any record can be related to any other record on the site, then however you create system, you're going to have a lot of check boxes/ large drop down lists in one place.

I would go with your first suggestion, and create a section that is used for creating related records, I would personally go with check boxes, as people can struggle with using multi selection lists, especially when they contain a lot of data. 

I would create a field like this: http://www.interactivetools.com/kb/article.php?Populate-a-list-field-from-another-section-15 for each section. If you use the num value for each section as the value for your checkbox field, and use the title field for the label you might be able to get away with making only one more get Records call to retrieve all of your related records. Maybe something like this:

<?php header('Content-type: text/html; charset=utf-8'); ?>
<?php
  /* STEP 1: LOAD RECORDS - Copy this PHP code block near the TOP of your page */
  
  // load viewer library
  $libraryPath = 'cmsAdmin/lib/viewer_functions.php';
  $dirsToCheck = array('C:/wamp/www/test/','','../','../../','../../../');
  foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
  if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }

  // load record from 'blog'
  list($blogs, $blogMetaData) = getRecords(array(
    'tableName'   => 'blog',
    'where'       => whereRecordNumberInUrl(0),
    'loadUploads' => true,
    'allowSearch' => false,
    'limit'       => '1',
  ));
  $blog = @$blogs[0]; // get first record
  if (!$blog) { dieWith404("Record not found!"); } // show error message if no record found

  // load records related to the blog
  list($relatedArticles, $blogMetaData) = getRecords(array(
    'tableName'   => 'related_blogs',
    'where'       => "`blog_related` LIKE '%\t{$blog['num']}\t'%",
    'loadUploads' => false,
    'allowSearch' => false,
  ));


This is just example code, so you'll have to modify it to work with your pages.

So this page is displaying a record from the blog section. The first getRecords function gets the blog the record that is going to be displayed on the page. Then the second getRecords function calls the related_records section, which contains all of the related fields for the different sections.For example a field called related_blogs for the blog section, related_news for a new section, related_contact for the contact page. So this getRecords function would grab records that have the current num value for the blog record checked in the related_blogs field.

I hope that all makes sense!

Thanks

Greg

Hi Greg,

Thanks so much for taking the time to respond.  I agree that a checkbox solution would be better....it's just dealing with the amount of checkboxes.

First, to clear something up.....I'm looking to have just one set of tags that are used throughout the system (spanning any section).  This is an HR benefits related site.  They have multiple multi record sections in the CMS, each dealing with HR related content, but each with it's own fields.  One is a Q&A section, another lists articles with titles and content.  So when I say they want to show related articles, they really want to span ALL the sections.....not just keep it within the section of the current article.  I think this poses a challenge as to me it would have to be a multi part lookup.  Once you have the current list of tags for the article being shown.....search each section, getting the articles that match at least one tag.....storing them, and then randomly displaying 5 of those articles in the sidebar.  Almost like adding each section onto a big array.....randomizing the array, and displaying the first five records.

I know it's way above and beyond to do anything where there would be multi keyword matching......basically a relevance system.  I've already told the client basically I will would do for this phase is take all the list of keywords for the current article....and match any articles that have ANY of the keywords.  

Really all they want is when someone is viewing an article....to show 5 other articles that are somewhat relevant.  I'm guessing that tagging is the best way to do this.....but if you have another suggestion I'm open to it.

I've been looking at this thread....http://www.interactivetools.com/forum/forum-posts.php?Related-Records-71580

But I'm not getting anywhere.

Curious to hear what you think after my explanation above and whether your thoughts have changed.

-Kevin

Sorry about my rambling....but let me say this:

Let's assume they are going to have a manageable list of tags (under 20).  And I have those as checkboxes, pulling from another section in the CMS.  So for each record they can select up to 20 tags.  Ok, I have this working fine.  I'm also able to construct the tags where clause using the following code:

$where = "";
$articleCategories = array();


$articleCategories = explode("\t", trim($detailRecord['tags'], "\t"));

foreach ($articleCategories as $category) {
$where .= " tags LIKE '%\t$category\t%' OR";
}

$where = rtrim($where, "OR"); //remove the last "OR"

Ok this works.  Now I guess my question would be.....what is the best way to use that where clause.....to query across multiple tables.....getting a master list of related articles.  Then randomizing the list, and picking 5.  Then listing the title and snippet from each with a link to the actual article.

Phew.....out of breath after that one.

-Kevin

So I'm going with a multi-select list for the keywords.

Then what I'm going to do is run it through a function to grab the related articles.  This is the code I'm using for 2 of my sections.....I'll add more later:

function getRelatedArticles($detailRecord) {

$where = "";
$articleKeywords = array();

$articleKeywords = explode("\t", trim($detailRecord['keywords'], "\t"));

foreach ($articleKeywords as $keywords) {
$where .= " keywords LIKE '%\t$keywords\t%' OR";
}

$where = rtrim($where, "OR"); //remove the last "OR"
$where = "(" . $where . ")";
$where .= " AND num != '" . intval($detailRecord['num']) . "'";


list($relatedArticle, $relatedArticlesMetaData) = getRecords(array( 
'tableName' => 'ask_a_consultant', 
'allowSearch' => false, 
'where' => $where,
'debugSql' => false,
));

list($relatedArticle2, $relatedArticlesMetaData2) = getRecords(array( 
'tableName' => 'legislative_update', 
'allowSearch' => false, 
'where' => $where,
'debugSql' => false,
));

$relatedArticles = array_merge($relatedArticle, $relatedArticle2);

// Shuffle the array to randomize records
shuffle($relatedArticles);

return $relatedArticles;

}


So you can see I basically grab the list of related articles and put them each in an array.  Then I merge the arrays.  Then to randomize the output....I shuffle the arrays.

After that, I can return the array to my page where this code put the top 4 in a list:

$max_loop=4;
$count = 0;
foreach ($relatedArticles as $relatedArticle) {
if ($relatedArticle['_tableName'] == 'ask_a_consultant') {
echo "<li><a href=\"" . $relatedArticle['_link'] . "\"><b>" . $relatedArticle['question'] . "</b><br />" . textTrim($relatedArticle['answer'], 100, 'notags', '', '') . "</a></li>";

} else {

echo "<li><a href=\"" . $relatedArticle['_link'] . "\"><b>" . $relatedArticle['title'] . "</b><br />" . textTrim($relatedArticle['summary'], 135, 'notags', '', '') . "</a></li>";
}

$count++;
if($count==$max_loop) break;
}


Because each section has a different field name for it's title and article text, you can see I do a check to see which section I'm in and format it accordingly.

I'll be expanding this to include other sections....right now I'm testing with 2.

So far so good....seems to be working.  No doubt it's not optimized.....but I'll look into that later.

Also, I do a check AFTER the function call to get the related articles:

if (empty($relatedArticles)) { $recentArticles = getRecentArticles($detailRecord); }

Basically if there were no related articles returned, go back and run a similar function to get the most RECENT articles:

function getRecentArticles($detailRecord) {

list($recentArticles, $relatedArticlesMetaData) = getRecords(array( 
'tableName' => 'ask_a_consultant', 
'allowSearch' => false, 
'limit' => 5,
'where' => "num != '" . intval($detailRecord['num']) . "'",
'orderBy' => 'createdDate DESC', 
'debugSql' => false,
));

list($recentArticles2, $relatedArticlesMetaData2) = getRecords(array( 
'tableName' => 'legislative_update', 
'allowSearch' => false, 
'limit' => 5, 
'orderBy' => 'createdDate DESC', 
'debugSql' => false,
));

$recentArticles = array_merge($recentArticles, $recentArticles2);
usort($recentArticles, 'date_sort');

return $recentArticles;

}

This again is the best I can come up with to go across multiple sections.  I then take that merged array and run it through a sort function to get them in the correct order:

function date_sort($a, $b) {
    $t1 = strtotime($a['date']);
    $t2 = strtotime($b['date']);
    return $t2 - $t1;
} 


I then using a similar foreach loop to show the latest 4 most recent articles.

-Kevin

Hi Kevin,

We've just been discussing this problem in our office when you replied. The method your using is probably the simplest, and what we were going to suggest. 

If you wanted something that would be quicker to load on the page you could create a CRON plugin that preloads all of the related records into a separate section  every time a record is saved or deleted in CMS, then you'd just have to get the related keyword links from that table. If you send an e-mail to consulting@interactivetools.com we can give you an estimate of the cost of creating the plugin if you require. 

Let me know if you have any questions.

Thanks!

Greg

Hi Greg,

Wow.....good to know I did something right!

I'll run that by the client and see if they want us to submit that request.  Right now things are working pretty quickly.

Looking at my code......anything pop out that you would do differently to optimize things?

-Kevin

Hi Kevin,

In general the code looks good. You could look into setting these variables as false in your getRecords functions:

list($relatedArticle, $relatedArticlesMetaData) = getRecords(array( 
  'tableName'        => 'ask_a_consultant', 
  'allowSearch'      => false, 
  'where'            => $where,
  'debugSql'         => false,
  'loadUploads'      => false,         // optional, defaults to yes, loads upload array into upload field
  'loadCreatedBy'    => false,         // optional, defaults to yes, adds createdBy. fields for created user
  'loadListDetails'  => false,         // optional, defaults to yes, adds $details with prev/next page, etc info
  'loadPseudoFields' => false,      // optional, defaults to yes, adds additional fields for :text, :label, :values, etc
)); 


These values are set to true by default, by setting them to false your getRecords MySQL call will be smaller, and less demanding on the server. Just enable any of the options that you use the data from. 

Cheers

Greg