Python sqlalchemy.orm.joinedload() Examples

def find_user(self, case_insensitive=False, **kwargs):
        from sqlalchemy import func as alchemyFn

        query = self.user_model.query
        if config_value("JOIN_USER_ROLES") and hasattr(self.user_model, "roles"):
            from sqlalchemy.orm import joinedload

            query = query.options(joinedload("roles"))

        if case_insensitive:
            # While it is of course possible to pass in multiple keys to filter on
            # that isn't the normal use case. If caller asks for case_insensitive
            # AND gives multiple keys - throw an error.
            if len(kwargs) > 1:
                raise ValueError("Case insensitive option only supports single key")
            attr, identifier = kwargs.popitem()
            subquery = alchemyFn.lower(
                getattr(self.user_model, attr)
            ) == alchemyFn.lower(identifier)
            return query.filter(subquery).first()
        else:
            return query.filter_by(**kwargs).first() 

def _share_snapshot_instance_get_with_filters(context, instance_ids=None,
                                              snapshot_ids=None, statuses=None,
                                              share_instance_ids=None,
                                              session=None):

    query = model_query(context, models.ShareSnapshotInstance, session=session,
                        read_deleted="no")

    if instance_ids is not None:
        query = query.filter(
            models.ShareSnapshotInstance.id.in_(instance_ids))

    if snapshot_ids is not None:
        query = query.filter(
            models.ShareSnapshotInstance.snapshot_id.in_(snapshot_ids))

    if share_instance_ids is not None:
        query = query.filter(models.ShareSnapshotInstance.share_instance_id
                             .in_(share_instance_ids))

    if statuses is not None:
        query = query.filter(models.ShareSnapshotInstance.status.in_(statuses))

    query = query.options(joinedload('share_group_snapshot'))
    return query 

def get_name_for_netloc_db(db_sess, netloc):

	if netloc in FEED_LOOKUP_CACHE:
		return FEED_LOOKUP_CACHE[netloc]

	row = db_sess.query(db.RssFeedUrlMapper) \
		.filter(db.RssFeedUrlMapper.feed_netloc == netloc) \
		.options(joinedload('feed_entry')) \
		.all()

	if not row:
		return False

	if len(row) > 1:
		print("ERROR: Multiple solutions for netloc %s?" % netloc)

	feedname = row[0].feed_entry.feed_name
	if feedname:
		FEED_LOOKUP_CACHE[netloc] = feedname
		return feedname
	else:
		return False 

def list_machines(self, locked=False):
        """Lists virtual machines.
        @return: list of virtual machines
        """
        session = self.Session()
        try:
            if locked:
                machines = session.query(Machine).options(joinedload("tags")).filter_by(locked=True).all()
            else:
                machines = session.query(Machine).options(joinedload("tags")).all()
            return machines
        except SQLAlchemyError as e:
            log.debug("Database error listing machines: {0}".format(e))
            return []
        finally:
            session.close() 

def renderFeedsTable(page=1):

	feeds = g.session.query(db.RssFeedPost)       \
		.order_by(desc(db.RssFeedPost.published))


	feeds = feeds.options(joinedload('tag_rel'))
	feeds = feeds.options(joinedload('author_rel'))



	if feeds is None:
		flash('No feeds? Something is /probably/ broken!.')
		return redirect(url_for('renderFeedsTable'))

	feed_entries = paginate(feeds, page, app.config['FEED_ITEMS_PER_PAGE'])

	return render_template('rss-pages/feeds.html',
						   subheader = "",
						   sequence_item   = feed_entries,
						   page            = page
						   ) 

def view_task(self, task_id, details=False):
        """Retrieve information on a task.
        @param task_id: ID of the task to query.
        @return: details on the task.
        """
        session = self.Session()
        try:
            if details:
                task = session.query(Task).options(joinedload("guest"), joinedload("errors"), joinedload("tags")).get(task_id)
            else:
                task = session.query(Task).get(task_id)
        except SQLAlchemyError as e:
            log.debug("Database error viewing task: {0}".format(e))
            return None
        else:
            if task:
                session.expunge(task)
            return task
        finally:
            session.close() 

def _host_get_by_uuid(context, host_uuid, segment_uuid=None):
    query = model_query(context, models.Host
                        ).filter_by(uuid=host_uuid
                                    ).options(joinedload('failover_segment'))
    if segment_uuid:
        query = query.filter_by(failover_segment_id=segment_uuid)

    result = query.first()

    if not result:
        if segment_uuid:
            raise exception.HostNotFoundUnderFailoverSegment(
                host_uuid=host_uuid, segment_uuid=segment_uuid)
        else:
            raise exception.HostNotFound(id=host_uuid)

    return result 

def api_people():
    q = request.args.get('q', '').strip()
    try:
        limit = max(int(request.args.get('limit', 10)), 0)
    except:
        limit = 10

    if q and request.args.get('similar'):
        people = [p for p, _ in Person.similarly_named_to(q, 0.7)]
    else:
        query = Person.query\
            .options(joinedload(Person.affiliation))
        if q:
            q = '%' + q.replace('%', '%%').replace(' ', '%') + '%'
            query = query.filter(Person.name.like(q))\
                         .order_by(func.length(Person.name))

        people = query.order_by(Person.name)\
                      .limit(limit)\
                      .all()

    return jsonify({'people': [p.json() for p in people]})

# THIS IS A PUBLIC API 

def monitor_dashboard():
    docs = [x.id for x in Document.query
            .filter(or_(
                Document.created_by_user_id == current_user.id,
                Document.checked_by_user_id == current_user.id
            ))
            .order_by(Document.created_at.desc()).limit(30)]

    docs = Document.query\
        .options(
            joinedload('created_by'),
            joinedload('sources'),
            joinedload('topic'),
            joinedload('medium'),
        )\
        .filter(Document.id.in_(docs))\
        .order_by(Document.created_at.desc())

    doc_groups = []
    for date, group in groupby(docs, lambda d: d.created_at.date()):
        doc_groups.append([date, list(group)])

    return render_template('dashboard/monitor.haml',
                           doc_groups=doc_groups) 

def dashboard():
    latest_docs = [x.id for x in Document.query.order_by(Document.created_at.desc()).limit(30)]

    latest_docs = Document.query\
        .options(
            joinedload('created_by'),
            joinedload('sources'),
            joinedload('topic'),
            joinedload('medium'),
        )\
        .filter(Document.id.in_(latest_docs))\
        .order_by(Document.created_at.desc())

    doc_groups = []
    for date, group in groupby(latest_docs, lambda d: d.created_at.date()):
        doc_groups.append([date, list(group)])

    return render_template('dashboard/dashboard.haml',
                           doc_groups=doc_groups) 

def get_current_game(self, db_session, with_bets=False, with_users=False):
        query = db_session.query(HSBetGame).filter(HSBetGame.is_running)

        # with_bets and with_users are just optimizations for the querying.
        # If a code path knows it's going to need to load the bets and users for each bet,
        # we can load them eagerly with a proper SQL JOIN instead of lazily later,
        # to make that code path faster
        if with_bets:
            query = query.options(joinedload(HSBetGame.bets))
        if with_users:
            query = query.options(joinedload(HSBetGame.bets).joinedload(HSBetBet.user))

        current_game = query.one_or_none()
        if current_game is None:
            current_game = HSBetGame()
            db_session.add(current_game)
            db_session.flush()  # so we get current_game.id set
        return current_game 

def with_(cls, schema):
        """
        Query class and eager load schema at once.
        :type schema: dict

        Example:
            schema = {
                'user': JOINED, # joinedload user
                'comments': (SUBQUERY, {  # load comments in separate query
                    'user': JOINED  # but, in this separate query, join user
                })
            }
            # the same schema using class properties:
            schema = {
                Post.user: JOINED,
                Post.comments: (SUBQUERY, {
                    Comment.user: JOINED
                })
            }
            User.with_(schema).first()
        """
        return cls.query.options(*eager_expr(schema or {})) 

def with_joined(cls, *paths):
        """
        Eagerload for simple cases where we need to just
         joined load some relations
        In strings syntax, you can split relations with dot 
         due to this SQLAlchemy feature: https://goo.gl/yM2DLX
         
        :type paths: *List[str] | *List[InstrumentedAttribute]

        Example 1:
            Comment.with_joined('user', 'post', 'post.comments').first()

        Example 2:
            Comment.with_joined(Comment.user, Comment.post).first()
        """
        options = [joinedload(path) for path in paths]
        return cls.query.options(*options) 

def query_orm_for_stop(cls, session, stop_id, detailed=False, agency=None):
        """
        simple utility for querying a stop from gtfsdb
        """
        ret_val = None
        try:
            log.info("query Stop for {}".format(stop_id))
            q = session.query(cls)
            q = q.filter(cls.stop_id == stop_id)
            # TODO q.filter(cls.agency_id == agency_id)
            if detailed:
                try:
                    q = q.options(joinedload("stop_features"))
                except:
                    pass
            ret_val = q.one()
        except Exception as e:
            log.info(e)
        return ret_val 

def _share_group_snapshot_get(context, share_group_snapshot_id, session=None):
    session = session or get_session()
    result = model_query(
        context, models.ShareGroupSnapshot, session=session,
        project_only=True, read_deleted='no',
    ).options(
        joinedload('share_group'),
        joinedload('share_group_snapshot_members'),
    ).filter_by(
        id=share_group_snapshot_id,
    ).first()

    if not result:
        raise exception.ShareGroupSnapshotNotFound(
            share_group_snapshot_id=share_group_snapshot_id)

    return result 

def _share_type_get_query(context, session=None, read_deleted=None,
                          expected_fields=None):
    expected_fields = expected_fields or []
    query = (model_query(context,
                         models.ShareTypes,
                         session=session,
                         read_deleted=read_deleted).
             options(joinedload('extra_specs')))

    if 'projects' in expected_fields:
        query = query.options(joinedload('projects'))

    if not context.is_admin:
        the_filter = [models.ShareTypes.is_public == true()]
        projects_attr = getattr(models.ShareTypes, 'projects')
        the_filter.extend([
            projects_attr.any(project_id=context.project_id)
        ])
        query = query.filter(or_(*the_filter))

    return query 

def share_instance_get(context, share_instance_id, session=None,
                       with_share_data=False):
    if session is None:
        session = get_session()
    result = model_query(
        context, models.ShareInstance, session=session,
    ).filter_by(
        id=share_instance_id,
    ).options(
        joinedload('export_locations'),
        joinedload('share_type'),
    ).first()
    if result is None:
        raise exception.NotFound()

    if with_share_data:
        parent_share = share_get(context, result['share_id'], session=session)
        result.set_share_data(parent_share)

    return result 

def get_alias_infos_with_pagination(user, page_id=0, query=None) -> [AliasInfo]:
    ret = []
    q = (
        db.session.query(Alias)
        .options(joinedload(Alias.mailbox))
        .filter(Alias.user_id == user.id)
        .order_by(Alias.created_at.desc())
    )

    if query:
        q = q.filter(
            or_(Alias.email.ilike(f"%{query}%"), Alias.note.ilike(f"%{query}%"))
        )

    q = q.limit(PAGE_LIMIT).offset(page_id * PAGE_LIMIT)

    for alias in q:
        ret.append(get_alias_info(alias))

    return ret 

def _share_type_extra_specs_get_item(context, share_type_id, key,
                                     session=None):
    result = _share_type_extra_specs_query(
        context, share_type_id, session=session
    ).filter_by(key=key).options(joinedload('share_type')).first()

    if not result:
        raise exception.ShareTypeExtraSpecsNotFound(
            extra_specs_key=key,
            share_type_id=share_type_id)

    return result 

def _share_type_get_by_name(context, name, session=None):
    result = (model_query(context, models.ShareTypes, session=session).
              options(joinedload('extra_specs')).
              filter_by(name=name).
              first())

    if not result:
        raise exception.ShareTypeNotFoundByName(share_type_name=name)

    return _dict_with_specs(result) 

def hazardset(request):
    id = request.matchdict["hazardset"]
    hazardset = (
        request.dbsession.query(HazardSet)
        .options(joinedload(HazardSet.layers).joinedload(Layer.hazardlevel))
        .get(id)
    )
    return {"hazardset": hazardset} 

def _server_get_query(context, session=None):
    if session is None:
        session = get_session()
    return (model_query(context, models.ShareServer, session=session).
            options(joinedload('share_instances'),
                    joinedload('network_allocations'),
                    joinedload('share_network_subnet'))) 

def _network_subnet_get_query(context, session=None):
    if session is None:
        session = get_session()
    return (model_query(context, models.ShareNetworkSubnet, session=session).
            options(joinedload('share_servers'), joinedload('share_network'))) 

def _network_get_query(context, session=None):
    if session is None:
        session = get_session()
    return (model_query(context, models.ShareNetwork, session=session,
                        project_only=True).
            options(joinedload('share_instances'),
                    joinedload('security_services'),
                    subqueryload('share_network_subnets'))) 

def _share_export_locations_get(context, share_instance_ids,
                                include_admin_only=True,
                                ignore_secondary_replicas=False, session=None):
    session = session or get_session()

    if not isinstance(share_instance_ids, (set, list, tuple)):
        share_instance_ids = (share_instance_ids, )

    query = model_query(
        context,
        models.ShareInstanceExportLocations,
        session=session,
        read_deleted="no",
    ).filter(
        models.ShareInstanceExportLocations.share_instance_id.in_(
            share_instance_ids),
    ).order_by(
        "updated_at",
    ).options(
        joinedload("_el_metadata_bare"),
    )

    if not include_admin_only:
        query = query.filter_by(is_admin_only=False)

    if ignore_secondary_replicas:
        replica_state_attr = models.ShareInstance.replica_state
        query = query.join("share_instance").filter(
            or_(replica_state_attr == None,  # noqa
                replica_state_attr == constants.REPLICA_STATE_ACTIVE))

    return query.all() 

def _share_metadata_get_query(context, share_id, session=None):
    return (model_query(context, models.ShareMetadata, session=session,
                        read_deleted="no").
            filter_by(share_id=share_id).
            options(joinedload('share'))) 

def share_snapshot_get(context, snapshot_id, session=None):
    result = (model_query(context, models.ShareSnapshot, session=session,
                          project_only=True).
              filter_by(id=snapshot_id).
              options(joinedload('share')).
              options(joinedload('instances')).
              first())

    if not result:
        raise exception.ShareSnapshotNotFound(snapshot_id=snapshot_id)

    return result 

def list_tasks(self, limit=None, details=False, category=None,
                   offset=None, status=None, sample_id=None, not_status=None,
                   completed_after=None, order_by=None):
        """Retrieve list of task.
        @param limit: specify a limit of entries.
        @param details: if details about must be included
        @param category: filter by category
        @param offset: list offset
        @param status: filter by task status
        @param sample_id: filter tasks for a sample
        @param not_status: exclude this task status from filter
        @param completed_after: only list tasks completed after this timestamp
        @param order_by: definition which field to sort by
        @return: list of tasks.
        """
        session = self.Session()
        try:
            search = session.query(Task)

            if status:
                search = search.filter_by(status=status)
            if not_status:
                search = search.filter(Task.status != not_status)
            if category:
                search = search.filter_by(category=category)
            if details:
                search = search.options(joinedload("guest"), joinedload("errors"), joinedload("tags"))
            if sample_id is not None:
                search = search.filter_by(sample_id=sample_id)
            if completed_after:
                search = search.filter(Task.completed_on > completed_after)

            search = search.order_by(order_by or "added_on desc")
            tasks = search.limit(limit).offset(offset).all()
            return tasks
        except SQLAlchemyError as e:
            log.debug("Database error listing tasks: {0}".format(e))
            return []
        finally:
            session.close() 

def _share_replica_get_with_filters(context, share_id=None, replica_id=None,
                                    replica_state=None, status=None,
                                    with_share_server=True, session=None):

    query = model_query(context, models.ShareInstance, session=session,
                        read_deleted="no")

    if share_id is not None:
        query = query.filter(models.ShareInstance.share_id == share_id)

    if replica_id is not None:
        query = query.filter(models.ShareInstance.id == replica_id)

    if replica_state is not None:
        query = query.filter(
            models.ShareInstance.replica_state == replica_state)
    else:
        query = query.filter(models.ShareInstance.replica_state.isnot(None))

    if status is not None:
        query = query.filter(models.ShareInstance.status == status)

    if with_share_server:
        query = query.options(joinedload('share_server'))

    return query 

def get(self, test_id: str):
        testcase = TestCase.query.options(undefer("message"), joinedload("job")).get(
            test_id
        )

        schema = TestCaseSchema()
        return self.respond_with_schema(schema, testcase)