title: Flask SQL Alchemy self join
body: <p>I want to perform self join with the use of Flask SQL Alchemy. I have following table in PostgreSQL:</p>
  <pre><code>CREATE TABLE table0 (
      id serial PRIMARY KEY,
      property_0 TEXT,
      property_1 TEXT);
  
  
  insert into table0(property_0, property_1) values('aa', 'bb');
  insert into table0(property_0, property_1) values('cc', 'dd');
  insert into table0(property_0, property_1) values('aa', 'ee');
  insert into table0(property_0, property_1) values('bb', 'ee');
  insert into table0(property_0, property_1) values('bb', 'rwqe');
  insert into table0(property_0, property_1) values('aa', 'value_for_t00');
  insert into table0(property_0, property_1) values('bb', 'value_for_t00');
  insert into table0(property_0, property_1) values('aa', 'value_for_t01');
  insert into table0(property_0, property_1) values('bb', 'value_for_t01');
  </code></pre>
  <p>and following query:</p>
  <pre class="lang-sql prettyprint-override"><code>select t00.property_0 as t00_property_0, t01.property_0 as t01_property_0, t00.property_1 as t00_property_1, t01.property_1 as t01_property_1
  from table0 t00
  left join table0 t01 on t00.property_0 = t01.property_0 and t00.property_1 = 'value_for_t00' and t01.property_1 = 'value_for_t01';
  </code></pre>
  <p>which gives me the expected correct output:</p>
  <pre><code> t00_property_0 | t01_property_0 | t00_property_1 | t01_property_1
  ----------------+----------------+----------------+----------------
   aa             |                | bb             |
   cc             |                | dd             |
   aa             |                | ee             |
   bb             |                | ee             |
   bb             |                | rwqe           |
   aa             | aa             | value_for_t00  | value_for_t01
   bb             | bb             | value_for_t00  | value_for_t01
   aa             |                | value_for_t01  |
   bb             |                | value_for_t01  |
  (9 rows)
  </code></pre>
  <p>Basically in the table there are values in two rows which I would like to have in the one output row like in the following two:</p>
  <pre><code> aa             | aa             | value_for_t00  | value_for_t01
   bb             | bb             | value_for_t00  | value_for_t01
  </code></pre>
  <p>but I want also to preserve the rest of rows with empty values.</p>
  <p>In the Flask application I have following data model:</p>
  <pre class="lang-py prettyprint-override"><code>class Table0(db.Model):
      __tablename__ = 'table0'
  
      id        = db.Column(db.Integer, primary_key=True)
      property_0 = db.Column(db.String)
      property_1 = db.Column(db.String)
      
  
      def __repr__(self):
          return &quot;&lt;Relation(id='%s', property_0='%s', property_1='%s)&gt;&quot; % (self.id, self.property_0, self.property_1)
  </code></pre>
  <p>and following query:</p>
  <pre class="lang-py prettyprint-override"><code>from flask_sqlalchemy import SQLAlchemy
  import pprint
  
  db = SQLAlchemy()
  
  t00 = db.aliased(Table0)
  t01 = db.aliased(Table0)
  
  on = (t00.property_0==t01.property_0) and \
       (t00.property_1=='value_for_t00') and \
       (t01.property_1=='value_for_t01')
  
  result = db.session.query(t00)\
                      .join(t01, on, isouter=True)\
                      .with_entities(t00.property_0.label(&quot;t00_property_0&quot;),
                                      t01.property_0.label(&quot;t01_property_0&quot;),
                                      t00.property_1.label(&quot;t00_property_1&quot;),
                                      t01.property_1.label(&quot;t01_property_1&quot;))\
                      .all()
      
  pprint.pprint(result)
  </code></pre>
  <p>Which I supposed was the query that corresponds to the query used in PostgreSQL, but for some reason it gives me different (and wrong) result:</p>
  <pre><code>[('aa', 'aa', 'bb', 'value_for_t01'),
   ('aa', 'aa', 'bb', 'value_for_t00'),
   ('aa', 'aa', 'bb', 'ee'),
   ('aa', 'aa', 'bb', 'bb'),
   ('cc', 'cc', 'dd', 'dd'),
   ('aa', 'aa', 'ee', 'value_for_t01'),
   ('aa', 'aa', 'ee', 'value_for_t00'),
   ('aa', 'aa', 'ee', 'ee'),
   ('aa', 'aa', 'ee', 'bb'),
   ('bb', 'bb', 'ee', 'value_for_t01'),
   ('bb', 'bb', 'ee', 'value_for_t00'),
   ('bb', 'bb', 'ee', 'rwqe'),
   ('bb', 'bb', 'ee', 'ee'),
   ('bb', 'bb', 'rwqe', 'value_for_t01'),
   ('bb', 'bb', 'rwqe', 'value_for_t00'),
   ('bb', 'bb', 'rwqe', 'rwqe'),
   ('bb', 'bb', 'rwqe', 'ee'),
   ('aa', 'aa', 'value_for_t00', 'value_for_t01'),
   ('aa', 'aa', 'value_for_t00', 'value_for_t00'),
   ('aa', 'aa', 'value_for_t00', 'ee'),
   ('aa', 'aa', 'value_for_t00', 'bb'),
   ('bb', 'bb', 'value_for_t00', 'value_for_t01'),
   ('bb', 'bb', 'value_for_t00', 'value_for_t00'),
   ('bb', 'bb', 'value_for_t00', 'rwqe'),
   ('bb', 'bb', 'value_for_t00', 'ee'),
   ('aa', 'aa', 'value_for_t01', 'value_for_t01'),
   ('aa', 'aa', 'value_for_t01', 'value_for_t00'),
   ('aa', 'aa', 'value_for_t01', 'ee'),
   ('aa', 'aa', 'value_for_t01', 'bb'),
   ('bb', 'bb', 'value_for_t01', 'value_for_t01'),
   ('bb', 'bb', 'value_for_t01', 'value_for_t00'),
   ('bb', 'bb', 'value_for_t01', 'rwqe'),
   ('bb', 'bb', 'value_for_t01', 'ee')]
  </code></pre>
  <p>Why the results are not the same and how to correct this?</p>