Illuminate allows you to create dynamic and static groups of students and save those to what we call a “student group”.

Student groups are tracked in the groups schema, and more specifically the groups.groups table.

groups.students

This view makes it simpler to identify which students are in a particular student group.

Columns

SELECT
  group_id,
  student_id,
  start_date,
  end_date
FROM groups.students
LIMIT 10

Gotchas

  • end_date will be NULL if the student has not exited the student group.
  • start_date and end_date will be '1969-12-31' and '3000-01-01' if the student is always in the student group. For example, this is possible if the student group is linked to a custom report.

Examples

-- Get a list of student groups for a student
SELECT
  group_id,
  group_name
FROM groups.groups
WHERE group_id IN (
  SELECT group_id
  FROM groups.students
  WHERE student_id = ? -- replace ? a student_id
    AND start_date <= current_date
    AND (current_date <= end_date OR end_date IS NULL)
)
LIMIT 10

-- Get a list of students in a student group
SELECT
  student_id,
  local_student_id,
  last_name,
  first_name
FROM students
WHERE student_id IN (
  SELECT student_id
  FROM groups.students
  WHERE group_id = ? -- replace ? with a group_id
    AND start_date <= current_date
    AND (current_date <= end_date OR end_date IS NULL)
)
ORDER BY last_name, first_name
LIMIT 50

Want some more examples? Send me some suggestions of the data you would like to see zach@illuminateed.com