Linking two tables by phone numbers where one is normalized and one isn't

I'm working on setting up a relation between two tables. Each table is full of data and the challenge is I have a table.

people_meta_phone.phone_number that is full of numbers like: +1 (705) 474-1234 705.123.4567 1 705 123.1567 all kinds of variations.

I have another table. sms_shortcode_preferences.number that is always 17051234567 and there may be more than one of these but I only want the most recent based on 'created_on'

I need PeopleMetaPhone to have a relationship to SmsShortcodePreference.

I've been at it for a day and have made no headway.

I can write raw SQL that will do it, but when I have public function smsShortcodePreferences(): HasOne { return $this->hasOne(SmsShortCodePreference::class, 'number', 'normalized_phone_number') ->orderByDesc('sms_shortcode_preferences.created_on'); }

public function getNormalizedPhoneNumberAttribute(): array|string
{
    $number = $this->phone_number;
    $normalized = str_replace(['-', ' ', '(', ')', '+', '.'], '', $number);

    // Prepend '1' if the number does not start with '1'
    if (!str_starts_with($normalized, '1')) {
        $normalized = '1' . $normalized;
    }

    return $normalized;
}

and try and run this test:

public function testVerifyRelationship()
    {
        $peopleMetaPhone = PeopleMetaPhone::factory()->create([
            'oid' => $this->user->oid,
            'phone_type' => 'mobile',
            'phone_number' => '(555) 555-5555',
            'is_private' => 0,
            'do_not_text' => 0,
        ]);
        
        $smsShortcodePreference = SmsShortcodePreference::factory()->create([
            'oid' => $this->user->oid,
            'name' => 'opt-in',
            'number' => '15555555555', // Unformatted version
        ]);


        $peopleMetaPhone->load('smsShortcodePreferences');

        dd($peopleMetaPhone->toArray());
    }

the relationship is always null

LaryAI

1 year ago

Level 58

To solve the issue of linking the two tables by phone numbers where one is normalized and the other isn't, you need to ensure that the phone numbers are consistently formatted before attempting to establish the relationship. Here's a step-by-step solution:

Normalize the Phone Numbers: Ensure that both phone numbers in people_meta_phone and sms_shortcode_preferences are normalized to the same format. You've already started this process in your getNormalizedPhoneNumberAttribute method. Make sure this method is correctly defined in your PeopleMetaPhone model.
Adjust the Relationship Method: In your PeopleMetaPhone model, ensure that the relationship method uses the normalized phone number. You should use an accessor to get the normalized phone number for the relationship.
Use Accessor for Normalized Phone Number: Make sure your accessor is correctly defined and used in the relationship. Here's how you can adjust your code:

// In PeopleMetaPhone model

public function getNormalizedPhoneNumberAttribute(): string
{
    $number = $this->phone_number;
    $normalized = str_replace(['-', ' ', '(', ')', '+', '.'], '', $number);

    // Prepend '1' if the number does not start with '1'
    if (!str_starts_with($normalized, '1')) {
        $normalized = '1' . $normalized;
    }

    return $normalized;
}

public function smsShortcodePreferences(): HasOne
{
    return $this->hasOne(SmsShortcodePreference::class, 'number', 'normalized_phone_number')
                ->orderByDesc('sms_shortcode_preferences.created_on');
}

Test the Relationship: Ensure your test is correctly set up to verify the relationship. Here's how you can adjust your test:

public function testVerifyRelationship()
{
    $peopleMetaPhone = PeopleMetaPhone::factory()->create([
        'oid' => $this->user->oid,
        'phone_type' => 'mobile',
        'phone_number' => '(555) 555-5555',
        'is_private' => 0,
        'do_not_text' => 0,
    ]);

    $smsShortcodePreference = SmsShortcodePreference::factory()->create([
        'oid' => $this->user->oid,
        'name' => 'opt-in',
        'number' => '15555555555', // Ensure this matches the normalized format
    ]);

    $peopleMetaPhone->load('smsShortcodePreferences');

    dd($peopleMetaPhone->toArray());
}

Verify the Output: When you run the test, ensure that the smsShortcodePreferences relationship is not null. If it is, double-check that the phone numbers are being normalized correctly and that the relationship is defined with the correct keys.

By ensuring that both phone numbers are consistently formatted and that the relationship is correctly defined, you should be able to establish the link between the two tables.

Please or to participate in this conversation.