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
4
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
3
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)]
)
3
Example 3
@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)
3
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)
3
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)
3
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)
3
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)
3
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)
3
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)
3
Example 10
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)
3
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
3
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
0
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')
0
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)
0
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())
0
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))
0
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'
)
0
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)
0
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'
)
0
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
0
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