sqlalchemy.union

Here are the examples of the python api sqlalchemy.union taken from open source projects. By voting up you can indicate which examples are most useful and appropriate.

21 Examples 7

Example 1

Project: bauble.classic Source File: __init__.py
def _get_pertinent_objects(cls, get_query_func, objs, session):
    """
    :param cls:
    :param get_query_func:
    :param objs:
    :param session:
    """
    if session is None:
        import bauble.db as db
        session = db.Session()
    if not isinstance(objs, (tuple, list)):
        objs = [objs]
    queries = map(lambda o: get_query_func(o, session), objs)
    # TODO: what is the problem with the following form?
    # results = session.query(cls).order_by(None).union(*queries)
    unions = union(*[q.statement for q in queries])
    results = session.query(cls).from_statement(unions)
    return results

Example 2

Project: sqlalchemy Source File: test_select.py
    def test_plain_union(self):
        table = self.tables.some_table
        s1 = select([table]).where(table.c.id == 2)
        s2 = select([table]).where(table.c.id == 3)

        u1 = union(s1, s2)
        self._assert_result(
            u1.order_by(u1.c.id),
            [(2, 2, 3), (3, 3, 4)]
        )

Example 3

Project: sqlalchemy Source File: test_query.py
Function: test_union
    @testing.requires.subqueries
    def test_union(self):
        (s1, s2) = (
            select([t1.c.col3.label('col3'), t1.c.col4.label('col4')],
                   t1.c.col2.in_(["t1col2r1", "t1col2r2"])),
            select([t2.c.col3.label('col3'), t2.c.col4.label('col4')],
                   t2.c.col2.in_(["t2col2r2", "t2col2r3"]))
        )
        u = union(s1, s2)

        wanted = [('aaa', 'aaa'), ('bbb', 'bbb'), ('bbb', 'ccc'),
                  ('ccc', 'aaa')]
        found1 = self._fetchall_sorted(u.execute())
        eq_(found1, wanted)

        found2 = self._fetchall_sorted(u.alias('bar').select().execute())
        eq_(found2, wanted)

Example 4

Project: sqlalchemy Source File: test_query.py
    @testing.fails_on('firebird', "doesn't like ORDER BY with UNIONs")
    def test_union_ordered(self):
        (s1, s2) = (
            select([t1.c.col3.label('col3'), t1.c.col4.label('col4')],
                   t1.c.col2.in_(["t1col2r1", "t1col2r2"])),
            select([t2.c.col3.label('col3'), t2.c.col4.label('col4')],
                   t2.c.col2.in_(["t2col2r2", "t2col2r3"]))
        )
        u = union(s1, s2, order_by=['col3', 'col4'])

        wanted = [('aaa', 'aaa'), ('bbb', 'bbb'), ('bbb', 'ccc'),
                  ('ccc', 'aaa')]
        eq_(u.execute().fetchall(), wanted)

Example 5

Project: sqlalchemy Source File: test_query.py
    @testing.fails_on('firebird', "doesn't like ORDER BY with UNIONs")
    @testing.requires.subqueries
    def test_union_ordered_alias(self):
        (s1, s2) = (
            select([t1.c.col3.label('col3'), t1.c.col4.label('col4')],
                   t1.c.col2.in_(["t1col2r1", "t1col2r2"])),
            select([t2.c.col3.label('col3'), t2.c.col4.label('col4')],
                   t2.c.col2.in_(["t2col2r2", "t2col2r3"]))
        )
        u = union(s1, s2, order_by=['col3', 'col4'])

        wanted = [('aaa', 'aaa'), ('bbb', 'bbb'), ('bbb', 'ccc'),
                  ('ccc', 'aaa')]
        eq_(u.alias('bar').select().execute().fetchall(), wanted)

Example 6

Project: sqlalchemy Source File: test_query.py
    @testing.requires.except_
    @testing.fails_on('sqlite', "Can't handle this style of nesting")
    def test_except_style1(self):
        e = except_(union(
            select([t1.c.col3, t1.c.col4]),
            select([t2.c.col3, t2.c.col4]),
            select([t3.c.col3, t3.c.col4]),
        ), select([t2.c.col3, t2.c.col4]))

        wanted = [('aaa', 'aaa'), ('aaa', 'ccc'), ('bbb', 'aaa'),
                  ('bbb', 'bbb'), ('ccc', 'bbb'), ('ccc', 'ccc')]

        found = self._fetchall_sorted(e.alias().select().execute())
        eq_(found, wanted)

Example 7

Project: sqlalchemy Source File: test_query.py
    @testing.requires.except_
    def test_except_style2(self):
        # same as style1, but add alias().select() to the except_().
        # sqlite can handle it now.

        e = except_(union(
            select([t1.c.col3, t1.c.col4]),
            select([t2.c.col3, t2.c.col4]),
            select([t3.c.col3, t3.c.col4]),
        ).alias().select(), select([t2.c.col3, t2.c.col4]))

        wanted = [('aaa', 'aaa'), ('aaa', 'ccc'), ('bbb', 'aaa'),
                  ('bbb', 'bbb'), ('ccc', 'bbb'), ('ccc', 'ccc')]

        found1 = self._fetchall_sorted(e.execute())
        eq_(found1, wanted)

        found2 = self._fetchall_sorted(e.alias().select().execute())
        eq_(found2, wanted)

Example 8

Project: sqlalchemy Source File: test_query.py
    @testing.requires.intersect
    @testing.fails_on('sqlite', "sqlite can't handle leading parenthesis")
    def test_intersect_unions(self):
        u = intersect(
            union(
                select([t1.c.col3, t1.c.col4]),
                select([t3.c.col3, t3.c.col4]),
            ),
            union(
                select([t2.c.col3, t2.c.col4]),
                select([t3.c.col3, t3.c.col4]),
            ).alias().select()
        )
        wanted = [('aaa', 'ccc'), ('bbb', 'aaa'), ('ccc', 'bbb')]
        found = self._fetchall_sorted(u.execute())

        eq_(found, wanted)

Example 9

Project: sqlalchemy Source File: test_query.py
    @testing.requires.intersect
    def test_intersect_unions_2(self):
        u = intersect(
            union(
                select([t1.c.col3, t1.c.col4]),
                select([t3.c.col3, t3.c.col4]),
            ).alias().select(),
            union(
                select([t2.c.col3, t2.c.col4]),
                select([t3.c.col3, t3.c.col4]),
            ).alias().select()
        )
        wanted = [('aaa', 'ccc'), ('bbb', 'aaa'), ('ccc', 'bbb')]
        found = self._fetchall_sorted(u.execute())

        eq_(found, wanted)

Example 10

Project: ibis Source File: alchemy.py
Function: compile
    def compile(self):
        context = self.context

        if self.distinct:
            sa_func = sa.union
        else:
            sa_func = sa.union_all

        left_set = context.get_compiled_expr(self.left)
        right_set = context.get_compiled_expr(self.right)

        return sa_func(left_set, right_set)

Example 11

Project: SAUCE Source File: event.py
    def members_query(self, qry=None):
        if not qry:
            qry = User.query
        qry = qry.select_entity_from(union(
            qry.join(event_members).join(Event)
                .filter_by(id=self.id).order_by(None),
            qry.join(lesson_members).join(Lesson)
                .filter_by(event_id=self.id).order_by(None),
            qry.join(team_members).join(Team).join(Lesson)
                .filter_by(event_id=self.id).order_by(None),
        )).order_by(User.user_name)
        return qry

Example 12

Project: SAUCE Source File: event.py
    def members_query(self, qry=None):
        if not qry:
            qry = User.query
        qry = qry.select_entity_from(union(
                qry.join(lesson_members).filter_by(lesson_id=self.id).order_by(None),
                qry.join(team_members).join(Team).filter_by(lesson_id=self.id).order_by(None),
            )).order_by(User.id)
        return qry

Example 13

Project: sqlalchemy Source File: test_compiler.py
    def test_union(self):
        t1 = table(
            't1', column('col1'), column('col2'),
            column('col3'), column('col4'))
        t2 = table(
            't2', column('col1'), column('col2'),
            column('col3'), column('col4'))
        s1, s2 = select(
            [t1.c.col3.label('col3'), t1.c.col4.label('col4')],
            t1.c.col2.in_(['t1col2r1', 't1col2r2'])), \
            select([t2.c.col3.label('col3'), t2.c.col4.label('col4')],
                   t2.c.col2.in_(['t2col2r2', 't2col2r3']))
        u = union(s1, s2, order_by=['col3', 'col4'])
        self.assert_compile(u,
                            'SELECT t1.col3 AS col3, t1.col4 AS col4 '
                            'FROM t1 WHERE t1.col2 IN (:col2_1, '
                            ':col2_2) UNION SELECT t2.col3 AS col3, '
                            't2.col4 AS col4 FROM t2 WHERE t2.col2 IN '
                            '(:col2_3, :col2_4) ORDER BY col3, col4')
        self.assert_compile(u.alias('bar').select(),
                            'SELECT bar.col3, bar.col4 FROM (SELECT '
                            't1.col3 AS col3, t1.col4 AS col4 FROM t1 '
                            'WHERE t1.col2 IN (:col2_1, :col2_2) UNION '
                            'SELECT t2.col3 AS col3, t2.col4 AS col4 '
                            'FROM t2 WHERE t2.col2 IN (:col2_3, '
                            ':col2_4)) AS bar')

Example 14

Project: sqlalchemy Source File: test_query.py
    @testing.crashes('oracle', 'FIXME: unknown, verify not fails_on')
    @testing.fails_on(
        'firebird',
        "has trouble extracting anonymous column from union subquery")
    @testing.fails_on('mysql', 'FIXME: unknown')
    @testing.fails_on('sqlite', 'FIXME: unknown')
    def test_union_all(self):
        e = union_all(
            select([t1.c.col3]),
            union(
                select([t1.c.col3]),
                select([t1.c.col3]),
            )
        )

        wanted = [('aaa',), ('aaa',), ('bbb',), ('bbb',), ('ccc',), ('ccc',)]
        found1 = self._fetchall_sorted(e.execute())
        eq_(found1, wanted)

        found2 = self._fetchall_sorted(e.alias('foo').select().execute())
        eq_(found2, wanted)

Example 15

Project: ggrc-core Source File: mysql.py
  def search(self, terms, types=None, permission_type='read',
             permission_model=None, contact_id=None, extra_params={}):
    """Prepare the search query and return the results set based on the
    full text table."""
    model_names = self._get_grouped_types(types, extra_params)
    columns = (
        self.record_type.key.label('key'),
        self.record_type.type.label('type'),
        self.record_type.property.label('property'),
        self.record_type.content.label('content'),
        case(
            [(self.record_type.property == 'title', literal(0))],
            else_=literal(1)).label('sort_key'))

    query = db.session.query(*columns)
    query = query.filter(self.get_permissions_query(
        model_names, permission_type, permission_model))
    query = query.filter(self._get_filter_query(terms))
    query = self.search_get_owner_query(query, types, contact_id)

    model_names = [model.__name__ for model in all_models.all_models]
    if types is not None:
      model_names = [m for m in model_names if m in types]

    unions = [query]
    # Add extra_params and extra_colums:
    for k, v in extra_params.iteritems():
      if k not in model_names:
        continue
      q = db.session.query(*columns)
      q = q.filter(
          self.get_permissions_query([k], permission_type, permission_model))
      q = q.filter(self._get_filter_query(terms))
      q = self.search_get_owner_query(q, [k], contact_id)
      q = self._add_extra_params_query(q, k, v)
      unions.append(q)
    all_queries = union(*unions)
    all_queries = aliased(all_queries.order_by(
        all_queries.c.sort_key, all_queries.c.content))
    return db.session.execute(
        select([all_queries.c.key, all_queries.c.type]).distinct())

Example 16

Project: ggrc-core Source File: query_helpers.py
def get_myobjects_query(types=None, contact_id=None, is_creator=False):  # noqa
  """Filters by "myview" for a given person.

  Finds all objects which might appear on a user's Profile or Dashboard
  pages.

  This method only *limits* the result set -- Contexts and Roles will still
  filter out forbidden objects.
  """
  type_models = _types_to_type_models(types)
  model_names = [model.__name__ for model in type_models]
  type_union_queries = []

  def _get_people():
    """Get all the people w/o any restrictions."""
    all_people = db.session.query(
        all_models.Person.id.label('id'),
        literal(all_models.Person.__name__).label('type'),
        literal(None).label('context_id')
    )
    return all_people

  def _get_object_people():
    """Objects to which the user is 'mapped'."""
    object_people_query = db.session.query(
        ObjectPerson.personable_id.label('id'),
        ObjectPerson.personable_type.label('type'),
        literal(None).label('context_id')
    ).filter(
        and_(
            ObjectPerson.person_id == contact_id,
            ObjectPerson.personable_type.in_(model_names)
        )
    )
    return object_people_query

  def _get_object_owners():
    """Objects for which the user is an 'owner'."""
    object_owners_query = db.session.query(
        ObjectOwner.ownable_id.label('id'),
        ObjectOwner.ownable_type.label('type'),
        literal(None).label('context_id')
    ).filter(
        and_(
            ObjectOwner.person_id == contact_id,
            ObjectOwner.ownable_type.in_(model_names),
        )
    )
    return object_owners_query

  def _get_object_mapped_ca():
    """Objects to which the user is mapped via a custom attribute."""
    ca_mapped_objects_query = db.session.query(
        CustomAttributeValue.attributable_id.label('id'),
        CustomAttributeValue.attributable_type.label('type'),
        literal(None).label('context_id')
    ).filter(
        and_(
            CustomAttributeValue.attribute_value == "Person",
            CustomAttributeValue.attribute_object_id == contact_id,
            CustomAttributeValue.attributable_type.in_(model_names)
        )
    )
    return ca_mapped_objects_query

  def _get_objects_user_assigned():
    """Objects for which the user is assigned."""
    dst_assignee_query = db.session.query(
        Relationship.destination_id.label('id'),
        Relationship.destination_type.label('type'),
        literal(None).label('context_id'),
    ).filter(
        and_(
            Relationship.source_type == "Person",
            Relationship.source_id == contact_id,
            Relationship.destination_type.in_(model_names)
        ),
    )
    src_assignee_query = db.session.query(
        Relationship.source_id.label('id'),
        Relationship.source_type.label('type'),
        literal(None).label('context_id'),
    ).filter(
        and_(
            Relationship.destination_type == "Person",
            Relationship.destination_id == contact_id,
            Relationship.source_type.in_(model_names)
        ),
    )
    return dst_assignee_query.union(src_assignee_query)

  def _get_results_by_context(model):
    """Objects based on the context of the current model.

    Return the objects that are in private contexts via UserRole.
    """
    context_query = db.session.query(
        model.id.label('id'),
        literal(model.__name__).label('type'),
        literal(None).label('context_id'),
    ).join(
        UserRole,
        and_(
            UserRole.context_id == model.context_id,
            UserRole.person_id == contact_id,
        )
    )
    return context_query

  def _get_assigned_to_records(model):
    """Get query by models contacts fields.

    Objects for which the user is the 'contact' or 'secondary contact'.
    Control also has 'principal_assessor' and 'secondary_assessor'.
    """
    model_type_queries = []
    for attr in ('contact_id', 'secondary_contact_id',
                 'principal_assessor_id', 'secondary_assessor_id'):
      if hasattr(model, attr):
        model_type_queries.append(getattr(model, attr) == contact_id)
    return model_type_queries

  def _get_tasks_in_cycle(model):
    """Filter tasks with particular statuses and cycle.

    Filtering tasks with statuses "Assigned", "InProgress" and "Finished".
    Where the task is in current users cycle.
    """
    task_query = db.session.query(
        model.id.label('id'),
        literal(model.__name__).label('type'),
        literal(None).label('context_id'),
    ).join(Cycle, Cycle.id == model.cycle_id).filter(
        and_(
            Cycle.is_current == true(),
            model.contact_id == contact_id,
            model.status.in_(
                all_models.CycleTaskGroupObjectTask.ACTIVE_STATES
            )
        )
    )
    return task_query

  def _get_model_specific_query(model):
    """Prepare query specific for a particular model."""
    model_type_query = None
    if model is all_models.CycleTaskGroupObjectTask:
      model_type_query = _get_tasks_in_cycle(model)
    else:
      model_type_queries = _get_assigned_to_records(model)
      if model_type_queries:
        type_column = get_type_select_column(model)
        model_type_query = db.session.query(
            model.id.label('id'),
            type_column.label('type'),
            literal(None).label('context_id')
        ).filter(or_(*model_type_queries)).distinct()
    return model_type_query

  # Note: We don't return mapped objects for the Creator because being mapped
  # does not give the Creator necessary permissions to view the object.
  if not is_creator:
    type_union_queries.append(_get_object_people())
  type_union_queries.extend((_get_object_owners(),
                            _get_object_mapped_ca(),
                            _get_objects_user_assigned()))

  for model in type_models:
    query = _get_model_specific_query(model)
    if query:
      type_union_queries.append(query)

    if model is all_models.Workflow:
      type_union_queries.append(backlog_workflows())
    if model is all_models.Person:
      type_union_queries.append(_get_people())
    if model in (all_models.Program, all_models.Audit, all_models.Workflow):
      type_union_queries.append(_get_results_by_context(model))

  return alias(union(*type_union_queries))

Example 17

Project: SAUCE Source File: event_admin.py
    def __init__(self, event, **kwargs):
        self.event = event

        menu_items = OrderedDict((
            ('./events/', 'Event'),
            ('./tutors/', 'Tutors'),
            ('./lessons/', 'Lessons'),
            ('./teams/', 'Teams'),
            ('./students/', 'Students'),
            ('./sheets/', 'Sheets'),
            ('./assignments/', 'Assignments'),
            ('./tests/', 'Tests'),
            ('./newsitems/', 'NewsItems'),
        ))
        self.menu_items = menu_items

        super(EventAdminController, self).__init__(**kwargs)

        self.events = EventsCrudController(
            inject=dict(teacher=request.user),
            query_modifier=lambda qry: qry.filter_by(id=self.event.id),
            menu_items=self.menu_items,
            allow_new=False, allow_delete=False,
            **kwargs)

        self.lessons = LessonsCrudController(
            inject=dict(event=self.event),
            query_modifier=lambda qry: qry.filter_by(event_id=self.event.id),
            query_modifiers={
                # Disabled so that the teacher can assign any users as tutors
                #'tutor': lambda qry: qry.filter(User.id.in_((t.id for t in self.event.tutors))),
                'teams': lambda qry: qry.join(Team.lesson).filter_by(event_id=self.event.id),
            },
            menu_items=self.menu_items,
            **kwargs)

        self.teams = TeamsCrudController(
            #query_modifier=lambda qry: qry.filter(Team.lesson_id.in_((l.id for l in self.event.lessons))),
            query_modifier=lambda qry: qry.join(Team.lesson).filter_by(event_id=self.event.id),
            query_modifiers={
                'lesson': lambda qry: qry.filter_by(event_id=self.event.id),
                # Disabled so that the teacher can assign any users as members
                #'members': lambda qry: qry.filter(User.id.in_((u.id for u in self.lesson.event.members))),
            },
            menu_items=self.menu_items,
            hints=dict(event=self.event),
            **kwargs)

        self.students = StudentsCrudController(
            query_modifier=lambda qry: qry.select_entity_from(union(
                    qry.join(event_members).join(Event)
                        .filter_by(id=self.event.id).order_by(None),
                    qry.join(lesson_members).join(Lesson)
                        .filter_by(event_id=self.event.id).order_by(None),
                    qry.join(team_members).join(Team).join(Lesson)
                        .filter_by(event_id=self.event.id).order_by(None),
                )).order_by(User.user_name),
            query_modifiers={
                #'teams': lambda qry: qry.filter(Team.lesson_id.in_((l.id for l in self.event.lessons))),
                'teams': lambda qry: qry.join(Team.lesson).filter_by(event_id=self.event.id),
                '_lessons': lambda qry: qry.filter_by(event_id=self.event.id),
            },
            menu_items=self.menu_items,
            hints=dict(event=self.event),
            **kwargs)

        self.tutors = TutorsCrudController(
            query_modifier=lambda qry: (qry.join(lesson_tutors).join(Lesson)
                .filter_by(event_id=self.event.id).order_by(User.id)),
            query_modifiers={
                'tutored_lessons': lambda qry: qry.filter_by(event_id=self.event.id),
            },
            menu_items=self.menu_items,
            hints=dict(event=self.event),
            **kwargs)

        self.sheets = SheetsCrudController(
            inject=dict(event=self.event, _teacher=request.user),
            query_modifier=lambda qry: qry.filter_by(event_id=self.event.id),
            menu_items=self.menu_items,
            allow_copy=True,
            **kwargs)

        self.assignments = AssignmentsCrudController(
            inject=dict(_teacher=request.user),
            query_modifier=lambda qry: qry.join(Assignment.sheet).filter_by(event_id=self.event.id),
            query_modifiers={
                'sheet': lambda qry: qry.filter_by(event_id=self.event.id),
            },
            menu_items=self.menu_items,
            **kwargs)

        self.tests = TestsCrudController(
            inject=dict(user=request.user),
            query_modifier=lambda qry: (qry.join(Test.assignment).join(Assignment.sheet)
                .filter_by(event_id=self.event.id)),
            query_modifiers={
                'assignment': lambda qry: qry.join(Assignment.sheet).filter_by(event_id=self.event.id),
            },
            menu_items=self.menu_items,
            **kwargs)

        self.newsitems = NewsItemController(
            inject=dict(user=request.user),
            query_modifier=lambda qry: qry.filter(or_(NewsItem.event == None, NewsItem.event == self.event)),
            query_modifiers={
                'event': lambda qry: qry.filter_by(id=self.event.id),
            },
            menu_items=self.menu_items,
            **kwargs)

        self.allow_only = Any(
            user_is_in('teachers', self.event),
            has_permission('manage'),
            msg=u'You have no permission to manage this Event'
        )

Example 18

Project: SAUCE Source File: lessons.py
    @expose('sauce.templates.submissions')
    def _default(self, *args, **kwargs):
        # TODO: This filtering really needs to be rewritten!
        filters = dict(zip(args[::2], args[1::2]))
        real_filters = dict(assignment_id=set(), user_id=set())

        if self.assignment:
            real_filters['assignment_id'] = set((self.assignment.id, ))
        else:
            sheet = None
            if self.sheet:
                sheet = self.sheet
            elif 'sheet' in filters:
                try:
                    s = int(filters['sheet'])
                    sheet = (DBSession.query(Sheet).filter_by(event_id=self.event.id)
                        .filter_by(sheet_id=s).one())
                except NoResultFound:
                    pass
            if sheet:
                if 'assignment' in filters:
                    try:
                        a = int(filters['assignment'])
                        a_id = (DBSession.query(Assignment.id).filter_by(sheet_id=sheet.id)
                            .filter_by(assignment_id=a).one().id)
                        real_filters['assignment_id'] |= set((a_id, ))
                    except NoResultFound:
                        pass
                else:
                    real_filters['assignment_id'] |= set((a.id for a in sheet.assignments))

        if self.event:
            # Dirty, dirty hack to properly filter assignments by event
            if real_filters['assignment_id']:
                # Only allow filters by assignments from the set event
                real_filters['assignment_id'] &= set((a.id for s in self.event.sheets for a in s.assignments))
            else:
                # Simply filter by assignments for this event
                real_filters['assignment_id'] = set((a.id for s in self.event.sheets for a in s.assignments))

        if 'lesson' in filters:
            try:
                l = int(filters['lesson'])
                q1 = DBSession.query(User.id).join(lesson_members).filter_by(lesson_id=l).order_by(None)
                q2 = DBSession.query(User.id).join(team_members).join(Team).filter_by(lesson_id=l).order_by(None)
                students = DBSession.query(User.id).select_entity_from(union(q1, q2)).order_by(User.id)
                real_filters['user_id'] |= set((s.id for s in students))
            except SQLAlchemyError:
                pass
        if 'team' in filters:
            try:
                students = (DBSession.query(User.id).join(team_members)
                    .filter_by(team_id=int(filters['team'])).join(Team))
                if self.lesson:
                    students = students.filter_by(lesson_id=self.lesson.id)
                else:
                    #students = students.filter(Team.lesson_id.in_(l.id for l in self.event.lessons))
                    students = students.join(Team.lesson).filter_by(event_id=self.event.id)
                real_filters['user_id'] |= set((s.id for s in students))
            except SQLAlchemyError:
                pass
        if 'user' in filters:
            try:
                user_id = DBSession.query(User.id).filter_by(id=int(filters['user'])).one().id
                real_filters['user_id'] |= set((user_id, ))
            except NoResultFound:
                pass

        # Cleanup filters for performancy
        definite_filters = dict()
        for (k, v) in real_filters.iteritems():
            if v:
                if isinstance(v, (list, tuple, set)) and len(v) == 1:
                    definite_filters[k] = v.pop()
                else:
                    definite_filters[k] = v

        c.table = self.table
        values = self.table_filler.get_value(filters=definite_filters)
        return dict(page='event', view=None, values=values,
            filters=filters, real_filters=real_filters, definite_filters=definite_filters)

Example 19

Project: SAUCE Source File: lessons.py
    def __init__(self, lesson, **kwargs):
        self.lesson = lesson

        menu_items = OrderedDict((
            ('./lessons/', 'Lesson'),
            ('./tutors/', 'Tutor'),
            ('./teams/', 'Teams'),
            ('./students/', 'Students'),
            #('./submissions/', 'Submissions'),
        ))
        self.menu_items = menu_items

        super(LessonController, self).__init__(**kwargs)

        self.lessons = LessonsCrudController(
            # inject=dict(tutor=request.user, event=self.lesson.event),  # No new lesson to be created
            query_modifier=lambda qry: qry.filter_by(id=self.lesson.id),
            query_modifiers={
                # Tutors can only delegate ownership to other tutors
                #'tutor': lambda qry: qry.filter(User.id.in_((t.id for t in self.lesson.event.tutors))),
                'tutor': lambda qry: qry.join(User.tutored_lessons).filter_by(event_id=self.lesson.event.id)
            },
            allow_new=False, allow_delete=False,
            menu_items=self.menu_items,
            **kwargs)
        self.students = StudentsCrudController(
            inject=dict(_lessons=[self.lesson]),
            query_modifier=lambda qry: qry.select_entity_from(union(
                    qry.join(lesson_members).filter_by(lesson_id=self.lesson.id).order_by(None),
                    qry.join(team_members).join(Team).filter_by(lesson_id=self.lesson.id).order_by(None),
                )).order_by(User.id),
            query_modifiers={
                'teams': lambda qry: qry.filter_by(lesson_id=self.lesson.id),
                '_lessons': lambda qry: qry.filter_by(id=self.lesson.id),
            },
            menu_items=self.menu_items,
            allow_delete=False,
            hints=dict(lesson=self.lesson, event=self.lesson.event),
            **kwargs)
        self.teams = TeamsCrudController(
            # inject=dict(lesson=self.lesson),  # Field shows only one value
            query_modifier=lambda qry: qry.filter_by(lesson_id=self.lesson.id),
            query_modifiers={
                #'members': lambda qry: qry.filter(User.id.in_((u.id for u in self.lesson.event.members))),
                'members': lambda qry: qry.select_entity_from(union(
                        qry.join(lesson_members).join(Lesson).filter_by(event_id=self.lesson.event.id).order_by(None),
                        qry.join(team_members).join(Team).join(Team.lesson).filter_by(event_id=self.lesson.event.id).order_by(None),
                    )).order_by(User.id),
                'lesson': lambda qry: qry.filter_by(id=self.lesson.id),
            },
            menu_items=self.menu_items,
            hints=dict(lesson=self.lesson, event=self.lesson.event),
            **kwargs)
        self.tutors = TutorsCrudController(
            query_modifier=lambda qry: (qry.join(lesson_tutors).filter_by(lesson_id=self.lesson.id)
                .order_by(User.id)),
            query_modifiers={
                'tutored_lessons': lambda qry: qry.filter(Lesson.event == self.lesson.event),
            },
            menu_items=self.menu_items, allow_new=False, allow_delete=False,
            hints=dict(lesson=self.lesson, event=self.lesson.event),
            **kwargs)

        self.submissions = SubmissionsController(lesson=self.lesson, menu_items=self.menu_items, **kwargs)

        # Allow access for event teacher and lesson teacher
        self.allow_only = Any(
            user_is_in('teachers', self.lesson.event),
            user_is_in('tutors', self.lesson),
            has_permission('manage'),
            msg=u'You have no permission to manage this Lesson'
        )

Example 20

Project: SAUCE Source File: lti.py
    @expose()
    @with_trailing_slash
    def _lookup(self, assignment_id, *args):
        try:
            assignment_id = int(assignment_id)
            # TODO: Use SQLAlchemy magic on model to make queries on assignment easier
            q1 = (Assignment.query.filter(Assignment.id == assignment_id)
                .join(Assignment._lti).order_by(None))
            q2 = (Assignment.query.filter(Assignment.id == assignment_id)
                .join(Sheet).join(Event).join(Event.lti).order_by(None))
            assignment = Assignment.query.select_entity_from(union(q1, q2)).one()
        except ValueError:
            flash('Invalid LTI Assignment id: %s' % assignment_id, 'error')
            abort(status.HTTP_400_BAD_REQUEST)
        except NoResultFound:
            flash('LTI Assignment %d not found' % assignment_id, 'error')
            abort(status.HTTP_404_NOT_FOUND)
        except MultipleResultsFound:  # pragma: no cover
            log.error('Database inconsistency: LTI Assignment %d', assignment_id, exc_info=True)
            flash('An error occurred while accessing LTI Assignment %d' % assignment_id, 'error')
            abort(status.HTTP_500_INTERNAL_SERVER_ERROR)

        controller = LTIAssignmentController(assignment)
        return controller, args

Example 21

Project: SAUCE Source File: submission_table.py
    def _do_get_provider_count_and_objs(self, **kw):
        '''Custom getter function respecting lesson

        Returns the result count from the database and a query object
        '''
        # TODO: Code duplication with CRC?!

        qry = Submission.query

        # Process lesson filter
        if self.lesson:
            q1 = (qry.join(Submission.user).join(lesson_members).join(Lesson)
                .filter(Lesson.id == self.lesson.id).order_by(None))
            q2 = (qry.join(Submission.user).join(team_members).join(Team)
                .filter(Team.lesson_id == self.lesson.id).order_by(None))
            qry = qry.select_entity_from(union(q1, q2)).order_by(Submission.id)

        filters = kw.pop('filters', dict())
        for filter in filters:
            if isinstance(filters[filter], (list, tuple, set)):
                qry = qry.filter(getattr(Submission, filter).in_(filters[filter]))
            else:
                qry = qry.filter(getattr(Submission, filter) == filters[filter])

        # Process filters from url
        kwfilters = kw
        exc = False
        try:
            kwfilters = self.__provider__._modify_params_for_dates(self.__model__, kwfilters)
        except ValueError as e:  # pragma: no cover
            log.info('Could not parse date filters', exc_info=True)
            flash('Could not parse date filters: "%s".' % e.message, 'error')
            exc = True

        try:
            kwfilters = self.__provider__._modify_params_for_relationships(self.__model__, kwfilters)
        except (ValueError, AttributeError) as e:  # pragma: no cover
            log.info('Could not parse relationship filters', exc_info=True)
            flash('Could not parse relationship filters: "%s". '
                  'You can only filter by the IDs of relationships, not by their names.' % e.message, 'error')
            exc = True
        if exc:  # pragma: no cover
            # Since non-parsed kwfilters are bad, we just have to ignore them now
            kwfilters = {}

        for field_name, value in kwfilters.iteritems():
            field = getattr(self.__model__, field_name)
            try:
                if self.__provider__.is_relation(self.__model__, field_name) and isinstance(value, list):  # pragma: no cover
                    value = value[0]
                    qry = qry.filter(field.contains(value))
                else:
                    qry = qry.filter(field == value)
            except:  # pragma: no cover
                log.warn('Could not create filter on query', exc_info=True)

        # Get total count
        count = qry.count()

        return count, qry