[Quite a challenge for me at least] Database filtering based on a table that acts as a link between two tables.

Hello folks,

I’m currently facing an issue and I hope that somebody can give me some insight on this. I’ll start by explaining what my script is supposed to do and what I’m facing right now.

Basically, the script acts as a search engine for this website I’m building. In the backend the admin can create tags that can act as filter options(if the admin wants it) that will be displayed on to the search page.

Detailed version:

So to start, I made four tables in my database. tags, tag_groups, item_tags, items.

tag_groups acts as a group for the tags (self-explanatory)

tags belongs to tag_groups

item_tags belongs to tags and also belongs to items (this acts as a bridge between items and tags so that an item can support more than one tag. It contains 3 id’s, the primary key of the table itself, the primary key of the item table and the primary key of the tag table)

After I created the table, I then started by working with the filter field in the following step order.

  1. I displayed all of the filter options(tags) containing all the ID’s in their input fields.

  <?php

        foreach($tagGroups as $group)

        {

            echo "<h4>".$group->name."</h4>";

            switch($group->type)

            {

                case "checkbox":

                    $list = CHtml::listData($group->tags, 'tid', 'name');

                    echo $form->checkBoxList($search, "tagsSelected", $list, array("name" =>"Search[tagsSelected][".$group->tgid."][]"));

                break;

                case "radio":

                     $list = CHtml::listData($group->tags, 'tid', 'name');

                    echo $form->radioButtonList($search, "tagsSelected", $list, array("name" =>"Search[tagsSelected][".$group->tgid."][]"));   

                break;

            }

        }

  1. When I submit the form, I manage to put all of the selected filter tags in an array, ready to check if the database has any links in item_tags for these tags. All in here.

$tagsSelected = array_filter($_POST['Search']['tagsSelected']);

  1. The tricky part and this is where I’m stuck, basically, with the list of tags that I have that are selected by the user, I try to filter these tags in item_tags. So if like 3 tags are selected, and only 2 of them are in item_tags for a specific item, then it should not display that item because it does not meet the conditions that the user requested. An example is;

Bob wants to buy a table from the store, but he’s very into tables so he has some conditions for these tables, the conditions are: They must be round, flat, and small. So in that search engine he ticks in these three conditions. In our database there is a table that -almost- meet his conditions; it’s flat and small, but alas it’s not round. Therefore, the page returns “Sorry, the table you were looking for is not in our database.”

TL;DR:

I need to filter items through the database based on tags(filter option), but I can’t seem to figure out how. If 2 out of 3 tags are attached to an item, it’s not supposed to show up that item. Is there a certain way to do this?

At last.

So that’s my problem, I’ve been brainstorming for hours and googling, but can’t find any possible ideas on how to approach this. Is there an easy SQL command or is there a way to approach this via Yii or PHP? I’d love to hear your insight and ideas. Thanks in advance

I’m guessing the problem is very tricky, I’ve been trying a lot of stuff out, but alas no solution :( Hope any of you scripters can come up with ideas!

(Btw, this was a bump xD)

I am thinking that you can search the condition where tag id (i prefer to called it tagFk) in items_tags IN the filter $tagsSelected and GROUP BY item id (itemFk), in select add calculation field COUNT(*) AS matchNum, and HAVING matchNum = length of $tagsSelected.

Hope this can help.