Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2009
    Posts
    23

    Unanswered: Related Items like Youtube's Related Videos

    Hi,

    I'm trying to implement a "related articles" module, like Youtube's "Related Videos", into our own intranet. I have those following tables;

    Code:
    CREATE TABLE `articles` (
      `article_id` SMALLINT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
      `category_id` SMALLINT(6) UNSIGNED NOT NULL DEFAULT '0',
      `title` VARCHAR(200),
      PRIMARY KEY (`article_id`)
    )ENGINE=MyISAM
    AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
    
    CREATE TABLE `tags` (
      `tag_id` MEDIUMINT(9) UNSIGNED NOT NULL AUTO_INCREMENT,
      `title` VARCHAR(50) COLLATE utf8_general_ci NOT NULL DEFAULT '',
      PRIMARY KEY (`tag_id`),
      UNIQUE KEY `title` (`title`)
    )ENGINE=MyISAM
    AUTO_INCREMENT=1 CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
    
    CREATE TABLE `tag_relations` (
      `article_id` SMALLINT(6) UNSIGNED NOT NULL DEFAULT '0',
      `tag_id` MEDIUMINT(9) UNSIGNED NOT NULL DEFAULT '0',
      PRIMARY KEY (`article_id`, `tag_id`)
    )ENGINE=InnoDB
    ROW_FORMAT=FIXED CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
    All articles have some tags and I want to make a relation between articles by tags. I want to make the most matching articles (limiting the article count with 5-6 maybe) be displayed.

    Ex; if "Article-1" has 4 tags like "tag-1, tag-2, tag-3, tag-4"; I want to display other articles that have all those tags too. If there are no matching articles, than 3-matching-tags articles would be displayed. If there are no 3-matching-tags, than it would be 2 and at last 1-matching-tag. And I want to display articles that are in the same category.

    I've found some SQL examples but none worked like I want. If anyone can help, I'd be very pleased.

    Thanks.

  2. #2
    Join Date
    Dec 2009
    Posts
    23
    I've found a query from Stackoverflow (original query was about related videos, so I changed table names only) but it doesn't work very well;

    Code:
    SELECT a2.articles_id
    FROM `tag_relations` AS a1
    JOIN `tag_relations` AS a2
    USING (tag_id)
    WHERE a1.article_id = ?
    AND a1.article_id <> a2.article_id
    GROUP BY a2.article_id
    It sometimes shows all articles, sometimes just the related ones, sometimes related and unrelated ones. Something seems wrong with this query but I don't know what.

  3. #3
    Join Date
    Dec 2009
    Posts
    23
    I've found another query and adapted for my tables;

    Code:
    SELECT a.article_id,
           a.title
    FROM articles a
         JOIN 
         (SELECT tr.article_id, Count(*) as MatchCount FROM `tag_relations` tr
          WHERE tr.tag_id in (SELECT tag_id FROM tag_relations tr2 WHERE
           tr2.article_id = 1) GROUP BY tr.article_id) as sub ON a.article_id =
            sub.article_id
    WHERE 
          a.`category_id` = '13' AND
          a.article_id <> '1'
    ORDER BY sub.MatchCount desc
    but this query also seems a little buggy. It only looks for the first matching tag, and not other ones. I mean, if 'article1' has 'tag1, tag2, tag3' and 'article2' has 'tag4, tag1' then both don't match as 'tag1' on article2 comes seconds on the 'tag_relations' table.

    So how can I solve this issue and display related articles? Thanks...

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •