Hi Bjarne,

Could you not use the Umbraco Examine Fluent API on the default Internal Member index? I think that contains an index of all members and their properties. It might be quicker than querying the database through Umbraco services.

Thanks, Dan.

Hi Dan

I haven't worked with Examine before.. do you have an example of how I can use it? The basic idea on how you would do it..

/Bjarne

Hi Bjarne,

Something like the following could work if your Internal Member Index is not empty and contains gender values:

var searcher = ExamineManager.Instance.SearchProviderCollection["InternalMemberSearcher"];
var searchCriteria = searcher.CreateSearchCriteria(IndexTypes.Member).Field("gender", "male").Compile();
var results = searcher.Search(searchCriteria);

Thanks, Dan.

I have tried your example above and rebuild thee index, but the results.TotalItemCount seems to returns 0. Is there something else I need to configurate?

/Bjarne

Btw . I am using Umbraco 6.1.6

In ExamineIndex.config I have added the additional property for "gender":

<IndexSet SetName="InternalMemberIndexSet" IndexPath="~/App_Data/TEMP/ExamineIndexes/InternalMember/">
    <IndexAttributeFields>
      <add Name="id" />
      <add Name="nodeName"/>
      <add Name="updateDate" />
      <add Name="writerName" />
      <add Name="loginName" />
      <add Name="email" />
      <add Name="nodeTypeAlias" />
    </IndexAttributeFields>
    <IndexUserFields>
      <add Name="gender" />
    </IndexUserFields>
  </IndexSet>

/Bjarne

Okay, I found that the index wasn't build from the code.. it has been changed so the index is build and the code above return the number of items found.

How can I check if a property has a value..and e.g. for a numeric property that the value is greater than 0?

Hi Bjarne,

I haven't tested it but I think you would have to use a raw query instead of using the Fluent API. Something like the following might work:

var searcher = ExamineManager.Instance.SearchProviderCollection["InternalMemberSearcher"];
var searchCriteria = searcher.CreateSearchCriteria(IndexTypes.Member).RawQuery("gender:male AND property>10");
var results = searcher.Search(searchCriteria);

If that doesn't work, you could always convert each of your results to a Member and analyse the property value:

var umbracoHelper = new UmbracoHelper(UmbracoContext.Current);

foreach (var result in results)
{
    var member = umbracoHelper.TypedMedia(result.Fields["id"]);

    if (member != null && member.HasValue("propertyAlias") && member.GetPropertyValue<int>("propertyAlias") > 10)
        // Member has property value greater than 10
}

Thanks, Dan.

Hi Dan

I tried with this query for another field:

var queryPaymentSubscriptionId = searcher.CreateSearchCriteria(IndexTypes.Member).RawQuery("paymentSubscriptionId > 0");
var resultsPaymentSubscriptionId = searcher.Search(queryPaymentSubscriptionId); 

However it returns a number 22188 and seems to be wrong a there only exists 6 records: two with 0, one has NULL a three has a value greather than 0.

In your other suggestion how is "results" defined? what would it contains? if the same as above, how would the searchCriteria then looks like?

Thanks,
Bjarne

When I use this it seems to return the right number, when I test with the database table:

var queryMale = searcher.CreateSearchCriteria(IndexTypes.Member).Field("gender", "Male").Compile();

However when I do the same for the value Female .. and another field emailSubscription, where I test for value 1 (true) the TotalItemCount seems to be 1 higher than when I query the database table.

var queryFemale = searcher.CreateSearchCriteria(IndexTypes.Member).Field("gender", "Female").Compile();
var queryEmailSubscription = searcher.CreateSearchCriteria(IndexTypes.Member).Field("emailSubscription", "1").Compile();

If I use SQL like this it returns 7642 rows:

SELECT * FROM cmsPropertyData
WHERE propertytypeid = 136
AND dataNvarchar = 'Male'

And the same result I get via the TotalItemCount in Examine. However for the value Female in the property "gender" and for check if the property "emailSubscription" has a value 1 (true) seems to return 1 more in value from TotalItemCount in Examine than number og rows via SQL query.

This returns 84247 rows:

SELECT * FROM cmsPropertyData
WHERE propertytypeid = 136
AND dataNvarchar = 'Female'

But TotalItemCount here returns 84248.

var queryFemale = searcher.CreateSearchCriteria(IndexTypes.Member).Field("gender", "Female").Compile();
var resultsFemale = searcher.Search(queryFemale); 

And this returns 72988.

SELECT * FROM cmsPropertyData
WHERE propertytypeid = 162
AND dataInt = 1

But TotalItemCount here returns 72989.

var queryEmailSubscription = searcher.CreateSearchCriteria(IndexTypes.Member).Field("emailSubscription", "1").Compile();
var resultsEmailSubscription = searcher.Search(queryEmailSubscription); 

I think it's a bit strange. I have rebuided the index, but without any changes.

/Bjarne

Okay, when open the index in Luke I see the values returned from TotalItemCount in Examine.

However for some reason there is 7642 rows with the value "male" in the database, but only 84247 rows for "female".

is working on a reply...