2

How to create an Endeca query on combination of multiple fields [just like where clause in sql query]. Suppose we have three fields indexed are -

  1. empId
  2. empName
  3. empGender

Now, I need a query like "where empName like 's%' AND empGender=male"

Thanks.

Saurabh
  • 2,384
  • 6
  • 37
  • 52

2 Answers2

4

Firstly,

Checkout Record Filters in the Advanced Development Guide.

If you are trying to use a Record Filter on a property, you will need to enable it explicitly in Developer Studio for that property, while your Dimensions will automatically have the ability to apply a Record Filter. This will help when you have explicit values to filter on, for example empGender.

Your Record Filter can then look as follow:

Nr=AND(empGender:male)

You can further use the Ntk parameter to specify fields to search on so assuming your empName field is enabled for wildcard searching (configure this in Developer Studio) searching this field will look as follow:

Ntk=empName&Ntt=s*

So assuming your properties have been configured correctly, your example above will probably end up looking as follow:

Nr=AND(empGender:male)&Ntk=empName&Ntt=s*

To take this one step further, you can specify Search Filters (ie. Ntk + Ntt parameters) together. I haven't tried this for wildcards so you'll need to confirm that yourself but to combine Search Filters you delimit them with |

Ntk=empName|empId&Ntt=s*|1234*

I suggest you manually build up queries in the Reference Application to confirm you get your expected results and then start to code this up in your application.

radimpe
  • 3,197
  • 2
  • 27
  • 46
  • Thanks. Its working. Actually we have a search form with 20 fields and user can search on many combinations (e.g. data range and emp status). For that I am preparing a service class which will generate dynamic query based on user selection. – Saurabh Nov 28 '13 at 08:15
  • Also, I could not find a way to do following query - "where empId in ('1', '2', '3')". Any idea? – Saurabh Nov 28 '13 at 14:04
  • Found a way but its not working with other combination - &Ntk=empId&Ntt=1+2+3&Ntx=mode+matchany (WORKING); &Ntk=empStatus|empId&Ntt=0|1+2+3&Ntx=mode+matchany (NOT WORKING) – Saurabh Nov 28 '13 at 18:29
  • 1
    Technically that is a different question. You need to use a `Record Filter` (Nr) and not a `Search Filter`. So your query should look something like this: `Nr=OR(empId:1,empId:2,empId:3)`. Assuming you've enabled `Record Filters` for the empId property (or it is a dimension) – radimpe Nov 29 '13 at 04:06
0

radimbe, the problem with record filters for this use case is that they need to be precise. This means you don't get pelling correction, thesaurus expansion, case insensitivity or stemming. It's very unlikely that a user will input precise information like this.

Saraubh, you can do a boolean search to do OR text search queries. You can also use the Endeca Query Language to specify a complex set of boolean logic that goes beyond boolean search and which would incorporate spelling correction, stemming, etc.

In general though, I think for an application like this, you should move away from searching specific individual fields simultaneously and make use of the faceting capabilities of dimensions to guide the user. Additionally, a search box that searches many fields in combination simultaneously in order of importance is really the way to go for a simplified user interface for this sort of application.

matgeech
  • 21
  • 1
  • 2
    You are correct, though the solution provided answers the specific use case. When it comes to the data set forth in the original question, the likely hood of using spell correction, stemming or any of the other features provided by Endeca is quite limited. Combined with the fact that none of those features are useable with a wild-card query, the filters are the best way to approach this, if you need to use Endeca. For example, searching on something like a part or an employee number, is generally a 'starts with' type query so you already have to use wildcards. Thanks for the input though. – radimpe Dec 08 '13 at 19:00