Cast local key in morphMany relationship before query
I am having a problem with a query in MySQL 8 as it's not using the index as it should. I have two tables, one is users and the other is conversions.
users (id bigint unsigned)
conversions (id bigint unsigned, actable_type varchar(191), actable_id varchar(36), INDEX actable_id)
Then, in the User class, I have the relationship defined as
public function conversions(): MorphMany
{
return $this->morphMany(Conversion::class, 'actable');
}
Here's how it performs:
> DB::enableQueryLog();
> $user = User::find(1);
> $user->conversions()->get();
> DB::getQueryLog();
= [
"query" => "select * from `conversions` where `conversions`.`actable_type` = ? and `conversions`.`actable_id` = ? and `conversions`.`actable_id` is not null",
"bindings" => [
"App\Models\User",
1,
],
"time" => 448.01,
],
> $user->conversions()->explain()
= Illuminate\Support\Collection {#8356
all: [
{#8252
+"id": 1,
+"select_type": "SIMPLE",
+"table": "conversions",
+"partitions": null,
+"type": "ALL",
+"possible_keys": "conversions_actable_id_index",
+"key": null,
+"key_len": null,
+"ref": null,
+"rows": 616308,
+"filtered": 5.0,
+"Extra": "Using where",
},
],
}
When I run the query manually where I pass the user id as a string, it performs like this:
> DB::enableQueryLog();
> Conversion::query()->where('actable_type', 'App\Models\User')->where('actable_id', '1')->get()
= [
"query" => "select * from `conversions` where `actable_type` = ? and `actable_id` = ?",
"bindings" => [
"App\Models\User",
"1",
],
"time" => 7.93,
],
> Conversion::query()->where('actable_type', 'App\Models\User')->where('actable_id', '1')->explain()
= Illuminate\Support\Collection {#8356
all: [
{#8381
+"id": 1,
+"select_type": "SIMPLE",
+"table": "conversions",
+"partitions": null,
+"type": "ref",
+"possible_keys": "conversions_actable_id_index",
+"key": "conversions_actable_id_index",
+"key_len": "145",
+"ref": "const",
+"rows": 1,
+"filtered": 10.0,
+"Extra": "Using where",
},
],
}
As you can see, when the actable_id is passed as an integer, it doesn't use the index, but when it's passed as a string, it uses.
I need the actable_id column to be a varchar(36) because sometimes the actables have UUIDs instead of bigints. How can I instruct Laravel, ideally in the User model class, to cast the User key to a string? I only want to do it for this relationship so it doesn't affect other relationships.
I can do the queries manually all the time, but having the relationship doing the right thing would simplify the code a lot.
Please or to participate in this conversation.