Efficient UUID for foreign keys

[davorminchorov]

Hi,

I am currently experimenting with foreign keys being uuids instead of ids and I was wondering if this package have any support for that.

The problem I am facing is when I have a belongsToMany relationship and try to eager load it using with('tags'), it returns empty results.

I couldn't find any info on that so far.

[michaeldyrynda]

Hey @davorminchorov - check the note in the laravel-model-uuid readme:

Whilst not recommended, if you do choose to use a UUID as your primary model key (id), be sure to configure your model for this setup correctly. Not updating these properties will lead to Laravel attempting to convert your id column to an integer, which will be cast to 0. When used in combination with laravel-efficient-uuid, this casting will result in a Ramsey\Uuid\Exception\InvalidUuidStringException being thrown.

I don't know for sure that this will fix the issue you're facing, as for performance reasons, it's not generally not recommended to use UUIDs as your primary key. As a result, we provide no specific level of support for it in either package.

[marshallthornton]

@davorminchorov Were you by chance also using the laravel-efficient-uuid package along with this one to store the data in the DB using binary columns? If so, and you are still looking for a possible solution on this, the reason it returns empty is because the UUID is being cast to a string in the where condition of the relation call which obviously doesn't match any of the binary data in the DB itself. You can see it adding the where condition in BelongsToMany.php in the Laravel codebase.

What we ended up doing when running into this issue while using both packages was to create a fake attribute in our model class, something like getIdRawAttribute() which then just returns $this->attributes['id']. Then when setting up the relation, we pass this fake attribute in for the $parentKey so that turns into something along the lines of $this->belongsToMany(Tag::class, null, null, null, 'id_raw') and then the relation seems to work since it gets the raw binary value when setting up the where condition on the relation query.

[davorminchorov]

@marshallthornton yeah, I was using the efficient uuid package as well in combo with the model uuid, but haven't investigated this since then.

Thanks for the detailed explanation, it's good to know that there's a possible solution.

I read about using Snowflake as a primary key recently which could be very similar to uuids but more efficient performance wise since they are integers instead of strings so I'll try it out soon.

[travisfisher]

@davorminchorov Were you by chance also using the laravel-efficient-uuid package along with this one to store the data in the DB using binary columns? If so, and you are still looking for a possible solution on this, the reason it returns empty is because the UUID is being cast to a string in the where condition of the relation call which obviously doesn't match any of the binary data in the DB itself. You can see it adding the where condition in BelongsToMany.php in the Laravel codebase.

What we ended up doing when running into this issue while using both packages was to create a fake attribute in our model class, something like getIdRawAttribute() which then just returns $this->attributes['id']. Then when setting up the relation, we pass this fake attribute in for the $parentKey so that turns into something along the lines of $this->belongsToMany(Tag::class, null, null, null, 'id_raw') and then the relation seems to work since it gets the raw binary value when setting up the where condition on the relation query.

You are a lifesaver @marshallthornton. I spent a lot of time trying to work around this issue and this approach solves the problem.

I wanted to provide some code samples for reference:

    /**
     * The attributes that should be cast.
     *
     * @var array
     */
    protected $casts = [
        'id' => EfficientUuid::class,
        'brand_id' => EfficientUuid::class,
        'instance_id' => EfficientUuid::class,
    ];

    /**
     * Get the raw id attribute.
     *
     */
    public function getRawIdAttribute()
    {
        return $this->attributes['id'];
    }

    /**
     * Get the raw brand id attribute.
     *
     */
    public function getRawBrandIdAttribute()
    {
        return $this->attributes['brand_id'];
    }

    /**
     * Get the raw instance id attribute.
     *
     */
    public function getRawInstanceIdAttribute()
    {
        return $this->attributes['instance_id'];
    }

    /**
     * @return Brand
     */
    public function brand()
    {
        return $this->belongsTo(Brand::class, 'raw_brand_id');
    }

    /**
     * @return Instance
     */
    public function instance()
    {
        return $this->belongsTo(Instance::class, 'raw_instance_id');
    }

[travisfisher]

@marshallthornton I am having issues with this approach when using eager loading:

use App\Models\Company;
use App\Models\Project;

/* Models */

$company = Company::find('ab472207-47ca-4a2e-a09d-9f0e31b7f882');
$company->brand; // Works with lazy loading, returns object
$company->contacts; // Works with lazy loading, returns array

/* Collections */

$company = Company::whereUuid('ab472207-47ca-4a2e-a09d-9f0e31b7f882')->firstOrFail();
$company->brand; // Works with lazy loading, returns object
$company->contacts; // Works with lazy loading, returns array

$company = Company::whereUuid('ab472207-47ca-4a2e-a09d-9f0e31b7f882')->with(['brand','contacts'])->firstOrFail();
$company->brand; // Does not work with eager loading, returns NULL
$company->contacts; // Does not work with eager loading, returns empty array

Below is the query log output, which shows eager loading using the "IN" method, instead of an "=" expression:

select * from `companies` where `companies`.`id` in (?) limit 1 [«G"GÊJ. Ÿ1·ø‚]
select * from `brands` where `brands`.`id` = ? limit 1 [Qö^å`ÜGU¸%b…@Ÿš]

select * from `companies` where `companies`.`id` in (?) limit 1 [«G"GÊJ. Ÿ1·ø‚]
select * from `brands` where `brands`.`id` = ? limit 1 [Qö^å`ÜGU¸%b…@Ÿš]

select * from `companies` where `companies`.`id` in (?) limit 1 [«G"GÊJ. Ÿ1·ø‚]
select * from `brands` where `brands`.`id` in (?) [Qö^å`ÜGU¸%b…@Ÿš]
select * from `contacts` where `contacts`.`company_id` in (?) [«G"GÊJ. Ÿ1·ø‚]

Any ideas on how to get that working with your approach?