Thread

Hi

I am trying to allow users to filter results by category and category groups. By this I mean I have 3 category groups (material, colour and clasp) and within each category-group there are multiple categories e.g.

Material:
Cotton, Silk, Wool, Linen ...

Colour:
Red, Blue, Green ...

Clasp:
Round, square, oval ...

I would like to be able to use an OR search within the categories, combined with an AND between the category-groups. I looked at Solspace’s Super Search and this only handles one or the other (you can’t do an AND or OR in the same search). So I am now looking at using the Query module and it’s really got my head scratching as my SQL knowledge is OK, but certainly not a master.

If it is possible with the Query module it looks like I might have to nest some queries, which again seems to pose problems based on what I have read so far.

If anyone has any experience in solving a similar problem, or any alternative suggestions about a better way to do this (Matrix?), I would be extremely grateful to hear your thoughts.

Many thanks
Jonathan

The only way I can currently see this is via this very nasty 3 deep SQL query (one for each category group)

SELECT DISTINCT(t.entry_id) FROM exp_channel_titles AS t 
LEFT JOIN exp_channels ON t.channel_id = exp_channels.channel_id 
INNER JOIN exp_category_posts ON t.entry_id = exp_category_posts.entry_id
INNER JOIN exp_categories ON exp_category_posts.cat_id = exp_categories.cat_id 
WHERE t.entry_id !='' 
AND t.site_id IN ('1') 
AND t.channel_id = '2' 
AND (exp_categories.cat_id = '3' OR exp_categories.cat_id = '1')
AND t.status = 'open' 
AND t.status != 'closed'
AND t.entry_id IN (

    SELECT DISTINCT(t.entry_id) FROM exp_channel_titles AS t 
    LEFT JOIN exp_channels ON t.channel_id = exp_channels.channel_id 
    INNER JOIN exp_category_posts ON t.entry_id = exp_category_posts.entry_id
    INNER JOIN exp_categories ON exp_category_posts.cat_id = exp_categories.cat_id 
    WHERE t.entry_id !='' 
    AND t.site_id IN ('1') 
    AND t.channel_id = '2' 
    AND (exp_categories.cat_id = '4')
    AND t.status = 'open' 
    AND t.status != 'closed'
    AND t.entry_id IN (

        SELECT DISTINCT(t.entry_id) FROM exp_channel_titles AS t 
        LEFT JOIN exp_channels ON t.channel_id = exp_channels.channel_id 
        INNER JOIN exp_category_posts ON t.entry_id = exp_category_posts.entry_id
        INNER JOIN exp_categories ON exp_category_posts.cat_id = exp_categories.cat_id 
        WHERE t.entry_id !='' 
        AND t.site_id IN ('1') 
        AND t.channel_id = '2' 
        AND (exp_categories.cat_id = '8')
        AND t.status = 'open' 
        AND t.status != 'closed'

    )
)
ORDER BY t.entry_date DESC, t.entry_id DESC LIMIT 0, 100;

Any other simpler / slicker approaches would be much appreciated!

Hi Jonathan,

Thanks for positing your question to the Forums!

We can’t directly support SQL, even as part of the Query Module. However, I think we may find a simpler approach.

One idea would be to set the categories for a channel entries tag dynamically. You can use the dynamic parameters feature and the channel categories tag to build dropdown selections.

<form method="post" action="{path='template_group/template_name'}">
<select name="selcat">
        <option value="">--Select Category--</option>
        {exp:channel:categories channel="yourchannel" style="linear" category_group="2"}
            <option value="category[{category_id}|]">{category_name}</option>
        {/exp:channel:categories}
    </select>
 <input type="submit" value="Go!" />
</form>

You would need one for each group you wanted to offer selection for.

Then, passing those values along to a channel entries tag:

{exp:channel:entries  dynamic_parameters="category"}

This is an example, it may need some refinement.

Cheers,

Thanks for your reply Dan and fully appreciate your poitn about SQL support.

I’m a little unclear about the solution you suggest. Would I be able to use dynamic_parameters to handle a combination of OR and AND categories?

The main thing I need to be able to achieve is to allow a user to search for something like the following:

RED or BLUE bags that are made of SILK (3 different categories from 2 different group, 2 joined with an OR (colours) and then joined with an AND to the other category (material)).

+1 on this BUMP :D

I made a similar thread http://ellislab.com/forums/viewthread/184851/#1013006 years ago, it didn’t really get much attention.

There maybe a way of using http://gotolow.com/addons/low-seg2cat/docs but this would require using ultiple URI and I am not sure how to do that yet. http://ellislab.com/forums/viewthread/159741/ seems to cover any category across any blog but not multiple categories.

ALternatively there is http://devot-ee.com/add-ons/zoo-triggers that seems to have potential but it will show categories via URL and not dymacally via checkboxes, which I would prefer.

So to make category check box list, like LINEDUP said doesn’t seem feasible at the moment.

Hi vlad_i

I think Solspace’s Super Search module might do what you are looking for, but unfortunately it does not solve my problem. I have spoken with their support team and they do not currently support searches with a combination of AND’s and OR’s.

I’m surprised more people have not tried to solve this scenario before, but I cannot find much evidence of it in the forums unfortunately.

I ‘think’ the rather nasty SQL that I have butchered together above should do the trick and fortunately this website will never have more than about 150 products to filter through. However it does not bode well for any larger sites.

I would really welcome any comments from the EE dev team, if they have ever come up with a solution for this.

Thanks

Jonathan

Linedup,

Yes and no. I am looking for more a check box solution. Check box one, two and three and only evebts tagged with those catehories show up. Advanced form as far as i know doesn’t do this. What you are trying to do can be done via json. We had a similar issue and js + json solved it. But far from simple out of the box solution :(

You mean only entries with all those options selected should show up (AND)? Unless I am missing something (and I probably am) then that can easily be done via Super Search categories:

http://www.solspace.com/docs/detail/super_search_results/#category

category=“Teeth&&Gums;” query would fetch entries that belonged both to the Teeth category and the Gums category inclusively.

If you have a solution to my question, I would be extremely interested to find out more please!

Thanks for that ill check it out! We had a similar issue of sorting players by rank that had many various score results. So there were pick this result but not this result. Or pick that result but not that result.. What we did is dump all the channel data into a JSON table then have js sort it all out. It would go through all the categories and pick only the relevant ones.

I hired a guy to do the js, so he told me how to have Ee spit out the JSON table. The rest was js and Ajax.

I would love to give you his info but he fell off the face of the earth! :(

Hi Linedup,

Nope, what I outlined wouldn’t fit the need for OR and AND. Our search module is just not that robust.

The only other Search module is Low Search, since you’ve already investigated Super Search. Although, I’m not entirely clear on its capabilities, Low would be happy to answer the more technical questions.

The Really Useful Search™ scene for EE seems to be a prime market.

Cheers,

Linedup,

I have put in a request to a new JSON dev to scope out the kinda search you are looking for. If they say they can do it I will let you know.

Vlad

Hi all,

I’m going to migrate this into Community Help to keep the conversation going.

If you need anything else technical support wise, please open a new thread.

Cheers,

Hi! Since Dan let this open for the community keep talking, let’s do it!

Linedup, vlad_1, how do you choose to deal with this in the end? Any other idea?

Linedup, since the IDs of the categories are unique across all category groups, do you really need this giant query?

Thanks, guys! Sorry for dig up this dead body.

Actually I am working on it now. 😊 I am going to use LOW search because I do not need the the AND & OR statement. As for Linedup I recommended him to talk to a JSON developer I know have no idea where that went.

I’m thinking about to improve Dynamo for my needs, since I need the AND and the OR statements, like Linedup does.

Does Dynamo can’t help you?