Mar 19, 2026

Level 27

Relationships using LIKE

I've got a Model with an id in three parts

'id' => 'FIRSTNAME_SECONDNAME_TITLE'

I've got a second model where I have matching foreign_ids but the data that goes in there has wildcards.

'name_id'=> '???_SMITH_??'

I want to be able to set up a relationship when all the parts that are filled in match but the wildcards are ignored.

I think I need subQueries in my hasMany method but can't work out how to do it.

In my Name Model

	public function relatedNames(): HasMany
	{
			return $this->hasMany(RelatedName::class, 'name_id', 'id')
			->orWhere(function ($query) {
				$query->where(function ($query) {
					$query->where('firstname', $this->firstname)
						->orWhere('firstname', '!==', '???');
				})
					->where(function ($query) {
						$query->where('lastname', $this->lastname)
							->orWhere('lastname', '!==', '???');
					})
					->where(function ($query) {
						$query->where('title', $this->title)
							->orWhere('title', '!==', '??');
					});
			});
}

In this demo every firstname and last name has the same amount of characters and they are in the database as stored generated columns.

Relationships don't seem to allow orWhere methods on them. How do I made this relationship include these partial matches?

LaryAI

1 month ago

Level 58

Certainly! The problem here is that you want to set up a relationship where the "foreign" key can have wildcards (using ?) and you want to match those to the parts of an id column in another model that don't have wildcards—at the database relationship (Eloquent) level if possible.

Relationships in Laravel typically use exact matches, but in your case, you need pattern matching (using LIKE) with support for ? as a single-character wildcard.

Solution Outline

Storing parts in generated columns makes this easier. We'll assume your table has columns for firstname, lastname, title for both models.
You can't use orWhere or an advanced query directly inside an Eloquent relationship's definition. However, you can use a custom relationship with hasMany and add extra constraints using ->where(...) or ->whereRaw(...).
You can't directly use ? as a wildcard in SQL; you must convert ? to _ (underscore), the single-character wildcard in SQL LIKE.

Example Solution

Let's assume your RelatedName model has columns:

name_id: string, e.g. '???_SMITH_??'
firstname, lastname, title as stored generated columns

Step 1: Add an Accessor to Convert `?` to `_` in Name Model

public function namePattern()
{
    // Converts THIS model's id into a SQL LIKE pattern, replacing '?' with '_'
    return str_replace('?', '_', $this->id);
}

Step 2: Custom Relationship Definition

Because we want RelatedNames whose pattern matches the Name's ID, but the pattern with _ wildcards, we use whereRaw with a LIKE.

public function relatedNames()
{
    return RelatedName::query()
        ->whereRaw('? LIKE REPLACE(name_id, "?", "_")', [$this->id]);
}

This makes the relationship: return all RelatedNames WHERE the pattern in name_id, with '?' replaced by '_' (SQL wildcard), matches this model's id.

If you want this as an actual relationship property, you can use:

public function relatedNames()
{
    return $this->hasMany(RelatedName::class, 'name_id', 'id')
        ->whereRaw('? LIKE REPLACE(name_id, "?", "_")', [$this->id]);
}

But this will run the exact same query.

Step 3: Usage

$name = Name::find(1);
$related = $name->relatedNames; // Will contain all related names matching the pattern

Summary

Store patterns as ???_SMITH_?? in name_id of RelatedName.
On the Name model, define the relationship using whereRaw + LIKE, replacing ? with _.
This tells Eloquent to match Name's id against the wildcard pattern.

Full Example

In Name model:

public function relatedNames()
{
    return $this->hasMany(RelatedName::class, 'name_id', 'id')
        ->whereRaw('? LIKE REPLACE(name_id, "?", "_")', [$this->id]);
}

Usage:

$relatedNames = $name->relatedNames;  // Now includes wildcard matches

Note: If you have the parts as columns, adjust the whereRaw to build the LIKE condition per part.

Let me know if you want a version that splits and matches by firstname, lastname, and title columns!

DNABeast

1 month ago

Level 27

Alas this doesn't work. I don't have identical id to foreign_id matching so any relationship call can only filter down.

I need to replace the relationship check that looks for id = foreign_id with a custom solution that checks for id LIKE partial-id

DNABeast

1 month ago

Best Answer

Level 27

This was done by creating a custom relationship.


namespace App\Relations;

use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Collection;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\Relation;

// This was put together by Windsurf.
class LikeRelationship extends Relation
{
    protected $localKey;
    protected $foreignKey;

    /**
     * Create a new relationship instance.
     *
     * @param  \Illuminate\Database\Eloquent\Builder  $query
     * @param  \Illuminate\Database\Eloquent\Model  $parent
     * @param  string  $foreignKey
     * @param  string  $localKey
     * @return void
     */
    public function __construct(Builder $query, Model $parent, string $foreignKey, string $localKey)
    {
        $this->foreignKey = $foreignKey;
        $this->localKey = $localKey;

        parent::__construct($query, $parent);
    }

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

            if ($parentValue === null) {
                $this->query->whereRaw('1 = 0');
            } else {
                $this->query->whereRaw('? LIKE ' . $this->foreignKey, [$parentValue]);
            }
        }
    }

    /**
     * Set the constraints for an eager load of the relation.
     *
     * @param  array  $models
     * @return void
     */
    public function addEagerConstraints(array $models)
    {
        $keys = $this->getKeys($models, $this->localKey);

        if (empty($keys)) {
            $this->query->whereRaw('1 = 0');
            return;
        }

        // For LIKE relationships, we need to use OR conditions
        $this->query->where(function ($query) use ($keys) {
            foreach ($keys as $key) {
                $query->orWhereRaw('? LIKE ' . $this->foreignKey, [$key]);
            }
        });
    }

    /**
     * Initialize the relation on a set of models.
     *
     * @param  array  $models
     * @param  string  $relation
     * @return array
     */
    public function initRelation(array $models, $relation)
    {
        foreach ($models as $model) {
            $model->setRelation($relation, $this->related->newCollection());
        }

        return $models;
    }

    /**
     * Match the eagerly loaded results to their parents.
     *
     * @param  array  $models
     * @param  \Illuminate\Database\Eloquent\Collection  $results
     * @param  string  $relation
     * @return array
     */
    public function match(array $models, Collection $results, $relation)
    {
        foreach ($models as $model) {
            $parentValue = $model->getAttribute($this->localKey);
            
            $matching = $results->filter(function ($result) use ($parentValue) {
                $foreignValue = $result->getAttribute($this->foreignKey);
                return $this->matchesLikePattern($parentValue, $foreignValue);
            });

            $model->setRelation($relation, $matching);
        }

        return $models;
    }

    /**
     * Get the results of the relationship.
     *
     * @return mixed
     */
    public function getResults()
    {
        return $this->query->get();
    }

    /**
     * Get the key for comparing against the parent key in "has" query.
     *
     * @return string
     */
    public function getForeignKeyName()
    {
        return $this->foreignKey;
    }

    /**
     * Get the key for comparing against the parent key in "has" query.
     *
     * @return string
     */
    public function getLocalKeyName()
    {
        return $this->localKey;
    }

    /**
     * Get the parent's key value.
     *
     * @return mixed
     */
    public function getParentKey()
    {
        return $this->parent->getAttribute($this->localKey);
    }

    /**
     * Check if a value matches a LIKE pattern.
     *
     * @param  string  $value
     * @param  string  $pattern
     * @return bool
     */
    protected function matchesLikePattern($value, $pattern)
    {
        // Convert SQL LIKE pattern to regex pattern
        $regexPattern = str_replace(
            ['%', '_'],
            ['.*', '.'],
            preg_quote($pattern, '/')
        );
        
        return preg_match("/^{$regexPattern}$/i", $value);
    }
}

Glukinho

1 month ago

Level 30

It seems it is needless to say such structure is wrong, you probably know that already.

Did you try something like this? https://stackoverflow.com/questions/38608244/laravel-eloquent-query-where-like-relationship

Please or to participate in this conversation.

Relationships using LIKE

Solution Outline

Example Solution

Step 1: Add an Accessor to Convert ? to _ in Name Model

Step 2: Custom Relationship Definition

Step 3: Usage

Summary

Full Example

Step 1: Add an Accessor to Convert `?` to `_` in Name Model