Query with paging by cursor causes error because of limitations for "IN filter" in cursor() method... What should be the alternative?

Question

I am developing a twitter like microblogging system by using the following models:

class Member(db.Model):    
    user = db.UserProperty(required=True)
    follower_count = db.IntegerProperty(default=0) # members following you    
    following_count = db.IntegerProperty(default=0) # members you are following

class NewsItem(db.Model):    
    text = db.StringProperty(required=True)
    posted_by = db.ReferenceProperty(reference_class=Member,required=True,collection_name="posted_items")
    posted_on = db.DateTimeProperty(auto_now_add=True)
    status = db.IntegerProperty(default=1) # 0: deleted

class Follow(db.Model):    
    member = db.ReferenceProperty(reference_class=Member,required=True,collection_name="followings")    
    followed_member = db.ReferenceProperty(reference_class=Member,required=True,collection_name="followers")    
    added_on = db.DateTimeProperty(auto_now_add=True) 

In this model structure I retrieve the messages of the members that the current user follows with the following code below:

follow_log_list = Follow.gql('WHERE member = :1 ', member)
followed_member_list = []
for follow_log in follow_log_list:
    followed_member_list.append(follow_log.followed_member)

query = NewsItem.all()
query.filter('posted_by IN', followed_member_list)
query.filter('status =', 1)
query.order('-posted_on')
query.with_cursor(cursor)   
newsList = query.fetch(10)  

template_values['cursor'] = query.cursor()

When query.cursor() method is called I get the following error:

"No cursor available for a MultiQuery (queries using "IN" or "!=" operators)"

This is normal since in the documentation of cursors this limitation is stated clearly as:

http://code.google.com/appengine/docs/python/datastore/queries.html

"You cannot use cursors with queries that use the IN or != filter operators."

What is the alternative way of getting the posts of followed members?

Thank you,

EDIT: The posted messages were filtered by their status and ordered by their posted date... But the sample did not show it in here, I've changed it...

Answer 1

I've been able to solve this problem using the search API.

https://developers.google.com/appengine/docs/python/search/

You need to mirror the relevant bits of your objects in a search document, and save it to an index in _pre_put_hook or _post_put_hook (and you can clean these out in your _pre_delete_hook or _post_delete_hook). Use a serialization of your key as the search doc_id.

Once you've got this going, you can do the kind of searching using Search, that you're trying to do above. It's fast! Just return doc_ids, then use them to get() your datastore objects.

Answer 2

My solution is using the date value like a cursor as I had described as a comment to Nick Johnson's answer... It is like this:

if cursor: # This is not actually a cursor! It is base64 datetime string
  cursordate = _strptime(base64.b64decode(cursor)) # _strptime is a local method that converts str to datetime

# IN has a limit for lists: 30 items allowed
listofNewsLists = []
listofMemberLists = [followed_member_list[i:i+30] for i in range(0, len(followed_member_list), 30)]
for eachList in listofMemberLists:
   query = NewsItem.all()
   query.filter('posted_by IN', eachList).filter('status =', 1)
   if cursor:
      query.filter('posted_on <', cursordate)
   query.order('-posted_on')                        
   listofNewsLists.append(query.fetch(PAGE_SIZE))

  newsList = []
  if listofNewsLists:
    emptyListCount = 0
    while len(newsList) < PAGE_SIZE and emptyListCount < len(listofNewsLists):
      max = datetime.datetime.min
      maxInd = -1
      emptyListCount = 0
      for i in range(len(listofNewsLists)):
        if listofNewsLists[i] == []:
          emptyListCount += 1
        elif listofNewsLists[i][0].posted_on > max:
          max = listofNewsLists[i][0].posted_on
          maxInd = i
      if max > datetime.datetime.min:
        newsList.append(listofNewsLists[maxInd].pop(0))

template_values['cursor'] = base64.b64encode(newsList[-1].posted_on.isoformat())

That is; I store the last displayed item's date value as the starting point of the new list...

This works well (I guess) unless I have items with same posted_on value...

Answer 3

The reason for this restriction is that IN and != queries are executed by splitting the query into multiple underlying queries, which are executed individually by the datastore, then merged together in sorted order.

If you want to do a query like this in a paginated fashion, you would have to execute the queries yourself, and do the merge yourself. To fetch a cursor, you'd need to fetch cursors from the individual sub-queries and concatenate them together. Further, you'd need to keep track of how many fetched but un-consumed results you had, so you can pick up exactly from where you left off.

As you can see, this is complicated and leads to excessively long cursor values, which is why it isn't implemented by the SDK currently. Unfortunately, it's the only practical way to do it, unless you can find a way to avoid the use of an IN clause, or drop your requirement for ordering by another one (in which case you could just execute the queries serially, paginating each).

Answer 4

A quick and nasty way...

Download this pagintor.py import it into your project.

Then you can do something like this for pagination

    from paginator import Paginator, InvalidPage, EmptyPage
     model = Member.all().fetch(100)
     paginator = Paginator(model,5)

                if(self.request.GET):
                    page = int(self.request.GET.get('page', '1'))
                    if(page is not None):
                        try:
                            page = int(self.request.GET.get('page', '1'))
                        except ValueError:
                            page = 1

                        # If page request (9999) is out of range, deliver last page of results.
                        try:
                            paginator = paginator.page(page)
                        except (EmptyPage, InvalidPage):
                            paginator = paginator.page(paginator.num_pages)
    return self.response.out.write( template.render(path+'.html',{'paginator':paginator}))


#In templates

{% if paginator.object_list %}

{% for values in paginator.object_list %}

#do your tasks

{% endfor %}
<div  align="right" class="pagination" >
        {% if paginator.has_previous %}
            <a  id="previous" href="{{ paginator.previous_page_number }}">Previous</a>
        {% else %}
         <span class="page-nulled" >
            Previous
        </span>
        {% endif %}

        <span class="current" id="pagenum" title="{{ paginator.number }}">
            &nbsp;&nbsp;&nbsp;Page {{ paginator.number }} of {{paginator.paginator.num_pages }}&nbsp;&nbsp;&nbsp;
        </span>

        {% if paginator.has_next %}
            <a  id="next" href="{{ paginator.next_page_number }}"> Next </a>
            {% else %}
         <span class="page-nulled" >
            Next 
        </span>
        {% endif %}

</div>

on click of next or previous , take the href val() and pass to the url as get variable like http://someurl?page=.

more reference here

Answer 5

Consider you have a member object named member_obj. From your model you can do something like this

To get the list of followings,

member_obj.followings will give you the list of keys , simply pass it to

followings_keys = member_obj.followings
Member.get(following_keys).

Then just loop through the members and display their messages.. This post on Modeling in appengine will help you a lot.