title: Nested joins and aliases with (Flask-)SQLAlchemy
body: <p>I'm trying to switch from using raw SQL to SQLAlchemy/Flask-SQLAlchemy. As far as I learned simple queries, I can't understand how to use subqueries, nested joins and aliases. </p>
  
  <p>Right now I have my SQL query:</p>
  
  <pre><code>SELECT device.*, wifi.name as room FROM
  (SELECT * FROM devices WHERE mac="{0}") as device
  LEFT JOIN
      (SELECT w2.*, wifi_map.name as name FROM
          (SELECT * FROM (SELECT * FROM wifi ORDER BY signal_pow DESC) as w1 GROUP BY device_id) as w2
          INNER JOIN wifi_map
          ON w2.bssid=wifi_map.bssid)
      as wifi
  ON device.id=wifi.device_id
  </code></pre>
  
  <p>I managed to convert the <code>LEFT JOIN</code> part to SQLAlchemy (the one that's aliased as <code>wifi</code>). It looks like this:</p>
  
  <pre><code>sorted_wifi = db.session.query(WiFi).order_by(WiFi.signal_pow.desc())\
      .join(WiFi.map).subquery()
  wifi_with_room = db.session.query().add_entity(WiFi, alias=sorted_wifi)\
      .group_by('device_id')
  </code></pre>
  
  <p>So here <code>wifi_with_room</code> is list of rows with model <code>Wifi</code> and <code>WiFi.map</code> as list of models <code>WiFiMap</code> that match (based on <code>WiFi.bssid == WiFiMap.bssid</code>). But I don't know how I'm supposed to join <code>wifi_with_room</code> together with <code>Device</code>. I tried</p>
  
  <pre><code>db.session.query(Device).outerjoin(wifi_with_room)
  </code></pre>
  
  <p>But that gives me error </p>
  
  <blockquote>
    <p>Every derived table must have its own alias</p>
  </blockquote>
  
  <p>So then I tried</p>
  
  <pre><code>db.session.query(Device).outerjoin(wifi_with_room.alias('wifi'))
  </code></pre>
  
  <p>But it returned </p>
  
  <blockquote>
    <p>AttributeError: 'Query' object has no attribute 'alias'</p>
  </blockquote>
  
  <p>What I want to achieve is list of rows that have:</p>
  
  <ul>
  <li>model <code>Device</code> (and all it's elements)</li>
  <li>in <code>Device.room</code> name of the room that's returned from <code>WiFiMap.name</code> by matching <code>WiFi.device_id == Device.id</code> and then <code>WiFiMap.bssid == WiFi.bssid</code></li>
  </ul>
  
  <hr>
  
  <p>Here are my models:</p>
  
  <pre><code>class Device(db.Model):
      __tablename__ = 'devices'
  
      id = db.Column(db.Integer, primary_key=True)
      mac = db.Column(db.Text)
      vendor = db.Column(db.Text)
      model = db.Column(db.Text)
      name = db.Column(db.Text)
      resolution_h = db.Column(db.Integer)
      resolution_w = db.Column(db.Integer)
      system_version = db.Column(db.Text)
      cpu_model = db.Column(db.Text)
      gpu = db.Column(db.Text)
      gpu_clock = db.Column(db.Integer)
      ram = db.Column(db.Integer)
      layouts = db.Column(db.Text)
      screen_size = db.Column(db.Float)
      screen_density = db.Column(db.Integer)
      imei = db.Column(db.Integer)
      density_cat = db.Column(db.Text)
      graphics_cat = db.Column(db.Text)
      type = db.Column(db.Text)
      last_active = db.Column(db.DateTime, onupdate=datetime.datetime.now)
      unavailable = db.Column(db.Integer)
  
  class WiFi(db.Model):
      __tablename__ = 'wifi'
  
      id = db.Column(db.Integer, primary_key=True)
      device_id = db.Column(db.Integer, db.ForeignKey('devices.id', onupdate="CASCADE", ondelete="CASCADE"), index=True)
      bssid = db.Column(db.String(17), index=True)
      ssid = db.Column(db.Text)
      signal_pow = db.Column(db.Integer)
  
  class WiFiMap(db.Model):
      __tablename__ = 'wifi_map'
  
      room = db.Column(db.Integer)
      x = db.Column(db.Integer)
      y = db.Column(db.Integer)
      bssid = db.Column(db.String(17), db.ForeignKey('wifi.bssid'), primary_key=True)
      ssid = db.Column(db.Text)
      name = db.Column(db.Text)
      wifi = db.relationship('WiFi', backref='map', lazy='immediate')
  </code></pre>