Filter assignment metrics by users

[marcospri]

For:

• https://github.com/hypothesis/lms/issues/6418

Testing

• Play with the API at something like:

http://localhost:8001/api/dashboard/courses/1826/assignments/metrics?h_userids=acct:f6c3ed0edd8a4013bfc8d0c22b9eca@lms.hypothes.is

[robertknight]

With this request:

http://localhost:8001/api/dashboard/courses/2/assignments/metrics?h_userids=acct:f6c3ed0edd8a4013bfc8d0c22b9eca@lms.hypothes.is

I got this error:

web (stderr)         |                ^^^^^^^^^^^^^^^^^^^^^
web (stderr)         |   File "/Users/robert/hypothesis/lms/lms/views/dashboard/api/assignment.py", line 102, in course_assignments_metrics
web (stderr)         |     assignments = self.request.db.scalars(
web (stderr)         |                   ^^^^^^^^^^^^^^^^^^^^^^^^
web (stderr)         |   File "/Users/robert/hypothesis/lms/.tox/dev/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2414, in scalars
web (stderr)         |     return self._execute_internal(
web (stderr)         |            ^^^^^^^^^^^^^^^^^^^^^^^
web (stderr)         |   File "/Users/robert/hypothesis/lms/.tox/dev/lib/python3.11/site-packages/sqlalchemy/orm/session.py", line 2191, in _execute_internal
web (stderr)         |     result: Result[Any] = compile_state_cls.orm_execute_statement(
web (stderr)         |                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
web (stderr)         |   File "/Users/robert/hypothesis/lms/.tox/dev/lib/python3.11/site-packages/sqlalchemy/orm/context.py", line 293, in orm_execute_statement
web (stderr)         |     result = conn.execute(
web (stderr)         |              ^^^^^^^^^^^^^
web (stderr)         |   File "/Users/robert/hypothesis/lms/.tox/dev/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1422, in execute
web (stderr)         |     return meth(
web (stderr)         |            ^^^^^
web (stderr)         |   File "/Users/robert/hypothesis/lms/.tox/dev/lib/python3.11/site-packages/sqlalchemy/sql/elements.py", line 514, in _execute_on_connection
web (stderr)         |     return connection._execute_clauseelement(
web (stderr)         |            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
web (stderr)         |   File "/Users/robert/hypothesis/lms/.tox/dev/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1644, in _execute_clauseelement
web (stderr)         |     ret = self._execute_context(
web (stderr)         |           ^^^^^^^^^^^^^^^^^^^^^^
web (stderr)         |   File "/Users/robert/hypothesis/lms/.tox/dev/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1850, in _execute_context
web (stderr)         |     return self._exec_single_context(
web (stderr)         |            ^^^^^^^^^^^^^^^^^^^^^^^^^^
web (stderr)         |   File "/Users/robert/hypothesis/lms/.tox/dev/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1990, in _exec_single_context
web (stderr)         |     self._handle_dbapi_exception(
web (stderr)         |   File "/Users/robert/hypothesis/lms/.tox/dev/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 2357, in _handle_dbapi_exception
web (stderr)         |     raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
web (stderr)         |   File "/Users/robert/hypothesis/lms/.tox/dev/lib/python3.11/site-packages/sqlalchemy/engine/base.py", line 1971, in _exec_single_context
web (stderr)         |     self.dialect.do_execute(
web (stderr)         |   File "/Users/robert/hypothesis/lms/.tox/dev/lib/python3.11/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute
web (stderr)         |     cursor.execute(statement, parameters)
web (stderr)         | sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DuplicateAlias) table name "assignment_membership" specified more than once
web (stderr)         |
web (stderr)         | [SQL: SELECT DISTINCT assignment.id, assignment.resource_link_id, assignment.tool_consumer_instance_guid, assignment.copied_from_id, assignment.document_url, assignment.extra, assignment.is_gradable, assignment.title, assignment.description, assignment.deep_linking_uuid, assignment.created, assignment.updated
web (stderr)         | FROM assignment JOIN assignment_membership ON assignment.id = assignment_membership.assignment_id JOIN assignment_membership ON assignment.id = assignment_membership.assignment_id JOIN "user" ON "user".id = assignment_membership.user_id, (SELECT DISTINCT ON (assignment_grouping.assignment_id) assignment_grouping.assignment_id AS assignment_id, assignment_grouping.grouping_id AS grouping_id
web (stderr)         | FROM assignment_grouping JOIN grouping ON grouping.id = assignment_grouping.grouping_id
web (stderr)         | WHERE grouping.type = %(type_1)s AND assignment_grouping.assignment_id IN (SELECT assignment_grouping.assignment_id
web (stderr)         | FROM assignment_grouping
web (stderr)         | WHERE assignment_grouping.grouping_id IN (%(grouping_id_1_1)s)) ORDER BY assignment_grouping.assignment_id, assignment_grouping.updated DESC) AS anon_1
web (stderr)         | WHERE assignment_membership.assignment_id IN (SELECT assignment_membership.assignment_id
web (stderr)         | FROM assignment_membership JOIN "user" ON "user".id = assignment_membership.user_id JOIN lti_role ON lti_role.id = assignment_membership.lti_role_id
web (stderr)         | WHERE "user".h_userid = %(h_userid_1)s AND lti_role.scope = %(scope_1)s AND lti_role.type = %(type_2)s) AND "user".h_userid IN (%(h_userid_2_1)s) AND assignment.id = anon_1.assignment_id AND anon_1.grouping_id = %(grouping_id_2)s ORDER BY assignment.title, assignment.id]
web (stderr)         | [parameters: {'type_1': 'course', 'h_userid_1': 'acct:3a022b6c146dfd9df4ea8662178eac@lms.hypothes.is', 'scope_1': 'course', 'type_2': 'instructor', 'grouping_id_2': 2, 'grouping_id_1_1': 2, 'h_userid_2_1': 'acct:f6c3ed0edd8a4013bfc8d0c22b9eca@lms.hypothes.is'}]
web (stderr)         | (Background on this error at: https://sqlalche.me/e/20/f405)

The authentication I am using is taken from an accessing the dashboard as an instructor and "Copy as cURL"-ing the API request from browser devtools.

[marcospri]

It looks like there is an issue when specifying an h_userids filter if the current user is an instructor, as opposed to using an admin login?

Yes, good catch. I need to refactor the tests to run every filter combination. I'll do that in a follow up PR.

Fixed that particular issue for now.