0

I am using Stackmob SMQuery and trying to have a Query with 4 OR conditions and sort all the results after all. My code is as below. The problem is 

        SMQuery *query = [[SMQuery alloc] initWithSchema:STACKMOB_SCHEMA_TESTIMONIAL];

        [query where:@"friend_id" isIn:[friendFacebookIds arrayByAddingObject:[KKKeychain getStringForKey:@"facebookId"]]];

        // WHERE (privacy == Public OR (privacy == Private AND friend_id == myFacebookId) OR (privacy == Restricted AND myFacebookId NOT IN restricted_Ids))

        SMQuery *subQuery0 = [[SMQuery alloc] initWithSchema:STACKMOB_SCHEMA_TESTIMONIAL];
        SMQuery *subQuery1 = [[SMQuery alloc] initWithSchema:STACKMOB_SCHEMA_TESTIMONIAL];
        SMQuery *subQuery2 = [[SMQuery alloc] initWithSchema:STACKMOB_SCHEMA_TESTIMONIAL];
        SMQuery *subQuery3 = [[SMQuery alloc] initWithSchema:STACKMOB_SCHEMA_TESTIMONIAL];

        [subQuery0 where:@"privacy" isEqualTo:nil]; // just in case no privacy was set, assume it is public
        [subQuery0 orderByField:@"createddate" ascending:NO];

        [subQuery1 where:@"privacy" isEqualTo:[NSNumber numberWithInt:TTPrivacyLevelPublic]];
        [subQuery1 orderByField:@"createddate" ascending:NO];

        [subQuery2 where:@"privacy" isEqualTo:[NSNumber numberWithInt:TTPrivacyLevelPrivate]];
        [subQuery2 where:@"user_id" isEqualTo:[KKKeychain getStringForKey:@"facebookId"]]; // for private post, I can only see posts that were created by me
        [subQuery2 orderByField:@"createddate" ascending:NO];

        [subQuery3 where:@"privacy" isEqualTo:[NSNumber numberWithInt:TTPrivacyLevelRestricted]];
        [subQuery3 where:@"restricted_user_ids" isNotEqualTo:[KKKeychain getStringForKey:@"facebookId"]]; // make sure I'm not restricted to see
        [subQuery3 orderByField:@"createddate" ascending:NO];

        // combine subquery to main query

        [query and:[[[subQuery1 or:subQuery2] or:subQuery3] or:subQuery0]];

        [query orderByField:@"createddate" ascending:NO];
        ....

So if I commented the last line "[query orderByField:@"createddate" ascending:NO];" then it works fine. But then the results will be a combination of 4 different dataset which is not sorted in createdate order. I also tried to sort in each subquery but the result was also incorrect because they are only sorted inside each subquery, not combining with other results as well.

Thanks.

thkeen
  • 1,857
  • 2
  • 13
  • 16

1 Answers1

0

The database doesn't support executing a query that includes both OR and sorting - this is a database limitation. Instead, perform the query on StackMob, then locally sort the results by createddate using sortedArrayUsingComparator or something similar.

msv
  • 11
  • 2