def test_plain_join(self):
table1 = self.tables.people
table2 = self.tables.books
subq = select([table2.c.book_id]).\
where(table2.c.book_owner_id == table1.c.people_id)
# FROM books, people? isn't this wrong? No! Because
# this is only a fragment, books isn't in any other FROM clause
self.assert_compile(
join(table1, lateral(subq, name="alias"), true()),
"people JOIN LATERAL (SELECT books.book_id AS book_id "
"FROM books, people WHERE books.book_owner_id = people.people_id) "
"AS alias ON true")
# put it in correct context, implicit correlation works fine
self.assert_compile(
select([table1]).select_from(
join(table1, lateral(subq, name="alias"), true())),
"SELECT people.people_id, people.age, people.name "
"FROM people JOIN LATERAL (SELECT books.book_id AS book_id "
"FROM books WHERE books.book_owner_id = people.people_id) "
"AS alias ON true")
# explicit correlation
subq = subq.correlate(table1)
self.assert_compile(
select([table1]).select_from(
join(table1, lateral(subq, name="alias"), true())),
"SELECT people.people_id, people.age, people.name "
"FROM people JOIN LATERAL (SELECT books.book_id AS book_id "
"FROM books WHERE books.book_owner_id = people.people_id) "
"AS alias ON true")
def get_query(self):
"""Return a query for apartments and their distances to pins."""
lateral_pin = lateral(
Pin.query.with_entities(Pin.id.label('id'), Pin.address.label('pin_address')), name='pin')
lateral_distance = lateral(Distance.query.with_entities(
Distance.minutes.label('minutes'), Distance.meters.label('meters')).filter(
Distance.apartment_id == Apartment.id, Distance.pin_id == lateral_pin.c.id), name='distance')
# We need all apartment columns expanded.
# If the query has only `Apartment` plus the columns, this error is raised:
# AttributeError: 'result' object has no attribute 'id'
columns = [c for c in Apartment.__table__.columns] + \
[lateral_pin.c.pin_address, lateral_distance.c.minutes, lateral_distance.c.meters]
query = self.session.query(*columns).filter(Apartment.active.is_(True))
# All pins that might have a distance or not.
return query.join(lateral_pin, true()).outerjoin(lateral_distance, true())
def test_standalone(self):
table1 = self.tables.people
subq = select([table1.c.people_id])
# alias name is not rendered because subquery is not
# in the context of a FROM clause
self.assert_compile(lateral(subq, name="alias"),
'LATERAL (SELECT people.people_id FROM people)')
self.assert_compile(subq.lateral(name="alias"),
'LATERAL (SELECT people.people_id FROM people)')
def test_from_function(self):
bookcases = self.tables.bookcases
srf = lateral(func.generate_series(1, bookcases.c.bookcase_shelves))
self.assert_compile(
select([bookcases]).select_from(bookcases.join(srf, true())),
"SELECT bookcases.bookcase_id, bookcases.bookcase_owner_id, "
"bookcases.bookcase_shelves, bookcases.bookcase_width "
"FROM bookcases JOIN "
"LATERAL generate_series(:generate_series_1, "
"bookcases.bookcase_shelves) AS anon_1 ON true")
def test_from_function(self):
bookcases = self.tables.bookcases
srf = lateral(func.generate_series(1, bookcases.c.bookcase_shelves))
self.assert_compile(
select([bookcases]).select_from(bookcases.join(srf, true())),
"SELECT bookcases.bookcase_id, bookcases.bookcase_owner_id, "
"bookcases.bookcase_shelves, bookcases.bookcase_width "
"FROM bookcases JOIN "
"LATERAL generate_series(:generate_series_1, "
"bookcases.bookcase_shelves) AS anon_1 ON true"
)
def test_plain_join(self):
table1 = self.tables.people
table2 = self.tables.books
subq = select([table2.c.book_id]).where(
table2.c.book_owner_id == table1.c.people_id
)
# FROM books, people? isn't this wrong? No! Because
# this is only a fragment, books isn't in any other FROM clause
self.assert_compile(
join(table1, lateral(subq, name="alias"), true()),
"people JOIN LATERAL (SELECT books.book_id AS book_id "
"FROM books, people WHERE books.book_owner_id = people.people_id) "
"AS alias ON true",
)
# put it in correct context, implicit correlation works fine
self.assert_compile(
select([table1]).select_from(
join(table1, lateral(subq, name="alias"), true())
),
"SELECT people.people_id, people.age, people.name "
"FROM people JOIN LATERAL (SELECT books.book_id AS book_id "
"FROM books WHERE books.book_owner_id = people.people_id) "
"AS alias ON true",
)
# explicit correlation
subq = subq.correlate(table1)
self.assert_compile(
select([table1]).select_from(
join(table1, lateral(subq, name="alias"), true())
),
"SELECT people.people_id, people.age, people.name "
"FROM people JOIN LATERAL (SELECT books.book_id AS book_id "
"FROM books WHERE books.book_owner_id = people.people_id) "
"AS alias ON true",
)
def test_standalone(self):
table1 = self.tables.people
subq = select([table1.c.people_id])
# alias name is not rendered because subquery is not
# in the context of a FROM clause
self.assert_compile(
lateral(subq, name="alias"),
'LATERAL (SELECT people.people_id FROM people)'
)
self.assert_compile(
subq.lateral(name="alias"),
'LATERAL (SELECT people.people_id FROM people)'
)