CD2: Query Repository / Sharing

[DanL]

I thought I'd start a thread for posting Canvas Data 2 queries we've found useful, and not being a DBA myself, for review/feedback if you have found better ways of getting the same data.

Users Query, including pseudonym/communication channel lookup (login info, email address/es, etc.):

SELECT u.id,
       u.created_at,
       u.updated_at,
       u.workflow_state,
       u.sortable_name,
       u.name,
       u.time_zone,
       u.avatar_image_url,
       u.avatar_image_source,
       u.short_name,
       u.last_logged_out,
       u.pronouns,
       u.merged_into_user_id,
       p.deleted_at,
       p.integration_id,
       p.created_at  pcreated_at,
       p.updated_at  pupdated_at,
       p.sis_batch_id,
       p.unique_id   NetID,
       p.login_count,
       p.failed_login_count,
       p.last_request_at,
       p.last_login_at,
       p.current_login_at,
       p.last_login_ip,
       p.current_login_ip,
       p.sis_user_id EmplID,
       c.path        email
FROM   canvas.users u,
       canvas.pseudonyms p,
       canvas.communication_channels c
WHERE  u.id = p.user_id
       AND u.id = c.user_id
       AND c.path_type = 'email'
       AND c.position = 1
ORDER  BY sortable_name ASC 

[DanL]

Enrollments by User Query:

SELECT p.unique_id       User_ID,
       p.sis_user_id     EmplID, /* we are a Peoplesoft campus - use your own alias here */
       c.id              course_id,
       c."name"          course_name,
       c.workflow_state  course_wflow,
       c.sis_source_id   course_sis_id,
       cs.id             sec_id,
       cs.sis_source_id  sec_sis_id,
       cs.workflow_state sec_wflow,
       e.workflow_state  enroll_wflow,
       r."name"          role_name,
       r.base_role_type,
       r.workflow_state  role_wflow,
       u.NAME            user_name,
       t.NAME            term_name,
       t.sis_source_id   term_id,
       a.NAME            acct_name,
       a.sis_source_id   acct_sis_id
FROM   canvas.users u,
       canvas.pseudonyms p,
       canvas.enrollments e,
       canvas.courses c,
       canvas.course_sections cs,
       canvas.enrollment_terms t,
       canvas.accounts a,
       canvas.enrollment_states es,
       canvas.roles r
WHERE  u.id = p.user_id
       AND e.user_id = u.id
       AND e.course_section_id = cs.id
       AND c.id = cs.course_id
       AND e.id = es.enrollment_id
       AND e.role_id = r.id
       AND t.id = c.enrollment_term_id
       AND a.id = c.account_id
       AND a.workflow_state = 'active'
       AND p.unique_id = '$CanvasUser' /* campus userid here */
ORDER  BY c.sis_source_id,
          p.unique_id,
          c.sis_source_id 

[DanL]

For campuses that don't set start/end dates via SIS feed at the course level except when providing "extended access" after a term ends, this query might be useful to find those exceptions:

Courses with a non-null start_at date

SELECT cs.course_id,
       cs.id           sec_id,
       c.sis_source_id course_id,
       cs.NAME         sec_name,
       cs.start_at,
       cs.end_at
FROM   course_sections cs,
       courses c
WHERE  cs.start_at IS NOT NULL
       AND c.id = cs.course_id
ORDER  BY c.sis_source_id 

[IanGoh]

In the sql world, we'd do joins, e.g.,

SELECT
:
FROM canvas.users u
LEFT JOIN canvas.pseudonyms p
   ON u.id = p.user_id
LEFT JOIN canvas.communication_channels c
   ON u.id = c.user_id
      AND c.path_type = 'email'
      AND c.position = 1

[DanL]

Yup, that's definitely better.

I'm very open to improvements. 😀