Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

andrewblackwell's avatar

Custom foreign key in relation cast as integer instead of string?

I need to link two tables together using a custom foreign key on both tables, as follows:

public function image_assets()
    {
        return $this->hasMany('App\Models\Asset', 
								'unique_serial_id', 
								'unique_serial_id');
    }

However, the problem is that the resulting query treats 'unique_serial_id' as a string instead of an integer, which slows the query down no end (the underlying field is an integer). I can see from Clockwork that the query looks as follows:

SELECT `unique_serial_id`, `updated_at`, `s3` FROM `assets` WHERE `assets`.`unique_serial_id` in ('13934', '18249', '18250', '18251', '18252', '18253', ...) //there are ca 1000 entries, so I won't list them here.

No amount of indexing will speed this query up (it's currently at 1700ms!). However, it would no doubt be much faster if the resulting value was queried as an integer, matching the column type, as follows:

SELECT `unique_serial_id`, `updated_at`, `s3` FROM `assets` WHERE `assets`.`unique_serial_id` in (13934, 18249, 18250, 18251,18252, 18253, ...)

Is there any way to enforce custom foreign keys to be treated as integers in Laravel?

(Update: I also noticed something else that is strange: when I copy the query from Clockwork into TablePlus, it executes the query in 75ms (which is fine), but in php it takes 1700ms.)

0 likes
5 replies
Sinnbeck's avatar

Untested but can you try setting it as a cast on both models?

protects $casts = [
    'unique_serial_id' => 'integer'
];
andrewblackwell's avatar

@Sinnbeck Thanks, that didn't make any difference though, I'm afraid.

I also noticed something else that is strange: when I copy the query from Clockwork into TablePlus, it executes the query in 75ms (which is fine), but in php it takes 1700ms.

Sinnbeck's avatar

@andrewblackwell Did you add it to both models? Are you sure that is the query that is slow? Are you looking under the databases tab or the performance tab?

andrewblackwell's avatar

@Sinnbeck I tried casting the field on both models, still no difference. Both under the performance and the databases tab it's telling me that the query takes over 1500ms, but I'm beginning to wonder if it isn't Clockwork misreading the query duration, and that it's actually underlying calculations in php that are taking time. I'll try and measure with some other tools too. Thanks for your feedback!

Sinnbeck's avatar

@andrewblackwell a small tip. Copy it to a test route in web.php and run it. That way you can quickly run the query in isolation

1 like

Please or to participate in this conversation.