Apache Age Query

Question

I am building an educational application and one of its features is at once student can only be enrolled in 5 courses so for that purpose I need to create a trigger which executes once the student enrolls in the 6th course. Basically the trigger should implement the methodology of removing oldest course so it needs to make sure that it gets the timestamp of each course and removes the edge from the student to course once it finds the oldest enrolled course to ensure that student is enrolled in only 5 courses at any time.

I want the query for that trigger in Apache Age.

Answer 1

You will have to define a function to for the trigger like:

CREATE OR REPLACE FUNCTION check_course()
RETURNS TRIGGER AS $$
BEGIN
  IF (
    SELECT COUNT(*) FROM student_course WHERE s_id = NEW._id
  ) > 5 THEN
    DELETE FROM student_course
    WHERE s_id = NEW.s_id
    AND c_id = (
      SELECT c_id FROM student_course
      WHERE s_id = NEW.s_id
      ORDER BY timestamp ASC
      LIMIT 1
    );
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Then you can attach this function to your trigger as:

CREATE TRIGGER remove_course_trigger
AFTER INSERT ON student_course
FOR EACH ROW
EXECUTE FUNCTION check_course();

Answer 2

I think a trigger that doesn't allow creating a 6th relation would be better, because deleting a relation between student and course will erase any evidence of that student's enrollment. Something like this would prevent a 6th enrollment:

CREATE FUNCTION graph_name.check_rows() RETURNS trigger
   LANGUAGE plpgsql AS
$function$
BEGIN
   IF (SELECT count(*)
       FROM cypher('graph_name', $$
       MATCH (a:student)-[e]-(c:course)
       WHERE a.student_id = NEW.student_id
       RETURN COUNT(e)
       $$) AS (count agtype)) >= 5
   THEN
      RAISE EXCEPTION 'the student is already enrolled in 5 courses';
   END IF;
 
   RETURN NEW;
END;
$function$;
 
CREATE TRIGGER check_rows BEFORE INSERT ON graph_name.edge_label_name
   FOR EACH ROW EXECUTE PROCEDURE graph_name.check_rows();

You can add additional conditions, such as checking the enrollment status to ensure it is 'open,' indicating that the student is currently taking classes for that course and has not yet graduated

Answer 3

You can try this:

CREATE TRIGGER enforce_course_limit
AFTER INSERT ON enrollments
FOR EACH ROW
EXECUTE FUNCTION remove_oldest_course();

CREATE FUNCTION remove_oldest_course() RETURNS TRIGGER AS $$
DECLARE
    student_id INTEGER;
    course_id INTEGER;
BEGIN
    student_id := NEW.student_id;
    course_id := NEW.course_id;

    -- Check if the student is already enrolled in 6 courses
    IF (SELECT COUNT(*) FROM enrollments WHERE student_id = NEW.student_id) > 5 THEN
        -- Remove the oldest enrolled course for the student
        DELETE FROM enrollments
        WHERE (student_id, course_id) IN (
            SELECT student_id, course_id
            FROM enrollments
            WHERE student_id = NEW.student_id
            ORDER BY enrollment_date ASC
            LIMIT 1
        );
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Answer 4

You can utilize trigger to achieve this. The trigger should execute a function that checks the number of courses already enrolled for the student. If the count exceeds 5, it will remove the oldest enrolled course, thus maintaining the limit of 5 courses per student.

CREATE OR REPLACE FUNCTION check_course()
RETURNS TRIGGER AS $$
BEGIN
  IF (
    SELECT COUNT(*) FROM enrollments WHERE student_id = NEW.student_id
  ) > 5 THEN
    DELETE FROM enrollments
    WHERE student_id = NEW.student_id
    AND course_id = (
      SELECT course_id FROM enrollments
      WHERE student_id = NEW.student_id
      ORDER BY enrollment_date ASC
      LIMIT 1
    );
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER enforce_course_limit
AFTER INSERT ON enrollments
FOR EACH ROW
EXECUTE FUNCTION check_course();

Answer 5

Here is a pseudo-code for the trigger and function:

CREATE OR REPLACE FUNCTION maintain_course_limit()
RETURNS TRIGGER AS $$
DECLARE
  oldest_enrollment_id INTEGER;
BEGIN
  -- Determine the courses a student is enrolled in
  WITH course_enrollments AS (
    SELECT *
    FROM edges  -- replace with your edge table
    WHERE source_vertex_id = NEW.source_vertex_id AND edge_label = 'course_enrollment'  -- replace with your label
  )
  SELECT target_vertex_id INTO oldest_enrollment_id
  FROM course_enrollments
  WHERE edge_properties->'timestamp' = (SELECT MIN(edge_properties->'timestamp') FROM course_enrollments)
  LIMIT 1;

  -- If the student is enrolled in more than 5 courses, remove the enrollment for the oldest course
  IF (SELECT COUNT(*) FROM course_enrollments) > 5 THEN
    DELETE FROM edges  -- replace with your edge table
    WHERE source_vertex_id = NEW.source_vertex_id AND target_vertex_id = oldest_enrollment_id AND edge_label = 'course_enrollment';  -- replace with your label
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER course_limit_trigger
BEFORE INSERT ON edges  -- replace with your edge table
FOR EACH ROW
WHEN (NEW.edge_label = 'course_enrollment')  -- replace with your label
EXECUTE FUNCTION maintain_course_limit();

Answer 6

You can try this, I hope it works for you

CREATE TRIGGER enroll_trigger
AFTER INSERT ON enrollments
REFERENCING NEW ROW AS new_row
FOR EACH ROW
BEGIN
    DECLARE student_count INTEGER;
    DECLARE oldest_enrollment_timestamp BIGINT;

    SELECT COUNT(*) INTO student_count
    FROM enrollments
    WHERE student_id = new_row.student_id;

    IF student_count > 5 THEN
        SELECT enrollment_timestamp INTO oldest_enrollment_timestamp
        FROM enrollments
        WHERE student_id = new_row.student_id
        ORDER BY enrollment_timestamp
        LIMIT 1;

        DELETE FROM enrollments
        WHERE student_id = new_row.student_id
        AND enrollment_timestamp = oldest_enrollment_timestamp;
    END IF;
END;