Location of teacher name and assignment URL in Canvas Data

[nate_mcclennen]

We are working on a custom Canvas Data project and having difficulty finding a few required fields in Canvas Data. Each row in the download is based on a unique student's assessment on a learning outcome for an assignment. We then relate a set of fields to this. Can anyone point me in the direction of:

1. Connecting the teacher for a particular course where the assignment lives. We need teacher name.

2. Connecting the url of the assignment. 

Thanks. 

[robotcars]

Hi @nate_mcclennen ,

Getting the teacher name for a course can be tricky depending on your use of Canvas. The teacher role is not 1:1, so getting a single name for a teacher requires your own logic.

1. If every course simply has 1 teacher, then you can probably run a something like, 
   SELECT DISTINCT course_id, user_id FROM enrollment_dim WHERE type = 'TeacherEnrollment'
   and JOIN it down to user_dim. Then use that as a CTE or view to attach the teacher to the course.
2. If a course can have more than 1 teacher, you may have to do that, and do a window function and order by created_at, assuming that the first teacher enrolled is the one you want.
3. My preferred method. I have a separate view called course_sis_teacher_vw. This view comes from our SIS data that I use to populate Canvas. It contains the course sis id and the teachers sis id, and joins in the canvas course id and teacher canvas id, and their names. Since there is only 1 teacher of record in our SIS, I use this when I need that specific teacher from Canvas.

Solving that, you can query assignments, with course, and teacher name. This example uses option 2.

-- mssql
WITH teachers AS (

  SELECT
    course_id
    , course_section_id
    , user_id
    , canvas_user_id
    , teacher_name
  FROM
    (SELECT
      enrollment_dim.course_id
      , enrollment_dim.course_section_id
      , enrollment_dim.user_id
      , user_dim.canvas_id AS canvas_user_id
      , user_dim.sortable_name AS teacher_name
      , ROW_NUMBER() OVER (PARTITION BY type, user_id ORDER BY enrollment_dim.created_at ASC) first_teacher
    FROM enrollment_dim
      JOIN user_dim ON enrollment_dim.user_id = user_dim.id
    WHERE type = 'TeacherEnrollment'
    ) x
  WHERE first_teacher = 1

)

SELECT
  course_dim.id
  , course_dim.canvas_id AS canvas_course_id
  , course_dim.name AS course_name
  , assignment_dim.title AS assignment_title
  , teacher_name
FROM assignment_dim
  JOIN course_dim ON assignment_dim.course_id = course_dim.id
  JOIN teachers ON teachers.course_id = course_dim.id‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Now, the want the URL of the assignment, can be tricky. It depends on the assignment and submission type, then joining onto those tables, and quite possibly having to format your own strings to replicate the URL from routes and ids.

Are you also looking for the submissions of these assignments? In that case, you'd basically take that query and SELECT FROM submission_dim, JOIN ON assignment_dim and the rest, with a second JOIN on user_dim AS students, and joining submission_dim.user_id = user_dim.id to get the student name.

-- mssql
SELECT
  course_dim.id
  , course_dim.canvas_id AS canvas_course_id
  , course_dim.name AS course_name
  , assignment_dim.title AS assignment_title
  , teacher_name
  , students.sortable_name
FROM submission_dim
  JOIN assignment_dim ON assignment_dim.id = submission_dim.assignment_id
  JOIN course_dim ON course_dim.id = assignment_dim.course_id
  JOIN teachers ON teachers.course_id = course_dim.id
  JOIN user_dim AS students ON students.id = submission_dim.user_id‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[nate_mcclennen]

Thanks - super helpful. I will work through this. In the assignment URL

element, we are not looking for the submission URL, but the URL to the

assignment itself. In the API documentation at doc/api/live I find:

html_url (string, optional): the URL to the assignment's web page

And the url link shows up when you select the generic out of the box report

"Outcome results".

However, I do not see this data in Canvas Data.

Thoughts?

Nate

[robotcars]

You can look into the Requests table and try to get the URL of assignment_id. But this will depend on a large volume of data, and only be available when once a student accesses the URL.

If it's not in Canvas Data, you can either separately pull that data from the api or try to duplicate them from the UI.

Live Events offers another option.

Asset Accessed and most events contain the URL of the request when the user accesses it.

Collecting these, would give you a URL for every assignment resource and type, but only help you as students access them. With enough rows you can probably assess the URL of every assignment type, or get the pattern, or JOIN directly on a reduced view. Using Live Events poses the same problem as Requests table in that, you won't have the data til a user touches it, but Live Events offers much more than Requests.

-- ex, using live events data stored in sql
SELECT
  url_meta
  , domain
  , context_id_meta
  , context_type_meta
  , asset_type
FROM asset_accessed
WHERE asset_type IN ('assignment', ...)‍‍‍‍‍‍‍‍
‍‍‍‍‍‍‍‍‍

https://community.canvaslms.com/docs/DOC-17354-awesome-canvaslms#CanvasLiveEvents