def where_condition_for_page(ordering_columns, place, dialect):
"""Construct the SQL condition required to restrict a query to the desired
page.
:param ordering_columns: The query's ordering columns
:type ordering_columns: list(:class:`.columns.OC`)
:param place: The starting position for the page
:type place: tuple
:param dialect: The SQL dialect in use
:returns: An SQLAlchemy expression suitable for use in ``.where()`` or
``.filter()``.
"""
if len(ordering_columns) != len(place):
raise InvalidPage(
"Page marker has different column count to query's order clause")
zipped = zip(ordering_columns, place)
swapped = [c.pair_for_comparison(value, dialect) for c, value in zipped]
row, place_row = zip(*swapped)
if len(row) == 1:
condition = row[0] > place_row[0]
else:
condition = func.row(*row) > func.row(*place_row)
return condition
def where_condition_for_page(ordering_columns, place):
row, place_row = paging_condition(ordering_columns, place)
if len(row) == 1:
condition = row[0] > place_row[0]
else:
condition = func.row(*row) > func.row(*place_row)
return condition
def _apply_before_or_after(self) -> PaginatedQuery:
"""Apply the "before" or "after" restrictions if necessary."""
# pylint: disable=assignment-from-no-return
if not (self.after_id or self.before_id):
return self
query = self
# determine the ID of the "anchor item" that we're using as an upper or lower
# bound, and which type of bound it is
if self.after_id:
anchor_id = self.after_id
# since we're looking for other items "after" the anchor item, it will act
# as an upper bound when the sort order is descending, otherwise it's a
# lower bound
is_anchor_upper_bound = self.sort_desc
elif self.before_id:
anchor_id = self.before_id
# opposite of "after" behavior - when looking "before" the anchor item, it's
# an upper bound if the sort order is *ascending*
is_anchor_upper_bound = not self.sort_desc
subquery = self._anchor_subquery(anchor_id)
# restrict the results to items on the right "side" of the anchor item
if is_anchor_upper_bound:
query = query.filter(func.row(*self.sorting_columns) < subquery)
else:
query = query.filter(func.row(*self.sorting_columns) > subquery)
return query
def _apply_before_or_after(self) -> 'PaginatedQuery':
"""Apply the "before" or "after" restrictions if necessary."""
if not (self.after_id or self.before_id):
return self
query = self
# determine the ID of the "anchor item" that we're using as an upper or
# lower bound, and which type of bound it is
if self.after_id:
anchor_id = self.after_id
# since we're looking for other items "after" the anchor item, it
# will act as an upper bound when the sort order is descending,
# otherwise it's a lower bound
is_anchor_upper_bound = self.sort_desc
elif self.before_id:
anchor_id = self.before_id
# opposite of "after" behavior - when looking "before" the anchor
# item, it's an upper bound if the sort order is *ascending*
is_anchor_upper_bound = not self.sort_desc
# create a subquery to get comparison values for the anchor item
id_column = list(self.model_cls.__table__.primary_key)[0]
subquery = (self.request.db_session.query(
*self.sorting_columns).filter(id_column == anchor_id).subquery())
# restrict the results to items on the right "side" of the anchor item
if is_anchor_upper_bound:
query = query.filter(func.row(*self.sorting_columns) < subquery)
else:
query = query.filter(func.row(*self.sorting_columns) > subquery)
return query
def where_condition_for_page(ordering_columns, place):
"""Construct the SQL condition required to restrict a query to the desired
page.
:param ordering_columns: The query's ordering columns
:type ordering_columns: list(:class:`.columns.OC`)
:param place: The starting position for the page
:type place: tuple
:returns: An SQLAlchemy expression suitable for use in ``.where()`` or
``.filter()``.
"""
row, place_row = paging_condition(ordering_columns, place)
if len(row) == 1:
condition = row[0] > place_row[0]
else:
condition = func.row(*row) > func.row(*place_row)
return condition
def get_resume_query(session):
user_data = func.array_agg(func.row(User.id, User.name)).label('user')
query = session.query(
Delivery.status,
Address.city,
user_data). \
join(User, Delivery.user_id == User.id). \
join(Address, Delivery.user_id == Address.user_id)
deliveries = query.group_by(Delivery.status, Address.city)
return deliveries
def form_query(specs):
jsonified = func.array_to_json(
func.array_agg(
func.row(accel_data.seq_id, accel_data.node, accel_data.axis,
accel_data.accel)))
starting_seq = int(specs['min_sequence']) + 2000 * int(specs['userstart'])
ending_seq = int(specs['min_sequence']) + 2000 * int(specs['userfinish'])
skip_every = int(2000 / int(specs['freq']))
print(starting_seq, ending_seq)
vectors = (db.session.query(accel_data.seq_id, jsonified).group_by(
accel_data.seq_id).order_by(accel_data.seq_id).filter(
accel_data.seq_id.between(starting_seq, ending_seq)).filter(
accel_data.seq_id % skip_every == 0)).yield_per(1000)
return vectors
def test_function_against_row_constructor(self, connection):
stmt = select(func.row_to_json(func.row(1, "foo")))
eq_(connection.scalar(stmt), {"f1": 1, "f2": "foo"})
def where_condition_for_page(ordering_columns, place):
row, place_row = paging_condition(ordering_columns, place)
condition = func.row(*row) > func.row(*place_row)
return condition