Welcome Guest, Not a member yet? Register   Sign In
Need Help With Active Record Join Function

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.

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:
$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->order_by("matches", "desc");
        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

read your error message.

you have an underscore in your field name:

Unknown column ‘tag2._tutorial_id’ in ‘on clause’

Theme © iAndrew 2016 - Forum software by © MyBB