rcdict_thoughts.py

from rcdict import *

class User: pass

class Group(Model):
  name  = StringField()
  admin = ForeignKey(User)
  #users = ReverseForeignKey(User) generated automatically

class User(Model):
  name  = StringField()
  age   = IntegerField()
  group = ForiegnKey(Group)
  #posts = ReverseForeignKey(Post) generated automatically
  #admins = ReverseForeignKey(Group) generated automatically

class Post(Model):
  name = StringField()
  user = ForeignKey(User)



###############################################################################
###############################################################################

User.select('Jake' in User.name and User.age > 20 and User.posts.count > 5)
#                  A-      t0--          t1- B             t2--- a---- C

"""SELECT t0.id

FROM       unnamed_fields AS t0 ----------------- WHERE t0.field = 'user__name'
INNER JOIN unnamed_fields AS t1 ON t0.id = t1.id    AND t1.field = 'user__age'
INNER JOIN unnamed_fields AS t2 ON t0.id = t2.value AND t2.field = 'post__user_id' --ReverseForeignKey uses othertable__thistable_id and base.id = table.value

WHERE t0.field = 'user__name'

AND t0.value LIKE '%Jake%'
--           A---
AND t1.value > 20
--           B

GROUP BY t0.id
HAVING COUNT(t2.value) > 5
--     a-------------- C
"""

###############################################################################
###############################################################################

Post.select('Jake' in Post.user.group.admin.name)
#                  A-      t0-- t1--- t2--- t3--

"""SELECT t0.id

FROM       unnamed_fields AS t0 ----------------- WHERE t0.field = 'post__user_id'    ForeignKey uses table.field = thistable__othertable_id
INNER JOIN unnamed_fields AS t1 ON t0.value = t1.id AND t1.field = 'user__group_id' --and thistable.value = othertable.id
INNER JOIN unnamed_fields AS t2 ON t1.value = t2.id AND t2.field = 'group__admin_id'
INNER JOIN unnamed_fields AS t3 ON t2.value = t3.id AND t3.field = 'user__name'

WHERE t0.field = 'post__user_id'

AND t3.value LIKE '%Jake%'
--           A---
"""

###############################################################################
###############################################################################

Post.select(Post.user == Post.user.group.admin and Post.user.posts.count > 5)
# Inefficient:   t0-- A-      t1-- t2--- t3---          t4-- t5--- a---- B
# Efficient:     t0-- A-      (t0) t1--- t2--           (t0) t3--- a---- B

#Ineffecient:
"""SELECT t0.id

FROM       unnamed_fields AS t0 ----------------- WHERE t0.field = 'post__user_id'

INNER JOIN unnamed_fields AS t1 ON t0.id = t1.id    AND t1.field = 'post__user_id'
INNER JOIN unnamed_fields AS t2 ON t1.value = t2.id AND t2.field = 'user__group_id'
INNER JOIN unnamed_fields AS t3 ON t2.value = t3.id AND t3.field = 'group__admin_id'

INNER JOIN unnamed_fields AS t4 ON t0.id = t4.id    AND t4.field = 'post__user_id'
INNER JOIN unnamed_fields AS t5 ON t4.value = t5.id AND t5.field = 'post__user_id'

WHERE t0.field = 'post__user_id'

AND t0.value = t3.value
--           A

GROUP BY t0.id
HAVING COUNT(t5.value) > 5
--     a-------------- B
"""

#Efficient
"""SELECT t0.id

FROM       unnamed_fields AS t0 ----------------- WHERE t0.field = 'post__user_id'
INNER JOIN unnamed_fields AS t1 ON t0.value = t1.id AND t1.field = 'user__group_id'
INNER JOIN unnamed_fields AS t2 ON t1.value = t2.id AND t2.field = 'group__admin_id'
INNER JOIN unnamed_fields AS t3 ON t0.id = t3.field AND t3.field = 'post__user_id'

WHERE t0.field = 'post__user_id'

AND t0.value = t2.value
--           A

GROUP BY t0.id
HAVING COUNT(t3.value) > 5
--     a-------------- B
"""