I want to perform self join with the use of Flask SQL Alchemy. I have following table in PostgreSQL:
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');
and following query:
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';
which gives me the expected correct output:
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)
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:
aa | aa | value_for_t00 | value_for_t01
bb | bb | value_for_t00 | value_for_t01
but I want also to preserve the rest of rows with empty values.
In the Flask application I have following data model:
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 "<Relation(id='%s', property_0='%s', property_1='%s)>" % (self.id, self.property_0, self.property_1)
and following query:
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("t00_property_0"),
t01.property_0.label("t01_property_0"),
t00.property_1.label("t00_property_1"),
t01.property_1.label("t01_property_1"))\
.all()
pprint.pprint(result)
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:
[('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')]
Why the results are not the same and how to correct this?
