def get_courses() -> JSONResponse[t.Sequence[t.Mapping[str, t.Any]]]: """Return all :class:`.models.Course` objects the current user is a member of. .. :quickref: Course; Get all courses the current user is enrolled in. :returns: A response containing the JSON serialized courses :param str extended: If set to ``true``, ``1`` or the empty string all the assignments and group sets for each course are also included under the key ``assignments`` and ``group_sets`` respectively. :>jsonarr str role: The name of the role the current user has in this course. :>jsonarr ``**rest``: JSON serialization of :py:class:`psef.models.Course`. :raises PermissionException: If there is no logged in user. (NOT_LOGGED_IN) """ def _get_rest(course: models.Course) -> t.Mapping[str, t.Any]: if helpers.extended_requested(): snippets: t.Sequence[models.CourseSnippet] = [] if (current_user.has_permission(GPerm.can_use_snippets) and current_user.has_permission( CPerm.can_view_course_snippets, course_id=course.id)): snippets = course.snippets return { 'assignments': course.get_all_visible_assignments(), 'group_sets': course.group_sets, 'snippets': snippets, **course.__to_json__(), } return course.__to_json__() extra_loads: t.Optional[t.List[t.Any]] = None if helpers.extended_requested(): extra_loads = [ selectinload(models.Course.assignments), selectinload(models.Course.snippets), selectinload(models.Course.group_sets) ] # We don't use `helpers.get_or_404` here as preloading doesn't seem to work # when we do. user = models.User.query.filter_by(id=current_user.id).options([ selectinload(models.User.courses, ).selectinload( models.CourseRole._permissions, # pylint: disable=protected-access ), ]).first() assert user is not None return jsonify([{ 'role': user.courses[c.id].name, **_get_rest(c), } for c in helpers.get_in_or_error( models.Course, t.cast(models.DbColumn[int], models.Course.id), [cr.course_id for cr in user.courses.values()], extra_loads, )])
def events(): query = db.query(model.Event)\ .options( selectinload(model.Event.teams_part)\ .joinedload(model.EventTeam.team, innerjoin=True), selectinload(model.Event.judgings)\ .joinedload(model.EventJudging.judge, innerjoin=True) ) params = flask.request.values query = filter_events(query, params) if "limit" in params: query = query.limit(params.get("limit", type=int)) ret = query.all() return [{ "id": e.id, "block": { "id": e.block_id, "name": get_block(e).name }, "ts_sched": e.ts_sched, "arena": e.arena, "teams": list(map(jsonify, e.teams)), "judges": list(map(jsonify, e.judges)) } for e in ret]
def setup_query(self, types): query = None class_ = None if self.get_argument('relationships', 'true').lower() == 'false': multi_loader = noload else: multi_loader = selectinload if types == 'rooms': query = select(Room).options(selectinload(Room.floor), multi_loader(Room.items), selectinload(Room.sample)) class_ = Room elif types == 'floors': query = select(Floor).options(multi_loader(Floor.rooms), multi_loader(Floor.samples), multi_loader(Floor.topics)) class_ = Floor elif types == 'items': query = select(Item).options(selectinload(Item.room)) class_ = Item elif types == 'floor-topics': query = select(FloorTopic).options(selectinload(FloorTopic.group), selectinload(FloorTopic.floor)) class_ = FloorTopic elif types == 'groups': query = select(Group) class_ = Group return (query, class_)
def update_query_for_extended_jsonify( cls: t.Type['Work'], query: _MyQuery['Work']) -> _MyQuery['Work']: """Update the given query to load all attributes needed for an extended jsonify eagerly. :param query: The query to update. :returns: The updated query, which now loads all attributes needed for an extended jsonify eagerly. """ return query.options( selectinload(cls.selected_items, ), # We want to load all users directly. We do this by loading the # user, which might be a group. For such groups we also load all # users. The users in this group will never be a group, so the # last `selectinload` here might be seen as strange. However, # during the serialization of a group we access `User.group`, which # checks if a user is really not a group. To prevent these last # queries the last `selectinload` is needed here. selectinload(cls.user, ).selectinload( user_models.User.group, ).selectinload( group_models.Group.members, ).selectinload( user_models.User.group, ), undefer(cls.comment), selectinload(cls.comment_author), )
async def get_certificate(session, account_id=None, certificate_id=None, certificate=None): if account_id and certificate_id: statement = (select(Certificate).options( selectinload(Order.certificate), selectinload(Order.account), selectinload(Certificate.order).selectinload(Order.account), ).join(Order, Certificate.order_id == Order.order_id).join( Account, Order.account_id == Account.account_id).filter( (account_id == Account.account_id) & (Certificate.certificate_id == certificate_id))) elif certificate: statement = (select(Certificate).filter( Certificate.cert == certificate).options( selectinload(Certificate.order).selectinload( Order.account)).join( Order, Certificate.order_id == Order.order_id).join( Account, Order.account_id == Account.account_id)) else: raise ValueError( "Either kid and certificate_id OR certificate should be specified" ) try: result = (await session.execute(statement)).first() except DBAPIError: # certificate_id is not a valid UUID raise web.HTTPNotFound return result[0] if result else None
def does_game_exist(session, user_id, channel_id): game = session.query(Game). \ options(selectinload(Game.pits)). \ options(selectinload(Game.bats)). \ filter(and_(Game.user_id == user_id, Game.channel_id == channel_id)). \ one_or_none() return game is not None
def go(): # NOTE: subqueryload is broken for this case, first found # when cartesian product detection was added. with expect_deprecated_20(with_polymorphic_dep): for a in (session.query(A).with_polymorphic([B, C]).options( selectinload(B.related), selectinload(C.related))): eq_(a.related, [d])
def get_rubric(submission_id: int) -> JSONResponse[t.Mapping[str, t.Any]]: """Return full rubric of the :class:`.models.Assignment` of the given submission (:class:`.models.Work`). .. :quickref: Submission; Get a rubric and its selected items. :param int submission_id: The id of the submission :returns: A response containing the JSON serialized rubric as described in :py:meth:`.Work.__rubric_to_json__`. :raises APIException: If the submission with the given id does not exist. (OBJECT_ID_NOT_FOUND) :raises PermissionException: If there is no logged in user. (NOT_LOGGED_IN) :raises PermissionException: If the user can not see the assignment of the given submission. (INCORRECT_PERMISSION) """ work = helpers.filter_single_or_404( models.Work, models.Work.id == submission_id, ~models.Work.deleted, options=[ selectinload(models.Work.assignment, ).selectinload( models.Assignment.rubric_rows, ).selectinload( models.RubricRow.items, ), selectinload(models.Work.selected_items), ]) auth.WorkPermissions(work).ensure_may_see() return jsonify(work.__rubric_to_json__())
async def delete( primary_key: int, db: AsyncSession = Depends(get_db), ): # Get the panel statement = ( select(Panel) .where(Panel.id == primary_key) .options(selectinload(Panel.reactions)) .options(selectinload(Panel.reactions, Reaction.category)) ) result = await db.execute(statement) panel = result.scalars().first() # Delete it if it exists if panel is not None: # Delete the message channel = await get_channel(panel.channel_id) if channel is not None: message = await get_message(channel, panel.message_id) if message is not None: await message.delete() # Delete all the reactions for reaction in panel.reactions: await db.delete(reaction) await db.delete(panel) await db.commit() return {"success": True}
async def read( primary_key: int, db: AsyncSession = Depends(get_db), ): # Get the panel and the reactions statement = ( select(Panel) .where(Panel.id == primary_key) .options(selectinload(Panel.reactions)) .options(selectinload(Panel.reactions, Reaction.category)) ) result = await db.execute(statement) panel = result.scalars().first() if panel is None: raise HTTPException(status_code=404, detail="not found") # Get the channel name from the id channel = await get_channel(panel.channel_id) channel_name = channel.name if channel is not None else "<DNE>" return SpecificPanel( id=panel.id, title=panel.title, content=panel.content, channel=channel_name, reactions=panel.reactions, )
def view_movies(self, start, number, director=None, actors=None, genres=None): session = self.session_factory() query = session.query(Movie) if director is not None and director != "": query = query.join(Director).filter(Director.full_name == director) for actor_name in actors: a = aliased(Actor) m = aliased(movie_actor) query = query.join(m, Movie.id == m.c.movie_id).join( a, m.c.actor_id == a.id) query = query.filter(a.full_name == actor_name) for genre_name in genres: g = aliased(Genre) mg = aliased(movie_genre) query = query.join(mg, Movie.id == mg.c.movie_id).join( g, mg.c.genre_id == g.id) query = query.filter(g.name == genre_name) count = query.count() # paginate query = query.options(selectinload(Movie.actors), selectinload(Movie.director)) results = query.limit(number).offset(start).all() return results, start + number < count
def utterances(self, session: Session = None) -> sqlalchemy.orm.Query: """ Get all utterances in the corpus Parameters ---------- session: sqlalchemy.orm.Session, optional Session to use in querying Returns ------- sqlalchemy.orm.Query Utterance query """ close = False if session is None: session = Session(self.db_engine) close = True utterances = session.query(Utterance).options( joinedload(Utterance.file, innerjoin=True), joinedload(Utterance.speaker, innerjoin=True), selectinload(Utterance.phone_intervals), selectinload(Utterance.word_intervals), selectinload(Utterance.reference_phone_intervals), ) if close: session.close() return utterances
async def get_challenge(session, account_id, challenge_id): statement = (select(Challenge).options( selectinload(Challenge.authorization).options( selectinload(Authorization.challenges), selectinload(Authorization.identifier).options( selectinload(Identifier.authorization), selectinload(Identifier.order).selectinload( Order.identifiers).joinedload( Identifier.authorization), ), )).join( Authorization, Challenge.authorization_id == Authorization.authorization_id, ).join( Identifier, Authorization.identifier_id == Identifier.identifier_id).join( Order, Identifier.order_id == Order.order_id).join( Account, Order.account_id == Account.account_id).filter( (account_id == Account.account_id) & (Challenge.challenge_id == challenge_id))) try: result = (await session.execute(statement)).first() except DBAPIError: # challenge_id is not a valid UUID raise web.HTTPNotFound return result[0] if result else None
def speakers(self, session: Session = None) -> sqlalchemy.orm.Query: """ Get all speakers in the corpus Parameters ---------- session: sqlalchemy.orm.Session, optional Session to use in querying Returns ------- sqlalchemy.orm.Query Speaker query """ close = False if session is None: session = self.session() close = True speakers = session.query(Speaker).options( selectinload(Speaker.utterances), selectinload(Speaker.files).joinedload(SpeakerOrdering.file, innerjoin=True), joinedload(Speaker.dictionary), ) if close: session.close() return speakers
def files(self, session: Session = None) -> sqlalchemy.orm.Query: """ Get all files in the corpus Parameters ---------- session: sqlalchemy.orm.Session, optional Session to use in querying Returns ------- sqlalchemy.orm.Query File query """ close = False if session is None: session = self.session() close = True files = session.query(File).options( selectinload(File.utterances), selectinload(File.speakers).joinedload(SpeakerOrdering.speaker, innerjoin=True), joinedload(File.sound_file), joinedload(File.text_file), ) if close: session.close() return files
def search_events(self, q, user, page, category_id, admin_override_enabled): filters = [Event.title_matches(q), ~Event.is_deleted] if category_id is not None: filters.append(Event.category_chain_overlaps(category_id)) query = (Event.query.filter(*filters).options( load_only('id', 'category_id', 'access_key', 'protection_mode'), undefer(Event.effective_protection_mode), _apply_acl_entry_strategy(selectinload(Event.acl_entries), EventPrincipal))) objs, pagenav = self._paginate(query, page, Event.id, user, admin_override_enabled) query = (Event.query.filter(Event.id.in_(e.id for e in objs)).options( undefer(Event.detailed_category_chain), selectinload(Event.person_links).joinedload('person').joinedload( 'user').load_only('is_system'), joinedload(Event.own_venue), joinedload(Event.own_room).options(raiseload('*'), joinedload('location')), )) events_by_id = {e.id: e for e in query} events = [events_by_id[e.id] for e in objs] res = HTMLStrippingEventSchema(many=True).dump(events) return pagenav, EventResultSchema(many=True).load(res)
def test_orm_query_using_with_entities(self): """test issue #6503""" User, Address, Keyword, Order, Item = self.classes( "User", "Address", "Keyword", "Order", "Item") self._run_cache_key_fixture( lambda: stmt_20( fixture_session().query(User).join(User.addresses). with_entities(Address.id), # fixture_session().query(Address.id).join(User.addresses), # fixture_session().query(User).options( selectinload(User.addresses)).with_entities(User.id), # fixture_session().query(User).options( selectinload(User.addresses)), # fixture_session().query(User).with_entities(User.id), # # here, propagate_attr->orm is Address, entity is Address.id, # but the join() + with_entities() will log a # _MemoizedSelectEntities to differentiate fixture_session().query(Address, Order).join( Address.dingaling).with_entities(Address.id), # # same, propagate_attr->orm is Address, entity is Address.id, # but the join() + with_entities() will log a # _MemoizedSelectEntities to differentiate fixture_session().query(Address, User).join( Address.dingaling).with_entities(Address.id), ), compare_values=True, )
def list_groups_and_maps_for_stops_in_route(route_pk): """ This function is used to get the service maps for a route. It returns a list of tuples (service map group, service map) for each service map group having use_for_stops_in_route equal True. :param route_pk: the route's PK :return: the list described above """ session = dbconnection.get_session() query = (session.query(models.ServiceMapGroup, models.ServiceMap).join( models.System, models.System.pk == models.ServiceMapGroup.system_pk).join( models.Route, models.Route.system_pk == models.System.pk).outerjoin( models.ServiceMap, sql.and_( models.ServiceMap.route_pk == models.Route.pk, models.ServiceMap.group_pk == models.ServiceMapGroup.pk, ), ).filter(models.ServiceMapGroup.use_for_stops_in_route).filter( models.Route.pk == route_pk).options( selectinload(models.ServiceMap.vertices)).options( selectinload(models.ServiceMap.vertices, models.ServiceMapVertex.stop))) return [(group, map_) for (group, map_) in query]
def test_selectinload_w_joinedload_after(self, calling_style): """test has been enhanced to also test #7224""" A, B, C = self.classes("A", "B", "C") s = Session(testing.db) partitioned_b = self.partitioned_b if calling_style == "ac_attribute": opt = selectinload(A.partitioned_bs).joinedload(partitioned_b.cs) elif calling_style == "ac_attr_w_of_type": # this would have been a workaround for people who encountered # #7224. The exception that was raised for "ac_attribute" actually # suggested to use of_type() so we can assume this pattern is # probably being used opt = selectinload( A.partitioned_bs.of_type(partitioned_b) ).joinedload(partitioned_b.cs) else: assert False def go(): for a1 in s.query(A).options(opt): for b in a1.partitioned_bs: eq_(len(b.cs), 2) self.assert_sql_count(testing.db, go, 2)
def scan_database(base, session, conn): """ Синхронизирует одну базу данных целиком. """ original_db = original_models.OriginalDatabase.fetch_from_metadata(conn) if original_db.last_update == base.last_update: logging.info(f"В оригинальной БД не было изменений, выходим") return base = session.query(Database).options( selectinload(Database.scripts), selectinload(Database.tables)).filter(Database.id == base.id).one() logging.debug(f"Достаём оригиналы объектов БД {base.name}") original_proc_data_set = original_models.OriginalProcedure.get_all(conn) original_views_data_set = original_models.OriginalView.get_all(conn) original_tabfunc_data_set = original_models.OriginalTableFunction.get_all( conn) original_sfunc_data_set = original_models.OriginalScalarFunction.get_all( conn) original_tables_data_set = original_models.OriginalTable.get_all(conn) logging.debug(f"Синхронизируем хранимые процедуры БД {base.name}") sync_subordinate_members(original_proc_data_set, DBStoredProcedure, base.procedures, session, base) logging.debug(f"Синхронизируем представления БД {base.name}") sync_subordinate_members(original_views_data_set, DBView, base.views, session, base) logging.debug(f"Синхронизируем табличные функции БД {base.name}") sync_subordinate_members(original_tabfunc_data_set, DBTableFunction, base.table_functions, session, base) logging.debug(f"Синхронизируем скалярные функции БД {base.name}") sync_subordinate_members(original_sfunc_data_set, DBScalarFunction, base.scalar_functions, session, base) logging.debug(f"Синхронизируем таблицы БД {base.name}") sync_subordinate_members(original_tables_data_set, DBTable, base.tables, session, base) persistent_tables = { table.name: table for table in session if isinstance(table, DBTable) } logging.debug( f"Сопоставляем триггеры для оставшихся таблиц БД {base.name}") for table_name in persistent_tables: table = persistent_tables[table_name] logging.debug( f"Собираем триггеры для таблицы {table_name} в БД {base.name}") original_triggers_data_set = original_models.OriginalTrigger.get_triggers_for_table( conn, table.database_object_id) sync_subordinate_members(original_triggers_data_set, DBTrigger, table.triggers, session, table) # обновляем метаданные самой базы base.update_from(original_db) logging.info(f"Обработка базы {base.name} завершена")
def test_load(self): A, B, ASub, C = self.classes("A", "B", "ASub", "C") s = Session() q = (s.query(A).order_by(A.id).options(selectinload(ASub.cs), selectinload(A.bs))) self._assert_all_selectin(q)
def _route_get_event(event_id: int): with Session() as session: item: Event = (session.query(Event).options( selectinload(Event.artists), selectinload(Event.location)).get_or_404(event_id)) return camelize( item.to_dict( mapper=event_mapper(load_artist=True, load_location=True)))
def company_queries_with_all_relations(): return Company.query.options( selectinload(Company.employments).selectinload( Employment.user).selectinload(User.activities).options( selectinload(Activity.mission).selectinload( Mission.expenditures)).options( selectinload(Activity.revisions))).options( selectinload(Company.vehicles))
def transactions(args): # For performance reasons, eager load some extra data we will use later for CSV's. return _filter( db.session.query(Transaction).join(Activity).options( orm.selectinload(Transaction.recipient_country_percentages), orm.selectinload(Transaction.recipient_region_percentages), orm.selectinload(Transaction.sector_percentages), ), args)
def getBuilds(self, tenant=None, project=None, pipeline=None, change=None, branch=None, patchset=None, ref=None, newrev=None, event_id=None, uuid=None, job_name=None, voting=None, node_name=None, result=None, provides=None, final=None, held=None, limit=50, offset=0): build_table = self.connection.zuul_build_table buildset_table = self.connection.zuul_buildset_table provides_table = self.connection.zuul_provides_table # contains_eager allows us to perform eager loading on the # buildset *and* use that table in filters (unlike # joinedload). q = self.session().query(self.connection.buildModel).\ join(self.connection.buildSetModel).\ outerjoin(self.connection.providesModel).\ options(orm.contains_eager(self.connection.buildModel.buildset), orm.selectinload(self.connection.buildModel.provides), orm.selectinload(self.connection.buildModel.artifacts)) # If the query planner isn't able to reduce either the number # of rows returned by the buildset or build tables, then it # tends to produce a very slow query. This hint produces # better results, but only in those cases. When we can narrow # things down with indexes, it's better to omit the hint. # job_name is a tricky one. It is indexed, but if there are a # lot of rows, it is better to include the hint, but if there # are few, it is better to not include it. We include the hint # regardless of whether job_name is specified (optimizing for # the more common case). if not (project or change or uuid): q = q.with_hint(build_table, 'USE INDEX (PRIMARY)', 'mysql') q = self.listFilter(q, buildset_table.c.tenant, tenant) q = self.listFilter(q, buildset_table.c.project, project) q = self.listFilter(q, buildset_table.c.pipeline, pipeline) q = self.listFilter(q, buildset_table.c.change, change) q = self.listFilter(q, buildset_table.c.branch, branch) q = self.listFilter(q, buildset_table.c.patchset, patchset) q = self.listFilter(q, buildset_table.c.ref, ref) q = self.listFilter(q, buildset_table.c.newrev, newrev) q = self.listFilter(q, buildset_table.c.event_id, event_id) q = self.listFilter(q, build_table.c.uuid, uuid) q = self.listFilter(q, build_table.c.job_name, job_name) q = self.listFilter(q, build_table.c.voting, voting) q = self.listFilter(q, build_table.c.node_name, node_name) q = self.listFilter(q, build_table.c.result, result) q = self.listFilter(q, build_table.c.final, final) q = self.listFilter(q, provides_table.c.name, provides) q = self.listFilter(q, build_table.c.held, held) q = q.order_by(build_table.c.id.desc()).\ limit(limit).\ offset(offset) try: return q.all() except sqlalchemy.orm.exc.NoResultFound: return []
async def storylet_view(request: Request, area_id: int, storylet_id: int): with get_session() as session: area = session.query(Area).get(area_id) storylet = session.query(Storylet)\ .options( selectinload(Storylet.areas), selectinload(Storylet.branches).selectinload(Branch.observations) ).get(storylet_id) return {"area": area, "storylet": storylet}
async def patch(cls: T, session: AsyncSession, _id: int, new_data: dict) -> Optional[Row]: # Патч по сути только для курьеров update_data = {} for key, value in new_data.items(): if value is None: continue if key == "working_hours": update_data[ "working_hours_timedeltas"] = get_timedeltas_from_string( value) elif key == "delivery_hours": update_data[ "delivery_hours_timedeltas"] = get_timedeltas_from_string( value) update_data[key] = value response = await session.execute( update(cls).where(cls.id == _id).options(selectinload( cls.orders)).values(update_data).returning(cls)) if not response.fetchall(): return None new_object = (await session.execute( select(cls).where(cls.id == _id).options(selectinload(cls.orders)) )).first()[0] new_object_orders = get_best_orders(new_object.orders, capacity=new_object.get_capacity()) if new_object_orders: new_orders = [] orders_sum_weight = 0 for order in new_object_orders: for order_timedelta in order.delivery_hours_timedeltas: for courier_timedelta in new_object.working_hours_timedeltas: if (check_courier_can_delivery_by_time( order_timedelta=order_timedelta, courier_timedelta=courier_timedelta, ) and (round(orders_sum_weight + order.weight, 2) <= new_object.get_capacity()) and (order not in new_orders) and (order.region in new_object.regions)): orders_sum_weight = round( orders_sum_weight + order.weight, 2) new_orders.append(order) for order in new_object.orders: if order not in new_orders: order.assign_time = None order.courier_id = None await session.commit() return new_object
def get_user_posts(user_id, *, category_id=None, limit=10, offset=0): user_posts = Post.query.filter(Post.owner_id == user_id) if category_id: user_posts = user_posts.filter(Post.category_id == category_id) limit = limit if 1 <= limit <= 50 else 10 offset = offset if offset >= 0 else 0 return (user_posts.options(selectinload(Post.comments)).options( selectinload(Post.owner)).options(selectinload( Post.categories)).offset(offset).limit(limit))
def transactions_by_sector(args): return _filter( db.session.query(Transaction, SectorPercentage).join( Activity, Activity.iati_identifier == Transaction.activity_id).join(SectorPercentage).options( orm.selectinload(Transaction.recipient_country_percentages), orm.selectinload(Transaction.recipient_region_percentages), orm.selectinload(Transaction.sector_percentages), ), args)
def list_team_games(self, team_name): try: dataframe = self.session.query(Game).options(selectinload(Game.team1), selectinload(Game.team2)). \ filter(and_(Game.best_of == 3, or_(Game.team1.has(Team.name == team_name), Game.team2.has(Team.name == team_name)))).all() except: self.session.rollback() raise return dataframe
def test_load(self): A, B, ASub, C = self.classes("A", "B", "ASub", "C") s = Session() q = s.query(A).order_by(A.id).options( selectinload(ASub.cs), selectinload(A.bs) ) self._assert_all_selectin(q)
def get_avatar(request): """ Returns the current User object """ logger.info("# Retrieving avatar #") login = request.unauthenticated_userid logger.info(u" + Login : %s" % login) result = None if login is not None: logger.info(" + Returning the user") query = request.dbsession.query(User) query = query.join(Login) query = query.options(load_only("firstname", "lastname")) query = query.options( contains_eager(User.login).load_only('login').selectinload( Login._groups).load_only('name'), selectinload(User.companies).load_only('id', 'active'), ) query = query.filter(Login.login == login) result = query.first() if result is None: forget(request) raise HTTPFound("/") else: logger.info(" + No user found") logger.debug(u"-> End of the avatar collection") return result
def test_selectinload(self): A, B = self.classes("A", "B") sess = Session() with self.sql_execution_asserter() as asserter: # note this is many-to-one. use_get is unconditionally turned # off for relationship to aliased class for now. a1 = sess.query(A).options(selectinload(A.b)).first() eq_(a1.b, B(id=1)) asserter.assert_( CompiledSQL( "SELECT a.id AS a_id, a.b_id AS a_b_id " "FROM a LIMIT :param_1", [{"param_1": 1}], ), CompiledSQL( "SELECT a_1.id AS a_1_id, b.id AS b_id FROM a AS a_1 " "JOIN (b JOIN d ON d.b_id = b.id JOIN c ON c.id = d.c_id) " "ON a_1.b_id = b.id WHERE a_1.id " "IN ([EXPANDING_primary_keys]) ORDER BY a_1.id", [{"primary_keys": [1]}], ), )
def test_load_company_plus_employees(self): s = Session() q = s.query(Company).options( selectinload(Company.employees). selectin_polymorphic([Engineer, Manager]) ).order_by(Company.company_id) result = self.assert_sql_execution( testing.db, q.all, CompiledSQL( "SELECT companies.company_id AS companies_company_id, " "companies.name AS companies_name FROM companies " "ORDER BY companies.company_id", {} ), CompiledSQL( "SELECT companies_1.company_id AS companies_1_company_id, " "people.person_id AS people_person_id, " "people.company_id AS people_company_id, " "people.name AS people_name, people.type AS people_type " "FROM companies AS companies_1 JOIN people " "ON companies_1.company_id = people.company_id " "WHERE companies_1.company_id IN ([EXPANDING_primary_keys]) " "ORDER BY companies_1.company_id, people.person_id", {"primary_keys": [1, 2]} ), AllOf( CompiledSQL( "SELECT managers.person_id AS managers_person_id, " "people.person_id AS people_person_id, " "people.company_id AS people_company_id, " "people.name AS people_name, people.type AS people_type, " "managers.status AS managers_status, " "managers.manager_name AS managers_manager_name " "FROM people JOIN managers " "ON people.person_id = managers.person_id " "WHERE people.person_id IN ([EXPANDING_primary_keys]) " "ORDER BY people.person_id", {"primary_keys": [3, 4]} ), CompiledSQL( "SELECT engineers.person_id AS engineers_person_id, " "people.person_id AS people_person_id, " "people.company_id AS people_company_id, " "people.name AS people_name, people.type AS people_type, " "engineers.status AS engineers_status, " "engineers.engineer_name AS engineers_engineer_name, " "engineers.primary_language AS engineers_primary_language " "FROM people JOIN engineers " "ON people.person_id = engineers.person_id " "WHERE people.person_id IN ([EXPANDING_primary_keys]) " "ORDER BY people.person_id", {"primary_keys": [1, 2, 5]} ) ) ) eq_(result, [self.c1, self.c2])
def getBuilds(self, tenant=None, project=None, pipeline=None, change=None, branch=None, patchset=None, ref=None, newrev=None, uuid=None, job_name=None, voting=None, node_name=None, result=None, provides=None, limit=50, offset=0): build_table = self.connection.zuul_build_table buildset_table = self.connection.zuul_buildset_table provides_table = self.connection.zuul_provides_table # contains_eager allows us to perform eager loading on the # buildset *and* use that table in filters (unlike # joinedload). q = self.session().query(self.connection.buildModel).\ join(self.connection.buildSetModel).\ outerjoin(self.connection.providesModel).\ options(orm.contains_eager(self.connection.buildModel.buildset), orm.selectinload(self.connection.buildModel.provides), orm.selectinload(self.connection.buildModel.artifacts)).\ with_hint(build_table, 'USE INDEX (PRIMARY)', 'mysql') q = self.listFilter(q, buildset_table.c.tenant, tenant) q = self.listFilter(q, buildset_table.c.project, project) q = self.listFilter(q, buildset_table.c.pipeline, pipeline) q = self.listFilter(q, buildset_table.c.change, change) q = self.listFilter(q, buildset_table.c.branch, branch) q = self.listFilter(q, buildset_table.c.patchset, patchset) q = self.listFilter(q, buildset_table.c.ref, ref) q = self.listFilter(q, buildset_table.c.newrev, newrev) q = self.listFilter(q, build_table.c.uuid, uuid) q = self.listFilter(q, build_table.c.job_name, job_name) q = self.listFilter(q, build_table.c.voting, voting) q = self.listFilter(q, build_table.c.node_name, node_name) q = self.listFilter(q, build_table.c.result, result) q = self.listFilter(q, provides_table.c.name, provides) q = q.order_by(build_table.c.id.desc()).\ limit(limit).\ offset(offset) try: return q.all() except sqlalchemy.orm.exc.NoResultFound: return []
def test_round_trip_results(self): A, B, C = self.classes("A", "B", "C") sess = Session() q = sess.query(A).options(selectinload(A.bs).selectinload(B.cs)) @profiling.function_call_count() def go(): for i in range(100): obj = q.all() list(obj) sess.close() go()
def test_selectinload_query(self): session = ShardedSession( shards={"test": testing.db}, shard_chooser=lambda *args: "test", id_chooser=lambda *args: None, query_chooser=lambda *args: ["test"], ) Book, Page = self.classes("Book", "Page") book = Book() book.pages.append(Page()) session.add(book) session.commit() result = session.query(Book).options(selectinload("pages")).all() eq_(result, [book])
def query(self): business_type_id = self._get_training_business_type() query = self.dbsession.query( distinct(Business.id), Business ).filter( Business.business_type_id == business_type_id ) query = query.options( joinedload(Business.project).load_only('id'). selectinload(Project.company).load_only( Company.id, Company.name ), selectinload(Business.tasks). selectinload(Project.customers).load_only( Customer.id, Customer.label ) ) return query
def get_room_blockings(timeframe=None, created_by=None, in_rooms_owned_by=None): query = (Blocking.query .join(Blocking.blocked_rooms) .join(BlockedRoom.room) .options(contains_eager('blocked_rooms').contains_eager('room'), selectinload('_allowed'))) criteria = [] if timeframe == 'recent': criteria.append(Blocking.end_date >= date.today()) elif timeframe == 'year': criteria.extend([Blocking.start_date <= date(date.today().year, 12, 31), Blocking.end_date >= date(date.today().year, 1, 1)]) if created_by: criteria.append(Blocking.created_by_user == created_by) if in_rooms_owned_by: criteria.append(BlockedRoom.room_id.in_(get_managed_room_ids(in_rooms_owned_by))) query = query.filter(db.and_(*criteria)) return query.all()
def go(): for a1 in s.query(A).options( noload("*"), selectinload(A.partitioned_bs) ): for b in a1.partitioned_bs: eq_(b.cs, [])
def go(): for a1 in s.query(A).options( selectinload(A.partitioned_bs).joinedload("cs") ): for b in a1.partitioned_bs: eq_(len(b.cs), 2)