CODEWITHSUNDEEP
sqlalchemyflask-sqlalchemy
sb32134
sb32134

Reputation: 430

How to create a parent-child-subchild information in flask-sqlalchemy

I'm using Flask-SQLAlchemy and flask on server side. I want to create a relationship between User(Parent), Category(Child) and item(Subchild). I don't know if a parent-child-subchild is right way to approach this but my end goal is to easily fetch all the items rated by user in each category. Following is one of the cases i tried but it doesn't work, probably because i'm doing something wrong.

I have an item table with item category(such as fiction, non-fiction, crime book). each category have 5 items(books). I want to let user rate five items from each category. So, i have following table. 

class User(db.Model, UserMixin):
  __tablename__ = "users"
  id = db.Column('user_id',db.Integer, primary_key=True)
  active = db.Column(db.Boolean(), nullable=False, default=False)
  username = db.Column(db.String(50), nullable=False, unique=True)
  password = db.Column(db.String(255), nullable=False, default='')

# do we need unique=True in any column
#let's create class for item(child) and category(parent) 
class Rated_item(db.Model):
  __tablename__ = 'item'
  id = db.Column(db.Integer, primary_key=True)
  item_id = db.Column(db.Integer)
  category_id = db.Column(db.Integer)
  quality = db.Column(db.String(50))
  taste = db.Column(db.String(50))
  user_id = db.Column(db.Integer, db.ForeignKey('users.user_id'))
  # evaluation_status is set to True, once a user finishes an item.
  evaluation_status = db.Column(db.Boolean, unique=False, default=False)

Since i'm working with flask-jinja based webpage, every-time a user logs in, i need to send status of user i.e how many categories have been rated by user and how many items have been rated in each category.

I tried following but i think i'm doing something conceptually wrong.

status=Rated_item.query.filter_by(user_id = current_user.id).order_by(Rated_item.pub_date.desc()).all()

Any suggestion how should i improve my table or query to get all categories (and all items in each category) rated by user.

Upvotes: 0

Views: 872

Answers (1)

thebignoob
thebignoob

Reputation: 471

''' items rated by user ''' 

status1 = Rated_item.query.func.count(distinct(item_id)).filter_by(user_id == current_user.id)

'''categories rated by user'''

status2 = Rated_item.query.func.count(distinct(category_id)).filter_by(user_id == current_user.id)

I guess this query would help you.

Upvotes: 1

Related Questions