CakePHP: How to efficiently enforce unique join table records

Question

I'm using CakePHP 2.x and I frequently run into situations where I want to add records to a join table, but I want to make sure the same "join" record does not already exist.

For concreteness, here's a basic example...

Model: Student hasAndBelongsToMany Course(s)
Model: Course hasAndBelongsToMany Student(s)
Join Table = courses_students and has fields "student_id" and "course_id"

Let's say I have a form with a bunch of students that I want to associate with a course. The form submits a bunch of student_id(s) to the controller. Before I add the new records, I need to make sure the records don't already exist (because you can't enroll for the same course twice). This is where things get messy...

Option 1: I sometimes build a deleteAll() for every (possibly) pre-existing record in the join table that has the current course_id and any of the submitted user_id(s). This is efficient, but the downside is that it erases any pre-existing data that may have been stored in other fields, such as the date etc.

Option 2: I sometimes loop over each of the submitted records and do a find() for each (possibly) pre-existing record. This allows me to preserve existing records, but makes a bunch of extra queries to the DB.

These are the two ways I typically handle this situation, but I suspect there is a better way. What is it?

Rudie Rudie · Accepted Answer · 2013-11-21T21:16:41

You could use REPLACE INTO. For example:

REPLACE INTO courses_students (course_id, student_id, when)
  VALUES (1, 2, 12345678)

Which would overwrite column when with the new value. I'm sure there's a way to not do that in pure SQL though: http://dev.mysql.com/doc/refman/5.0/en/replace.html

Also not sure how that would work in CakePHP. Their DBAL probably has a helper for db merges.

(MySQL doesn't support MERGE INTO .. WHEN MATCHED, so you'll have to do a SELECT and then INSERT anyway.)

(Btw: option 2 isn't inefficient, because you'll have created a composite, unique key for course_id + student_id. The SELECT will be super fast.)

edit
SELECTs are very, very fast and cheap if you index your columns correctly. Table courses_students should have a PK on (course_id, student_id). You can do all the SELECTs in 1 query, if you're worried about the number of queries:

:ids from input
SELECT course_id, student_id FROM courses_students WHERE course_id IN (:ids) AND student_id IN (:ids)

And then map them to an assoc accessible array:

array(course_id => array(student_id => TRUE))

And then loop through the input (POST?) and filter existing records:

if ( !isset($map[$course_id][$student_id]) ) {
  // INSERT HERE
}

For a total of count(INPUT) + 1 queries.

But all of that is a lot of code. You can just loop and query per record. I promise it's cheap.

CakePHP: How to efficiently enforce unique join table records

1 Answers