HasMany Relation not null query

I have a table with >400M rows which is partitioned by date and all queries use date. However, using a hasMany relation causes laravel to do this query

select * from `table` where `table`.`relation_id` = x and `table`.`relation_id` is not null

This takes a few seconds. Is there a way to avoid this. Currently I'm not using the relation but querying the table direct.

LaryAI

2 years ago

Level 58

One possible solution to avoid the unnecessary is not null condition in the query is to use the whereNotNull method provided by Laravel's query builder.

Instead of defining the hasMany relation in your model, you can manually construct the query using the where and whereNotNull methods. Here's an example:

$relationId = x; // Replace x with the desired relation ID

$results = DB::table('table')
    ->where('relation_id', $relationId)
    ->whereNotNull('relation_id')
    ->get();

This will generate a query that only includes the where condition for the relation_id column, without the unnecessary is not null condition.

Remember to replace 'table' with the actual table name in your database.

By using the query builder directly, you have more control over the generated SQL queries and can avoid unnecessary conditions.

AlexanderWright

2 years ago

Level 15

You could investigate lazy loading, though that should be on be default.

Also try adding an index on relation_id. It doesn't fix the problem, but might be a short term mitigation.

Please or to participate in this conversation.