SPresnac's avatar

HasOne relation with lowercase compare?

Hej,

I do have a hasOne relation like this

    public function employee(): HasOne
    {
        return $this->hasOne(
            related: Employee::class,
            foreignKey: 'username',
            localKey: 'username',
        );
    }

Problem is: The "Employee" class is extern (like in an external DB, I do have only read access). Some Usernames are written in lowercase, some are mixed and I do not know when applies what.

So, back in the days, I would make a lowercase compare on both sides and the problem would have fixed with that. But how do I do this with eloquents relation classes?

Simply setting the "foreignKey" to "lower('username')" surely is not working. How to fix this?

0 likes
4 replies
Snapey's avatar

You might be able to do this with Sushi but it won't be very efficient. https://github.com/calebporzio/sushi

My suggestion is to not follow this line of thought. Query the external system outside of eloquent and merge the results.

Snapey's avatar

Alternate strategies

  • add a view to the external system which is the same data but with the username field converted to lowercase

  • Have a job that regularly syncs data from the external database to a local copy, in the format you can use. For this you need to understand how up to date the data needs to be.

1 like
Glukinho's avatar

Extend \Illuminate\Database\Eloquent\Relations\HasOne class and apply your own key comparison logic? I personally don't feel I would do it myself, but consider this option.

update: it seems you should rewrite compareKeys() in \Illuminate\Database\Eloquent\Relations\Concerns\ComparesRelatedModels trait: https://github.com/laravel/framework/blob/a7e7c8c22cdd16819fc697059df6b0e840490dfe/src/Illuminate/Database/Eloquent/Relations/Concerns/ComparesRelatedModels.php#L65

Just add Str::lower(...) to both keys, seems pretty easy but I'm not sure is it enough or are there any pitfalls.

Maybe KISS principle in this case would be to treat external system as external system and not involve Eloquent at all.

martinbean's avatar

@spresnac There’s multiple things you should be doing here:

  • Not using a case-sensitive column type if data should be case-insensitive.
  • Normalising usernames when they’re saved (e.g. saving them as all-lowercase).
  • Adding keys to your tables (so a unique constraint on your username column, and then foreign keys so that your employee records actually point to a valid row instead of just doing a match on random username strings).
1 like

Please or to participate in this conversation.