• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Need Help With Active Record Join Function

#1
[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

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

you have an underscore in your field name:

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


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.