Need Help With Active Record Join Function

Need Help With Active Record Join Function - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Need Help With Active Record Join Function (/showthread.php?tid=43930)

Need Help With Active Record Join Function - El Forum - 07-28-2011

[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

Need Help With Active Record Join Function - El Forum - 07-28-2011

[eluser]jmadsen[/eluser]
read your error message.

you have an underscore in your field name:

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