How To Display Search Results With Multiple Tags Using Codeigniter Active Record?

How To Display Search Results With Multiple Tags Using Codeigniter Active Record? Ajax,Codeigniter,Jquery,upload How To Display Search Results With Multiple Tags Using Codeigniter Active Record?

I was working on a tags system in CI and, what I need was to generate a query that should only display posts with selected tags (i.e. only that have the tags) as an output. After searching many hours in internet, I saw this amazing blog with possible solutions.
http://tagging.pui.ch/post/37027745720/tags-database-schemas
Because of my database struture, i liked "Toxi Solution". The main advantage of this solution is that it is more normalized than the other solutions, and that you can have unlimited number of tags per post.

Since I need to only show posts that have the tags. So I have to do Intersection (AND) query.
Intersection (AND)

 Query for “bookmark+webservice+semweb” SELECT b.* FROM tagmap bt, bookmark b, tag t WHERE bt.tag_id = t.tag_id AND (t.name IN ('bookmark', 'webservice', 'semweb')) AND b.id = bt.bookmark_id GROUP BY b.id HAVING COUNT( b.id )=3 

I have to convert this query using Codeigniter's Active record.
Steps To Display Search Results With Multiple Tags using Codeigniter activerecord are follows
1. Joined tags table to posts table
2. Use where_in active record
3. Use group_by active record
4. Use having account record
5. Use custom count statement inside having statement

Example

 $this->db->join('tagmap', 'bookmark.bookmark_id = tagmap.bookmark_id', 'left'); $this->db->join('tag', 'tag.tag_id = tagmap.tag_id', 'left'); $a_tag = array(); //I am using static array for this example only (It can be dynamic array from Front end (eg. user inputs)) $filter_tag = array("bookmark", "webservice", "semweb"); $a_tag = explode(",", trim($filter_tag)); // Your tags as an array. $a_tag = array_unique($a_tag); // to have unique array (protect duplicate array values) $tag_count = count($a_tag); // count the number of elements in the array $this->db->where_in('tag.name', $a_tag); $this->db->group_by('bookmark.bookmark_id'); $count = "COUNT(bookmark.bookmark_id) = $tag_count"; // Custom string $this->db->having($count); $query = $this->db->get('bookmark'); return ($query->num_rows() > 0) ? $query->result() : FALSE; 

In this way you can retrieve posts that belong to a specific group of tags using Codeigniter Activerecord.

Ref: http://tagging.pui.ch/post/37027745720/tags-database-schemas

Posted by Hodie J