jesselee's avatar

why laravel add an extra where condition "foreign key is not null" to hasOne/Many relation?

I don't think this is a big deal and I just wonder why.

let's just say, a product variant could hasOne(Price::class)

$variant->price will generate sql like this:

SELECT * 
FROM `prices` 
WHERE `prices`.`variant_id` = 17 and `prices`.`variant_id` is not null 
LIMIT 1

here is the code from Illuminate/Database/Eloquent/Relations/HasOneOrMany.php

    /**
     * Set the base constraints on the relation query.
     *
     * @return void
     */
    public function addConstraints()
    {
        if (static::$constraints) {
            $this->query->where($this->foreignKey, '=', $this->getParentKey());

            $this->query->whereNotNull($this->foreignKey);
        }
    }

I don't quite understand why laravel will add this extra "whereNotNull on foreign key" .

I find it redundant but I'm not 100% sure.

0 likes
6 replies
davorminchorov's avatar

Because there's an option to make a foreign key constraint nullable in the migration. I mean, the column.

jesselee's avatar

@Ruffles Thanks for your fast reply.

in your case, the $variant->price, the $variant itself could be null.

$variant = new App\Variant;
$variant->price()->toSql();

this will generate sql like this

"select * from `prices` where `prices`.`variant_id` is null and `prices`.`variant_id` is not null"

looks weird. well, doesn't make sense to me on a second thought.

ckdot's avatar

Any chance this is going to be changed in the near future? Imagine you've got a table "categories". Each category has a parent category, referenced by parent_id (BelongsTo parent_id > id). So each category can have child categories (HasMany id > parent_id). This will probably work fine. But what if you want to have siblings of a category. A good way would be to implement another HasMany relation (parent_id > parent_id). But this won't work for root categories (parent_id IS NULL) now as the query will be something like "select * from categories where categories.parent_id is null and categories.parent_id is not null". This is not intuitive behavior and should be changed imo.

Corban's avatar

But your example is using a One to One relationship. The whole thing makes sense to me since every time I have used a One to One relationship is because those tables need to be always related, so null is not a valid option.

Of course this is probably debatable since I don't have the same amount of experience that a lot of users here, but I think that the key problem is that you mistook hasOne and hasMany and (as me and I believe almost everyone here) wanted to satisfy your curiosity.

lepev's avatar

Old post, but as I had the case, I did some digging I finally went to the source directly, and discovered the reason here

hasMany extends HasOneOrMany, and this check is required in the HasOne case.

2 likes

Please or to participate in this conversation.