python - Storing reference to other rows in SQLAlchemy Many-to-Many association table -

i'm working on project in sqlalchemy. let's (to simplify problem) there 3 tables @ play-- blog_posts, users, , users_daily_posts. first 2 this:

class blog_posts(db.model):     __tablename__ = 'blog_posts'     post_id = db.column(db.integer(), primary_key = true)     date = db.column(db.integer())     creator_id = db.column(db.integer())  class users(db.model):     __tablename__ = 'users'     user_id = db.column(db.integer(), primary_key = true)     likes = db.column(db.integer())     users_daily_posts = db.relationship('users_daily_posts', lazy='dynamic', backref=db.backref('user', lazy='dynamic')) 

for third table, expectation has primary keys user_id , date (say date integer), column likes, , column should keep track of blog posts on particular date. when post liked, propagates table , users table, blog_posts not store it. thought date should foreign key, turns out there uniqueness constraint on in original table, , won't unique in blog_posts table, think design like:

class users_daily_posts(db.model):     __tablename__ = 'users_daily_posts'     user_id = db.column(db.integer(), db.foreignkey('users.user_id', ondelete="cascade"), primary_key = true)     date = db.column(db.integer(), primary_key = true)     likes = db.column(db.integer())     blog_posts = ? 

for blog_posts, tried:

db.relationship('blog_posts', lazy='dynamic', backref=db.backref('posts', lazy='dynamic')) 

thinking might allow me add list of blog posts, gives error:

argumenterror: not determine join condition between parent/child tables on relationship users_daily_posts.blog_posts.  specify 'primaryjoin' expression.  if 'secondary' present, 'secondaryjoin' needed well. 

i've seen couple of posts error, don't understand primaryjoin expression be, i'm furthest can databases whiz. explain primary join , why? or @ least i'm doing wrong? if there different sqlalchemy idiom better suited purpose, i'd love hear it. time!

there many cases need association table between 2 classes. looking @ case, believe need column_property or simple query enough.

in case, have:

• each post have 1 creator or author, many 1 relationship;

• the "likes" can't no on users, can have a
  column property count how many likes;

• finally, "likes" more related visitors , posts. you
  need model in order keep information the
  visitors. , make relationship users.

try this:

from flask import flask flask.ext.sqlalchemy import sqlalchemy  import datetime  app = flask(__name__) app.config['sqlalchemy_database_uri'] = 'sqlite:////tmp/test.db' db = sqlalchemy(app)  class blog_post(db.model):     __tablename__ = 'blog_posts'     post_id = db.column(db.integer, primary_key = true)     creator_id = db.column(db.integer, db.foreignkey('users.user_id'), nullable=false)     headline = db.column(db.string(255), nullable=false)     date = db.column(db.date, nullable=false)     likes = db.column(db.integer)     def __init__(self, headline, body,creator_id):         self.headline = headline         self.creator_id = creator_id         self.date = datetime.datetime.today()   class user(db.model):     __tablename__ = 'users'     user_id = db.column(db.integer(), primary_key = true)     user_name = db.column(db.string(80), unique=true)     users_posts = db.column_property(         db.select([db.func.count(blog_post.post_id)]).\             where(blog_post.creator_id==user_id).\             correlate_except(blog_post)     )     def __init__(self, user_name):         self.user_name = user_name 

testing...

>>> db.create_all() >>> u = user('dedeco') >>> db.session.add(u) >>> db.session.commit() >>>  >>> p = blog_post('i dedeco 1','about dedeco 1',u.user_id) >>> db.session.add(p) >>> p = blog_post('i dedeco 2','about dedeco 2',u.user_id) >>> db.session.add(p) >>> p = blog_post('i dedeco 3','about dedeco 3',u.user_id) >>> db.session.add(p) >>> db.session.commit() >>> u.users_posts 3 

query:

>>> d = db.session.query(db.func.count(blog_post.post_id),db.func.strftime('%d/%m/%y', blog_post.date)).filter(blog_post.creator_id==u.user_id).group_by(db.func.strftime('%d/%m/%y', blog_post.date)) >>> d.all() [(3, u'04/11/2015')] 

you can see information many many on sqlalchemy documentation