CODEWITHSUNDEEP
pythonsqlitesqlalchemy
yzernik
yzernik

Reputation: 1191

SQLAlchemy query with where with comparison on tuple of multiple columns

I have a query that I need to support pagination in my app.

def get_paginated_items(
        self,
        limit: int,
        last_shipment_time: int = MAX_INT,
        last_item_id: int = MAX_INT,
    s = (
        select([self.items])
        .where((
            self.items.c.shipment_time,
            self.items.c.item_id,
        ) < (
            last_shipment_time,
            last_item_id,
        ))
        .order_by(
            self.items.c.shipment_time.desc(),
            self.items.c.item_id.desc(),
        )
        .limit(limit)
    )
    with self.get_connection() as connection:
        result = connection.execute(s)
        return result.fetchall()

If two items have the same shipment_time, the item_id should be used to break the tie in the comparison.

Apparently SQL have support for this (https://stackoverflow.com/a/52376813/1639564). However, when I use this query, it seems to ignore the second element of the tuple.

I am using SQLite for the database.

Upvotes: 3

Views: 1029

Answers (1)

rfkortekaas
rfkortekaas

Reputation: 6484

You need to specify that you are using a tuple in the where condition:

from sqlalchemy import tuple_

def get_paginated_items(
        self,
        limit: int,
        last_shipment_time: int = MAX_INT,
        last_item_id: int = MAX_INT,
    s = (
        select([self.items])
        .where(tuple_(
            self.items.c.shipment_time,
            self.items.c.item_id,
        ) < tuple_(
            last_shipment_time,
            last_item_id,
        ))
        .order_by(
            self.items.c.shipment_time.desc(),
            self.items.c.item_id.desc(),
        )
        .limit(limit)
    )
    with self.get_connection() as connection:
        result = connection.execute(s)
        return result.fetchall()

Upvotes: 5

Related Questions