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,
)
