Context: Synchronizing data with Microsoft's Graph API
Problem: Items have immutable ids that are case sensitive. There are many occurrences where unique items have the exact same Id, except for a single character having a different casing.
i.e.: (notice the 4th last character)
Item #1: EWg0AQbLDgfAFWEaaZsFuzmolNQAAAADDIQAA
Item #2: EWg0AQbLDgfAFWEaaZsFuzmolNQAAAADDiQAA
When using updateOrCreate() the underlying where() method generates a query against MySQL that returns a case insensitive result set.
Normally, I can get around this using whereRaw() and parameter binding, such as:
$id = 'casE seNsitive id';
Model::whereRaw("BINARY `graph_immutable_id`=?", [$id])->first();
However, for convenience, I would like to apply the same condition to updateOrCreate().
If I chain the above whereRaw() condition before updateOrCreate() it seems like it solves the issue:
Model::whereRaw("BINARY `graph_immutable_id`=?", [$id])
->updateOrCreate([], [$arrayOfProperties]);
From what I gather by skimming over the code base, this works because Builder will just add the extra condition onto the query, and I end up with the unique model.
Does anyone see any issue with this approach? Is there another way of going about this that I haven't thought of? (besides performing the raw query and then writing the code to either update or create manually)
Thanks
I think this is your only option query wise. However, this is the correct solution. This won't be any issue.
Another solution is actually creating a binary column. This way you force it to always be case sensitive. At least, that's what I know of it. You can try this in your migration for that:
DB::statement('ALTER TABLE models ADD graph_immutable_id binary(16)');
Also see first paragraph here https://dev.mysql.com/doc/refman/8.0/en/case-sensitivity.html
Binary is correct. That is for Mysql. Probably in other db's as well.
just use updateOrCreate
// // Create or retrieve the role
// $role = Role::firstOrCreate(['name' => $roleName]);
// Create or retrieve the role - FIXED: updateOrCreate to handle name changes
$role = Role::updateOrCreate(
['name' => $roleName], // Search by exact name
['name' => $roleName] // Update name if found
);
@bloodykheeng Your first post is a reply to a thread that’s half a decade old, and doesn’t even address the issue presented in the original post (case sensitivity whilst using updateOrCreate).
For anyone finding this thread and needing a solution, the cleanest way is to use a case-sensitive collation on the column. For example, in MySQL:
$table->string('foo')->unique()->collation('utf8mb4_0900_as_cs');
There's no need for binary types. For reference, as = accent-sensitive, cs = case-sensitive.
Please or to participate in this conversation.