[eluser]ShoeLace1291[/eluser]
I'm trying to duplicate a query that I was given that will use a table of articles to select rows with similiar tags compared to the current article.
I have a table called "tutorial_articles" that stores tutorials. Next, I have a relational table called "tutorial_tags" that store single keywords in each row. The tutorial_tags table has a column called tutorial_id that matches the keyword with the right article.
This is the query I was given as a sample and I'm trying to duplicate it using the active record class functions. The code instead uses a photo table as the main table.
Code:
select t2.photo_id,
count(t2.photo_id) as matches
from photo_tags t1
join photo_tags t2 on (
t1.tag = t2.tag
and t1.photo_id != t2.photo_id
)
join photos p on (t2.photo_id = p.photo_id)
where t1.photo_id = << ID of photo >>
group by t2.photo_id
order by matches desc;
This is my code:
Code:
$CI =& get_instance();
$CI->db->select("tag2.tutorial_id, count(tag2.tutorial_id) AS matches");
$CI->db->from("tutorial_tags AS tag1");
$CI->db->join("tutorial_tags AS tag2", "tag1.keyword = tag2.keyword AND tag1.tutorial_id != tag2._tutorial_id");
$CI->db->join("tutorial_articles AS art", "tag2.tutorial_id = art.id");
$CI->db->group_by("tag2.tutorial_id");
$CI->db->order_by("matches", "desc");
$CI->db->limit(6);
return $CI->db->get();
This is the syntax error I'm getting:
Quote:Unknown column 'tag2._tutorial_id' in 'on clause'
SELECT `tag2`.`tutorial_id`, count(tag2.tutorial_id) AS matches FROM (`tutorial_tags` AS tag1) JOIN `tutorial_tags` AS tag2 ON `tag1`.`keyword` = `tag2`.`keyword` AND tag1.tutorial_id != tag2._tutorial_id JOIN `tutorial_articles` AS art ON `tag2`.`tutorial_id` = `art`.`id` GROUP BY `tag2`.`tutorial_id` ORDER BY `matches` desc LIMIT 6
