def go(value): return statement.options( with_loader_criteria( HasFoob, lambda cls: cls.name == value, include_aliases=True, ))
def test_query_outerjoin_global_criteria(self, user_address_fixture): User, Address = user_address_fixture s = Session(testing.db) q = (s.query(User, Address).outerjoin(User.addresses).options( with_loader_criteria( Address, ~Address.email_address.like("ed@%"), )).order_by(User.id)) self.assert_compile( q, "SELECT users.id AS users_id, users.name AS users_name, " "addresses.id AS addresses_id, " "addresses.user_id AS addresses_user_id, " "addresses.email_address AS addresses_email_address " "FROM users LEFT OUTER JOIN addresses " "ON users.id = addresses.user_id AND " "addresses.email_address NOT LIKE :email_address_1 " "ORDER BY users.id", ) eq_( q.all(), [ (User(id=7), Address(id=1)), (User(id=8), None), # three addresses not here (User(id=9), Address(id=5)), (User(id=10), None), ], )
def test_select_selectinload_mapper_mapper_criteria( self, user_address_fixture ): User, Address = user_address_fixture stmt = select(User).options( selectinload(User.addresses), with_loader_criteria(Address, Address.email_address != "name"), ) s = Session(testing.db, future=True) with self.sql_execution_asserter() as asserter: s.execute(stmt).all() asserter.assert_( CompiledSQL( "SELECT users.id, users.name FROM users", [], ), CompiledSQL( "SELECT addresses.user_id AS addresses_user_id, addresses.id " "AS addresses_id, addresses.email_address " "AS addresses_email_address FROM addresses " "WHERE addresses.user_id IN ([POSTCOMPILE_primary_keys]) " "AND addresses.email_address != :email_address_1 " "ORDER BY addresses.id", [{"primary_keys": [7, 8, 9, 10], "email_address_1": "name"}], ), )
def go(stmt, value): return stmt.options( with_loader_criteria( HasFoob, lambda cls: cls.description == value, include_aliases=True, ))
async def search_by_boss(self, boss_identifier: str, time_range: tuple[int, int]): try: boss_id = int(boss_identifier) boss_alias = '' except ValueError: boss_id = -1 boss_alias = boss_identifier async with self.__session.begin() as async_session: stmt = select(Boss).options( selectinload(Boss.records), with_loader_criteria( Record, and_(Record.date_time >= time_range[0], Record.date_time <= time_range[1]))).filter( or_(Boss.boss_id == boss_id, Boss.alias == boss_alias)) query = await async_session.stream(stmt) boss = await query.scalars().first() if boss: result = [] for record in boss.records: result.append(self._formatter(record)) return {'result': result, 'code': DBStatusCode.SEARCH_SUCCESS} else: return {'result': [], 'code': DBStatusCode.SEARCH_FAIL}
def test_joinedload_global_criteria(self, user_address_fixture): User, Address = user_address_fixture s = Session(testing.db, future=True) stmt = select(User).options( joinedload(User.addresses), with_loader_criteria(Address, Address.email_address != "email"), ) with self.sql_execution_asserter() as asserter: s.execute(stmt) asserter.assert_( CompiledSQL( "SELECT users.id, users.name, addresses_1.id AS id_1, " "addresses_1.user_id, addresses_1.email_address FROM " "users LEFT OUTER JOIN addresses AS addresses_1 " "ON users.id = addresses_1.user_id " "AND addresses_1.email_address != :email_address_1 " "ORDER BY addresses_1.id", [{"email_address_1": "email"}], ), )
def _add_filtering_criteria(execute_state): """Intercept all ORM queries. Add a with_loader_criteria option to all of them. This option applies to SELECT queries and adds a global WHERE criteria (or as appropriate ON CLAUSE criteria for join targets) to all objects of a certain class or superclass. """ # the with_loader_criteria automatically applies itself to # relationship loads as well including lazy loads. So if this is # a relationship load, assume the option was set up from the top level # query. if ( not execute_state.is_column_load and not execute_state.is_relationship_load and not execute_state.execution_options.get("include_private", False) ): execute_state.statement = execute_state.statement.options( orm.with_loader_criteria( HasPrivate, lambda cls: cls.public == true(), include_aliases=True, ) )
def test_select_joinm2m_aliased_inclaliased_criteria( self, order_item_fixture ): Order, Item = order_item_fixture i1 = aliased(Item) stmt = ( select(Order) .join(Order.items.of_type(i1)) .options( with_loader_criteria( Item, Item.description != "description", include_aliases=True, ) ) ) self.assert_compile( stmt, "SELECT orders.id, orders.user_id, orders.address_id, " "orders.description, orders.isopen FROM orders " "JOIN order_items AS order_items_1 " "ON orders.id = order_items_1.order_id " "JOIN items AS items_1 ON items_1.id = order_items_1.item_id " "AND items_1.description != :description_1", )
def with_translation_criteria(request=None, include_aliases=True): current_locale, default_locale = get_locales(request) criteria = orm.with_loader_criteria(ContentTranslation, lambda cls: cls.language_id.in_( (current_locale, default_locale)), include_aliases=include_aliases) return criteria
def test_loader_criteria(self): User, Address = self.classes("User", "Address") from sqlalchemy import Column, Integer, String class Foo: id = Column(Integer) name = Column(String) self._run_cache_key_fixture( lambda: ( with_loader_criteria(User, User.name != "somename"), with_loader_criteria(User, User.id != 5), with_loader_criteria(User, lambda cls: cls.id == 10), with_loader_criteria(Address, Address.id != 5), with_loader_criteria(Foo, lambda cls: cls.id == 10), ), compare_values=True, )
def _do_orm_execute(orm_execute_state): if ( orm_execute_state.is_select and not orm_execute_state.is_column_load ): # FIXME: functional, but potentially quite slow orm_execute_state.statement = orm_execute_state.statement.options( with_loader_criteria(Content, or_(*(Content.allowed_roles.contains(r) for r in current_user.roles), ~Content.allowed_roles.any())) )
def test_select_aliased_aliased_criteria(self, user_address_fixture): User, Address = user_address_fixture u1 = aliased(User) stmt = select(u1).options(with_loader_criteria(u1, u1.name != "name")) self.assert_compile( stmt, "SELECT users_1.id, users_1.name " "FROM users AS users_1 WHERE users_1.name != :name_1", )
def test_select_from_mapper_mapper_criteria(self, user_address_fixture): User, Address = user_address_fixture stmt = (select(sql.func.count()).select_from(User).options( with_loader_criteria(User, User.name != "name"))) self.assert_compile( stmt, "SELECT count(*) AS count_1 FROM users " "WHERE users.name != :name_1", )
def do_orm_execute(execute_state): """ensure all queries for VersionedStartEnd include criteria""" ct = current_time() + datetime.timedelta(seconds=1) execute_state.statement = execute_state.statement.options( with_loader_criteria( VersionedStartEnd, lambda cls: and_(ct > cls.start, ct < cls.end), include_aliases=True, ) )
def _do_orm_execute(orm_execute_state): from pprint import pprint if (orm_execute_state.is_select and not orm_execute_state.is_column_load and not orm_execute_state.is_relationship_load): # orm_execute_state.statement SELECT: # https://docs.sqlalchemy.org/en/14/core/selectable.html#sqlalchemy.sql.expression.Select if str(orm_execute_state.statement.whereclause).find( '.oculto') == -1: orm_execute_state.statement = orm_execute_state.statement.options( with_loader_criteria(Pika, Pika.oculto == False))
def test_select_mapper_columns_mapper_criteria(self, user_address_fixture): User, Address = user_address_fixture stmt = select(User.id, User.name).options( with_loader_criteria(User, User.name != "name")) self.assert_compile( stmt, "SELECT users.id, users.name " "FROM users WHERE users.name != :name_1", )
def _do_orm_execute_hide_archived(orm_execute_state): if (orm_execute_state.is_select and not orm_execute_state.is_column_load and not orm_execute_state.is_relationship_load and not orm_execute_state.execution_options.get( "include_archived", False)): orm_execute_state.statement = orm_execute_state.statement.options( with_loader_criteria( HasArchived, lambda cls: cls.archived == False, # noqa: E712 include_aliases=True, ))
def test_select_join_mapper_mapper_criteria(self, user_address_fixture): User, Address = user_address_fixture stmt = (select(User).join(User.addresses).options( with_loader_criteria(Address, Address.email_address != "name"))) self.assert_compile( stmt, "SELECT users.id, users.name FROM users " "JOIN addresses ON users.id = addresses.user_id " "AND addresses.email_address != :email_address_1", )
def test_criteria_post_replace(self, user_address_fixture): User, Address = user_address_fixture stmt = (select(User).select_from(User).options( with_loader_criteria(User, User.name != "name")).with_only_columns( func.count())) self.assert_compile( stmt, "SELECT count(*) AS count_1 FROM users " "WHERE users.name != :name_1", )
def test_criteria_post_replace_legacy(self, user_address_fixture): User, Address = user_address_fixture s = fixture_session() stmt = (s.query(User).select_from(User).options( with_loader_criteria(User, User.name != "name")).with_entities( func.count())) self.assert_compile( stmt, "SELECT count(*) AS count_1 FROM users " "WHERE users.name != :name_1", )
def test_select_count_subquery_global_criteria(self, user_address_fixture): User, Address = user_address_fixture stmt = select(User).subquery() stmt = (select(sql.func.count()).select_from(stmt).options( with_loader_criteria(User, User.id != 8))) self.assert_compile( stmt, "SELECT count(*) AS count_1 FROM (SELECT users.id AS id, " "users.name AS name FROM users WHERE users.id != :id_1) AS anon_1", )
def test_select_joinm2m_mapper_mapper_criteria(self, order_item_fixture): Order, Item = order_item_fixture stmt = (select(Order).join(Order.items).options( with_loader_criteria(Item, Item.description != "description"))) self.assert_compile( stmt, "SELECT orders.id, orders.user_id, orders.address_id, " "orders.description, orders.isopen FROM orders " "JOIN order_items AS order_items_1 " "ON orders.id = order_items_1.order_id " "JOIN items ON items.id = order_items_1.item_id " "AND items.description != :description_1", )
def test_select_from_aliased_inclaliased_criteria(self, user_address_fixture): User, Address = user_address_fixture u1 = aliased(User) stmt = (select(sql.func.count()).select_from(u1).options( with_loader_criteria(User, User.name != "name", include_aliases=True))) self.assert_compile( stmt, "SELECT count(*) AS count_1 FROM users AS users_1 " "WHERE users_1.name != :name_1", )
def test_select_join_aliased_inclaliased_criteria(self, user_address_fixture): User, Address = user_address_fixture a1 = aliased(Address) stmt = (select(User).join(User.addresses.of_type(a1)).options( with_loader_criteria( Address, Address.email_address != "name", include_aliases=True, ))) self.assert_compile( stmt, "SELECT users.id, users.name FROM users " "JOIN addresses AS addresses_1 ON users.id = addresses_1.user_id " "AND addresses_1.email_address != :email_address_1", )
def test_select_joinedload_mapper_mapper_criteria(self, user_address_fixture): User, Address = user_address_fixture stmt = select(User).options( joinedload(User.addresses), with_loader_criteria(Address, Address.email_address != "name"), ) self.assert_compile( stmt, "SELECT users.id, users.name, addresses_1.id AS id_1, " "addresses_1.user_id, addresses_1.email_address " "FROM users LEFT OUTER JOIN addresses AS addresses_1 " "ON users.id = addresses_1.user_id " "AND addresses_1.email_address != :email_address_1 " "ORDER BY addresses_1.id", )
def test_query_count_global_criteria(self, user_address_fixture): User, Address = user_address_fixture s = Session(testing.db) q = s.query(User).options(with_loader_criteria(User, User.id != 8)) with self.sql_execution_asserter() as asserter: q.count() asserter.assert_( CompiledSQL( "SELECT count(*) AS count_1 FROM (SELECT " "users.id AS users_id, users.name AS users_name " "FROM users WHERE users.id != :id_1) AS anon_1", [{"id_1": 8}], ), )
def test_loader_criteria(self, pickle_it, use_mixin): """test #8109""" users, addresses = (self.tables.users, self.tables.addresses) AddressCls = AddressWMixin if use_mixin else Address self.mapper_registry.map_imperatively( User, users, properties={"addresses": relationship(AddressCls)}, ) self.mapper_registry.map_imperatively(AddressCls, addresses) with fixture_session(expire_on_commit=False) as sess: u1 = User(name="ed") u1.addresses = [ AddressCls(email_address="*****@*****.**"), AddressCls(email_address="*****@*****.**"), ] sess.add(u1) sess.commit() with fixture_session(expire_on_commit=False) as sess: # note that non-lambda is not picklable right now as # SQL expressions usually can't be pickled. opt = with_loader_criteria( Mixin if use_mixin else Address, no_ed_foo, include_aliases=True, ) u1 = sess.query(User).options(opt).first() if pickle_it: u1 = pickle.loads(pickle.dumps(u1)) sess.close() sess.add(u1) eq_([ad.email_address for ad in u1.addresses], ["*****@*****.**"])
async def search_by_member(self, member_identifier: str, time_range: tuple[int, int]): async with self.__session.begin() as async_session: stmt = select(Member).options( selectinload(Member.records), with_loader_criteria( Record, and_(Record.date_time >= time_range[0], Record.date_time <= time_range[1]))).filter( or_(Member.member_id == member_identifier, Member.alias == member_identifier)) query = await async_session.stream(stmt) member = await query.scalars().first() if member: result = [] for record in member.records: result.append(self._formatter(record)) return {'result': result, 'code': DBStatusCode.SEARCH_SUCCESS} else: return {'result': [], 'code': DBStatusCode.SEARCH_FAIL}
def go(param): return with_loader_criteria(Foo, lambda cls: cls.id == param)
def temporal_range(range_lower, range_upper): return with_loader_criteria( HasTemporal, lambda cls: cls.timestamp.between(range_lower, range_upper), include_aliases=True, )