Full Text Search combined with filtering

Hello all,

Today I've started to implement full text search, after a noticeable time it was working. But I ran into a problem. I want to apply filtering on search results. The user is able to full text search but I'd like to filter on the property: "PhysiotherapistId".

I'm working with the following code:

Query query = new Query(typeof(User));
query.SearchQuery = searchString;
query.QueryExpression = Entity.Attribute("PhysiotherapistId") == 4; // 4 will become dynamic in live code
IEnumerable<User> results = Database.Instance.UnitOfWork.Find<User>(query);

Console.WriteLine(Database.Instance.UnitOfWork.Count<User>(query) + " - " + results.Count<User>());

Whenever searching on Users whom are linked to PhysiotherapistId 4 the Console.WriteLine returns expected values (ie 2 - 2, 1 - 1 etc). Although, searching on Users who are linked to another physotherapist I get unexpected values (ie 0 - 1, 0 - 2). In other words, the: Database.Instance.UnitOfWork.Count(query) returns the correct amount of users where as: results.Count() doesn't.

Thanks in advance :-)

Sorry for a bumb, but anyone?

Thanks!

Hi Mark,

Could you please try the following:

using (var unitOfWork = _context.CreateUnitOfWork())
{
  var query = new Query
                      {
                        SearchQuery = searchString,
                        QueryExpression
                          = Entity.Attribute("PhysiotherapistId") == 4
                      };

  var results = unitOfWork.Search(query, typeof(User));

  // results will contain a list of search results in weighted order, each result will have a .Entity propert on it like results[0].Entity
}

The reason for this is that search returns a container for the results since search results could contain many different entity types. You need to pass a search query through the Search() method, not through Find().

I hope that helps and sorry for the delay. Let me know if it works out for you!

John-Daniel

Hello John,

Thanks for the reply! I've tried your solution but unfortunately it doesn't work.

For clarification, I added a picture:

Once more, thanks!

Hello Mark,

We've been trying to reproduce this issue without success.

Could you post the generated SQL for the queries please? This may clarify how a result with a PhysiotherapistId of 5 is getting selected when your query specifies a value of 4. You can capture the generated SQL by setting LightSpeedContext.Logger = new TraceLogger().

If nothing jumps out of that then could you send us a minimal repro project please? We would need:

• a buildable project (console application, or class library with NUnit tests) that exhibits the incorrect behaviour, stripped down to just the entity type or types required to reproduce it
• sample data that result in the problem (e.g. sample users)

Thanks!

Hello Ivan,

I noticed something remarkable, while I was reproducing the behaviour I might have found the problem. It has something todo with other queries being executed.

MySQL-query when no other query is executed:

SELECT
  physiotherapists.Id AS "physiotherapists.Id",
  physiotherapists.Name AS "physiotherapists.Name",
  physiotherapists.physiotherapist_id AS "physiotherapists.physiotherapist_id",
  physiotherapists.Surname AS "physiotherapists.Surname"
FROM
  physiotherapists

--> Time:   0 ms


SELECT
  users.Id AS "users.Id",
  users.Birthday AS "users.Birthday",
  users.client_id AS "users.client_id",
  users.Gender AS "users.Gender",
  users.Initials AS "users.Initials",
  users.Name AS "users.Name",
  users.physiotherapist_id AS "users.physiotherapist_id",
  users.Surname AS "users.Surname"
FROM
  users
WHERE
  (users.physiotherapist_id = 4 AND users.Id = 210)

--> Time:   1 ms

The query in the top is works like a charm. But after executing a: Database.Instance.UnitOfWork.Find(); command the search action does not create any query. My guess is that it will look inside his cache(?). Whenever this doesn't help to find a solution I will post a project that reproduce the behaviour.

Thanks!

EDIT: I've attached a test project plus test database, inside the unit tests i've reproduced the error. Please let me know if the test project doesn't work or anything like that.

I think you've nailed it -- we had a bug where if the full text search returned exactly one hit, and the corresponding entity was already part of this unit of work, we just returned that entity without checking whether it passed the QueryExpression criteria.

This bug will be fixed in the next nightly build, available from about 1200 GMT. I wasn't sure whether your production issue always happened on the 'only one hit from the full text search' scenario, or whether you also saw it when there were multiple FTS matches, so do let us know if the update doesn't solve your production issue.

Thanks ivan! The bug fix solves the problem. Now I can happily use the full text search :-).