title: Flask-SQLAlchemy Query Join relational tables
body: <p>I am building an app using Flask &amp; SQLAlchemy. I basically have 3 tables: users, friendships and bestFriends:</p>
  
  <p>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' &amp; 'one-to-one' between 'users' and 'bestFriends'. </p>
  
  <p>This is my Model:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>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:</p>
  
  <p>Here is my app.py function for displaying the friends of a user:</p>
  
  <pre><code>@app.route('/friendList&lt;int:page&gt;', 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)
  </code></pre>
  
  <p>And this would be the Jinja side of the code:</p>
  
  <pre><code>{% extends "layout.html" %}
  {% block body %}
  
  &lt;div id="pagination"&gt;
  {% if userList.has_prev %}
          &lt;a href="{{ url_for('friendList', page=userList.prev_num) }}"&gt;Back&lt;/a&gt;
  {% endif %} 
  
  {% if userList.has_next %}
          &lt;a href="{{ url_for('friendList', page=userList.next_num) }}"&gt;Next&lt;/a&gt;
  {% endif %}
  &lt;/div&gt;
  
  &lt;div style="clear:both;"&gt;&lt;/div&gt;
  
  &lt;div id="innerContent"&gt;
  {% if userList %}
      {% for friends in userList %}
                      &lt;div class="contentUsers"&gt;
                          {{ friends.userName }}
                      &lt;/div&gt;
  
                      &lt;br&gt;&lt;br&gt;&lt;br&gt;&lt;br&gt;
  
      {% endfor %}{% else %}&lt;div&gt;No friends&lt;/div&gt;
  {% endif %} 
  
  &lt;/div&gt;
    {% endblock %}
  </code></pre>
  
  <p>And if I query like this:</p>
  
  <pre><code>userList = db.session.query(users,friendships).filter(users.id == friendships.friend_id).filter(friendships.user_id == userID).paginate(page, 1, False)
  </code></pre>
  
  <p>I get this error:</p>
  
  <pre><code>InvalidRequestError: Could not find a FROM clause to join from. Tried joining to &lt;class 'models.friendships'&gt;, 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.
  </code></pre>
  
  <p>If I query like this:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>I get the following error:</p>
  
  <pre><code>TypeError: 'Pagination' object is not iterable
  </code></pre>
  
  <p>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!!!</p>
  
  <p>If on the Jinja side I add the .items to the loop:</p>
  
  <pre><code>{% if userList.items %}
  {% for friends in userList.items %}
  
                  &lt;div class="contentUsers"&gt;
                      {{ friends.userName }}
                  &lt;/div&gt;
  
                  &lt;br&gt;&lt;br&gt;&lt;br&gt;&lt;br&gt;
  
  {% endfor %}{% else %}&lt;div&gt;No friends&lt;/div&gt;
  </code></pre>
  
  <p>{% endif %} </p>
  
  <p>It doesn't loop at all and simply displays the else statement of "no friends"   </p>