20

I am building an app using Flask & SQLAlchemy. I basically have 3 tables: users, friendships and bestFriends:

A user can have many friends but only one best friend. So I want my model to be relational. 'One-to-many' for the relation between 'users' and 'friendships' & 'one-to-one' between 'users' and 'bestFriends'.

This is my Model:

from app import db
from sqlalchemy.orm import relationship, backref
from sqlalchemy import Table, Column, Integer, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
class users(db.Model):
 __tablename__ = "Users"
 id = db.Column(db.Integer, primary_key=True)
 userName = db.Column(db.String, nullable=False)
 userEmail = db.Column(db.String, nullable=False)
 userPhone = db.Column(db.String, nullable=False)
 userPass = db.Column(db.String, nullable=False)
 friendsR = db.relationship('friendships', backref='friendships.friend_id', primaryjoin='users.id==friendships.user_id', lazy='joined')
 def __init__(self, userName, userEmail, userPhone, userPass):
 self.userName = userName
 self.userEmail = userEmail
 self.userPhone = userPhone
 self.userPass = userPass
 def __repr__(self):
 return '{}-{}-{}-{}'.format(self.id, self.userName, self.userEmail, self.userPhone)
class friendships(db.Model):
 __tablename__ = "Friendships"
 id = db.Column(db.Integer, primary_key=True)
 user_id = db.Column(db.Integer, ForeignKey('Users.id'), nullable=False)
 friend_id = db.Column(db.Integer, ForeignKey('Users.id'), nullable=False)
 userR = relationship('users', foreign_keys='friendships.user_id')
 friendR = relationship('users', foreign_keys='friendships.friend_id')
 def __init__(self, user_id, friend_id):
 self.user_id = user_id
 self.friend_id = friend_id
 def __repr__(self):
 return '{}-{}-{}-{}'.format(self.user_id, self.friend_id)
class bestFriends(db.Model):
 __tablename__ = "BestFriends"
 id = db.Column(db.Integer, primary_key=True)
 user_id = db.Column(db.Integer, ForeignKey('Users.id'), nullable=False)
 best_friend_id = db.Column(db.Integer, ForeignKey('Users.id'), nullable=False)
 user = relationship('users', foreign_keys='bestFriends.user_id')
 best_friend = relationship('users', foreign_keys='bestFriends.best_friend_id')
 def __init__(self, user_id, best_friend_id):
 self.user_id = user_id
 self.best_friend_id = best_friend_id
 def __repr__(self):
 return '{}-{}-{}-{}'.format(self.user_id, self.best_friend_id)

I need to be able to query the list of friends of the user logged in as well as the best friend of that user in case exists. I also need to PAGINATE the result:

Here is my app.py function for displaying the friends of a user:

@app.route('/friendList<int:page>', methods=['GET', 'POST'])
@app.route('/friends')
def friendList(page=1):
if not session.get('logged_in'):
 return render_template('login.html')
 else:
 userID = session['user_id']
 userList = users.query.join(friendships).add_columns(users.id, users.userName, users.userEmail, friendships.user_id, friendships.friend_id).filter(users.id == friendships.friend_id).filter(friendships.user_id == userID).paginate(page, 1, False)
 return render_template(
 'friends.html', userList=userList)

And this would be the Jinja side of the code:

{% extends "layout.html" %}
{% block body %}
<div id="pagination">
{% if userList.has_prev %}
 <a href="{{ url_for('friendList', page=userList.prev_num) }}">Back</a>
{% endif %} 
{% if userList.has_next %}
 <a href="{{ url_for('friendList', page=userList.next_num) }}">Next</a>
{% endif %}
</div>
<div style="clear:both;"></div>
<div id="innerContent">
{% if userList %}
 {% for friends in userList %}
 <div class="contentUsers">
 {{ friends.userName }}
 </div>
 <br><br><br><br>
 {% endfor %}{% else %}<div>No friends</div>
{% endif %} 
</div>
 {% endblock %}

And if I query like this:

userList = db.session.query(users,friendships).filter(users.id == friendships.friend_id).filter(friendships.user_id == userID).paginate(page, 1, False)

I get this error:

InvalidRequestError: Could not find a FROM clause to join from. Tried joining to <class 'models.friendships'>, but got: Can't determine join between 'Users' and 'Friendships'; tables have more than one foreign key constraint relationship between them. Please specify the 'onclause' of this join explicitly.

If I query like this:

userList = users.query.join(friendships, users.id==friendships.user_id).add_columns(users.id, users.userName, users.userEmail, friendships.user_id, friendships.friend_id).filter(users.id == friendships.friend_id).filter(friendships.user_id == userID).paginate(page, 1, False)

I get the following error:

TypeError: 'Pagination' object is not iterable

I still think this later query is right way to go but I think there is something wrong in my relationships/foreign keys between tables!!!

If on the Jinja side I add the .items to the loop:

{% if userList.items %}
{% for friends in userList.items %}
 <div class="contentUsers">
 {{ friends.userName }}
 </div>
 <br><br><br><br>
{% endfor %}{% else %}<div>No friends</div>

{% endif %}

It doesn't loop at all and simply displays the else statement of "no friends"

R Claven
1,2402 gold badges15 silver badges27 bronze badges
asked Jan 12, 2015 at 10:37
2
  • See these questions which may steer you in the right direction: stackoverflow.com/questions/18468887/… stackoverflow.com/questions/18409645/… Commented Jan 12, 2015 at 12:57
  • Hello Matthewh and thank you for your comment. Now I realize why I was able to apply the paginate() to some of my query and others not. But I still dont know how to perform the join query I need with Flask-SQLAlchemy. I have simple query working with paginate like: "userList = users.query.paginate(page, 5, False)" But I don't know how to perform a Flask-SQLAlchemy version(with paginate) of for example:"SELECT users.userId, users.name, users.email, friends.userId, friendId FROM users INNER JOIN friends ON users.userId=friends.friendId WHERE friends.userId=1"... Commented Jan 12, 2015 at 13:22

2 Answers 2

72

The error message is telling you that SQLAlchemy can't determine how to join the two tables users and friendships, because there is more than one foreign key linking them. You need to explicitly define the join condition.

Try:

userList = users.query\
 .join(friendships, users.id==friendships.user_id)\
 .add_columns(users.userId, users.name, users.email, friends.userId, friendId)\
 .filter(users.id == friendships.friend_id)\
 .filter(friendships.user_id == userID)\
 .paginate(page, 1, False)
beporter
4,0683 gold badges43 silver badges46 bronze badges
answered Jan 13, 2015 at 4:50
Sign up to request clarification or add additional context in comments.

Sorry Matthewh, was very tired, did not realize the column names I was using where the ones from my MySql example not my SqlAlchemy model: this would be the correct collumn names for the query you wrote me(still gives me error I added to original question): userList = users.query.join(friendships, users.id==friendships.user_id).add_columns(users.id, users.userName, users.userEmail, friendships.user_id, friendships.friend_id).filter(users.id == friendships.friend_id).filter(friendships.user_id == userID).paginate(page, 1, False)
Can you explain what the .add_columns() does? Is that necessary after joining?
1

Ok looks like after getting some sleep and viewing Matthewh's suggestion I almost found the final solution:

My model:

from app import db
from sqlalchemy.orm import relationship, backref
from sqlalchemy import Table, Column, Integer, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
class users(db.Model):
 __tablename__ = "Users"
 id = db.Column(db.Integer, primary_key=True)
 userName = db.Column(db.String, nullable=False)
 userEmail = db.Column(db.String, nullable=False)
 userPhone = db.Column(db.String, nullable=False)
 userPass = db.Column(db.String, nullable=False)
 def __init__(self, userName, userEmail, userPhone, userPass):
 self.userName = userName
 self.userEmail = userEmail
 self.userPhone = userPhone
 self.userPass = userPass
 def __repr__(self):
 return '{}-{}-{}-{}'.format(self.id, self.userName, self.userEmail, self.userPhone)
 class friendships(db.Model):
 __tablename__ = "Friendships"
 id = db.Column(db.Integer, primary_key=True)
 user_id = db.Column(db.Integer, db.ForeignKey('Users.id'), nullable=False)
 friend_id = db.Column(db.Integer, db.ForeignKey('Users.id'), nullable=False)
 userR = db.relationship('users', foreign_keys='friendships.user_id')
 friendR = db.relationship('users', foreign_keys='friendships.friend_id')
 def __init__(self, user_id, friend_id):
 self.user_id = user_id
 self.friend_id = friend_id
 def __repr__(self):
 return '{}-{}-{}-{}'.format(self.user_id, self.friend_id)
class bestFriends(db.Model):
 __tablename__ = "BestFriends"
 id = db.Column(db.Integer, primary_key=True)
 user_id = db.Column(db.Integer, db.ForeignKey('Users.id'), nullable=False)
 best_friend_id = db.Column(db.Integer, db.ForeignKey('Users.id'), nullable=False)
 user = db.relationship('users', foreign_keys='bestFriends.user_id')
 best_friend = db.relationship('users', foreign_keys='bestFriends.best_friend_id')
 def __init__(self, user_id, best_friend_id):
 self.user_id = user_id
 self.best_friend_id = best_friend_id
 def __repr__(self):
 return '{}-{}-{}-{}'.format(self.user_id, self.best_friend_id)

My app.py function(shows friends of user logged in):

@app.route('/friendList<int:page>', methods=['GET', 'POST'])
@app.route('/friends')
 def friendList(page=1):
 if not session.get('logged_in'):
 return render_template('login.html')
 else:
 userID = session['user_id']
 userList = users.query.join(friendships, users.id==friendships.user_id).add_columns(users.id, users.userName, users.userEmail, friendships.id, friendships.user_id, friendships.friend_id).filter(friendships.friend_id == userID).paginate(page, 1, False)
 return render_template('friends.html', userList=userList)

And the Jinja side of 'friends.html':

{% extends "layout.html" %}
{% block body %}
 <div id="pagination">
 {% if userList.has_prev %}
 <a href="{{ url_for('friendList', page=userList.prev_num)}}">Back</a>
 {% endif %}
 {% if userList.has_next %}
 <a href="{{ url_for('friendList', page=userList.next_num)}}">Next</a>
 {% endif %}
 </div>
 <div style="clear:both;"></div>
 <div id="innerContent">
 {% if userList.items %}
 {% for friends in userList.items %}
 <div class="contentUsers">
 {{ friends.userName }}
 </div>
 <br><br><br><br>
 {% endfor %}
 {% else %}
 <div>No friends</div>
 {% endif %}
 </div>
{% endblock %}

This gives me an object(friends in userList.items) like this:

([email protected], 2, u'Carlos', u'[email protected]', 2, 2, 1)

I was expecting this: |users.id|users.userName|users.userEmail|users.userPhone|friendships.id|friendships.user_id(the friends)| friendships.friend_id(the logged in user)|

So I have the following doubts/questions:

I am not completely understanding the structure of the object resulting for the query:

-repeated user id, name and email -what is the 'u' infront of second name and email

I am not completely understanding the relations model structure:

  • why is the following NOT required in the 'users' class of the database model:

    #friendsR = db.relationship('friendships', backref='friendships.friend_id', primaryjoin='users.id==friendships.user_id', lazy='joined')

IS my database model correctly defined regarding standarized relationship model as posted in this answer or should I improove some how??

arulmr
8,8709 gold badges57 silver badges70 bronze badges
answered Jan 13, 2015 at 13:03

Why don't you define only two models (users & friendships) and add a boolean entry for a "is best friend" friendship? That would simplify your queries by a lot. If you wanna find a user's bestfriend you just have to add a filter by "is best friend equals true".

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.