Google App Engine join results of two tables

Question

I am developing a blog project using jinja2 for templates, Google App Engine for hosting and running it and Python as the server side language.

So, my models.py looks like this :

from google.appengine.ext import ndb

class User(ndb.Model):
    fullname = ndb.StringProperty(required=True)
    user_name = ndb.StringProperty(required=True)
    email = ndb.StringProperty(required=True)
    password = ndb.TextProperty(indexed=True,required=True)
    photo = ndb.StringProperty()
    location = ndb.StringProperty()


class Post(ndb.Model):
    title = ndb.StringProperty(required=True)
    content = ndb.TextProperty(required=True)
    created = ndb.DateTimeProperty(auto_now_add=True)
    last_modified = ndb.DateTimeProperty(auto_now=True)
    user = ndb.KeyProperty(kind=User)

Now what I want is to have a result set like this :

User.fullname | User.Photo | Post.*

I am showing the above details for a single post. Now each Post entry is linked with a User with the User's Key.

Now since joins are not supported in ndb query class, how do I fetch and merge the two results ?

I tried the GQL like this :

select User.fullname, User.photo, User.id, Post.title, Post.content, Post.created
from User, Post
where User.__key__ == Post.user

But I get this error when I run this in the GQLQuery in my datastore console :

GQL query error: Encountered "," at line 2, column 10. Was expecting one of: "group", "limit", "offset", "order", "where"

Any suggestions ?

TIA

EDIT:1 Tried this query :

posts = ndb.query(User,Post).filter(User.key == Post.user).order(-Post.created)

But I get the following error:

TypeError: 'module' object is not callable

EDIT:2 Here is an index I tried :

 kind: Post
  properties:
  - name: content
  - name: created
  - name: user
    direction: desc

But doesn't seem to work :O

joins are not supported. the query you show has an implicit join. you need to join manually not with a query. — Zig Mandel
@ZigMandel: Yep right! That is why I tried this : posts = ndb.query(User,Post).filter(User.key == Post.user).order(-Post.created) but unfortunately I get this error : TypeError: 'module' object is not callable — Abhishek Ghosh
that would again be a join and joins are not supported. goto first comment. — Zig Mandel
But this isn't an explicit join. Anyways, what do you suggest @ZigMandel ? — Abhishek Ghosh
First thing to remember - there are no tables as far as the Datastore is concerned ;-) — Tim Hoffman

Abhishek Ghosh Abhishek Ghosh · Accepted Answer · 2016-06-10T12:54:52

I implemented an array of dictionaries like this :

class HomeHandler(BlogHandler):
    def get(self,user=None):
        posts = Post.query().order(-Post.created)
        all_users = User.query()
        likes = Likes.query()

        list_dict = []

        for p in posts:
            p_dict = {}
            for u in all_users:
                if p.user == u.key:
                    p_dict['p_id'] = p.key.id()
                    p_dict['p_title'] = p.title
                    p_dict['p_content'] = p.content
                    p_dict['p_created'] = p.created
                    p_dict['a_name'] = u.fullname
                    p_dict['a_id'] = u.key.id()
            for l in likes:
                if l.post == p.key:
                    p_dict['like_count'] = l.like_count
                    list_dict.append(p_dict)

        if user_email:
            self.render('home.html',user=loggedin_user,
                                    list_dict = list_dict)
        else:
            self.render('home.html',user=loggedin_user,
                                    list_dict = list_dict)

And then in the template I iterate over the list like this and get data from each dictionary item :

{% if list_dict %}
      {% for l in list_dict %}
           //get stuff like this 
           // l['p_id'],etc
      {% endfor %}
{% endif %}

I found this better way better as iterating over lists and dictionaries is very easy and time consuming.

Google App Engine join results of two tables

1 Answers