Example #1
0
    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")
Example #2
0
    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())
Example #3
0
    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)')
Example #4
0
    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")
Example #5
0
    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"
        )
Example #6
0
    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",
        )
Example #7
0
    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)'
        )