Jump to content

Selector to imitate MySQL "IN()" statement


choppingblock
 Share

Recommended Posts

Hey all,

i wonder if there is a way to imitate an IN() statement like 

"SELECT id WHERE field IN(1023,1208,2357)" 

I have a website where i have products assigned to categories, and each product can be assigned to multiple categories. The categories are (of course) pages, so the "categories" field of a product might look like "1034|1209|1508".

The pages to display the products are also assigned a category (the products are not displayed on the category pages themselves), but this can only be one at a time.

What I am trying to do is to find all products of a category and display them on a page... but I can't figure out how to do that.

I tried

$pages->find('category='.$page->category); 

but that didn't work, i think due to the fact that each page can have multiple categories.

I would need something like

$pages->find($page->category.' in category');
or
$pages->find('category has '.$page->category); 

Is there already a way to accomplish this? Or should I use a completely different approach?

Any help is appreciated.

Thanks!

Link to comment
Share on other sites

You could do category.name=cat1name|cat2name|cat3name

Basically the pipe character --> | <-- means OR, which should work for your case. The above is for the page name field, but you could just as easily do category=1027|1038|1057 if you want to match against the page ID

  • Like 1
Link to comment
Share on other sites

unfortunately this only works the other way round than what i need...

the field (left side of the operator) has multiple values, while the value (right side of the operator) has only one.

what i need to accomplish is finding out if the value on the right side matches one of the field values. if i could just flip the operands...

i can't think of an easy way to do this, but maybe someone smarter than me can... :)

  • Like 1
Link to comment
Share on other sites

figured it out. the LIKE operator is my friend with this... :)

unfortunately there seems to be a bug or missing feature because it get a

Exception: Operator '*=' is not implemented in FieldtypePage

I found a workaround to this here: http://processwire.com/talk/topic/3751-operator-in-fieldtypecomments/

Though it would be nice to use the "%=" operator instead. Any ideas why this doesn't work? I'm using PW 2.3.0 with the multi language modules.

Link to comment
Share on other sites

I am little confused what you are trying to achieve and why it doesn't work. I think part of the confusion is that I don't understand how you have setup your products and categories and how they relate each others.

In most common "multicategory" setup product has field called "category" that allows one or more categories. Then the selector is simple as this:

$pages->find("category=$categories"); 
  • Like 1
Link to comment
Share on other sites

I'm seeing certain similarities with another thread, http://processwire.com/talk/topic/3530-most-selector-operators-throwing-errors/. Even the exception matches that thread perfectly.

There's no "*=" or "%=" with page type field (just like that exception says.) It's not a bug or missing feature either, it's just that there's no need for such an operator. With page type fields "=" simply checks if page field value contains a value, not if it's equal to it  :)

What Antti pointed out above seems to be exactly what you're after; per your description you've got products with a field "categories" and pages with one category selected and you want to find any products that include that category in their "categories" fields. In this case this should do the trick:

$pages->find("categories={$page->category}");

I'd even go as far as say that if it doesn't seem to work, the issue is probably something entirely different.

Just to make sure, in your original post you mentioned field "categories" for products and your selector was "category=...", is it possible that something similar (ie. misspelled field name) is the actual issue here? :)

  • Like 3
Link to comment
Share on other sites

thanks teppo, for pointing me in the right direction. seems i simply used a wrong syntax / quotation style...

i used

$pages->find('categories='.$page->category);

when i should have used

$pages->find("categories={$page->category}");

i was sure there was a super simple solution to the problem... and there it is.

thanks again! :)

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...