skip to Main Content

I’m trying to implement an efficient search on street names.
A street has multiple residences (houses). The search I’m trying to build is street names LIKE %example% within the given city_id.
The result must include the LIKE list of matching street name, and the name of the city it belongs to (with the given city_id in $request).

Some things that I have tried:

Search from the Residence table:

$residences = FrResidence::where('city_id', $request->city_id)->whereHas('street', function ($q) use ($request) {
    $q->where('name', 'LIKE', "$request->name%");
})->limit($limit)->get();

The issue with this approach is that I need unique street names. And as streets belong to multiple residences I get the same results multiple items.

The other thing I have tried is to search LIKE from street, and find the matching city afterward. This works. However, requests can take up to 15 seconds (as the Residences table has 20+ million records):

// Find matching street
$streets = FrStreet::where('name', 'LIKE', "$request->name%")->limit($limit)->get();
$response = [];
foreach ($streets as $street) {
    // Find matching residence
    $filter = [['street_id', $street->id]];

    // Add optional filters
    if ($request->has('city_id')) {
        array_push($filter, ['city_id', $request->city_id]);
    }

    $residence = FrResidence::where($filter)->first();
    if (!empty($residence)) {
        array_push(
            $response,
            $searchResponseController->createStreetResult($country, [
                'name' => $street->name,
                'street_id' => $street->id,
                'cityName' =>  $residence->city->name,
                'cityId' => $residence->city->id
            ])
        );
    }
}

What is the best and fastest way to do this?

The database setup I have is:
Street has many Residences

Street model:

protected $fillable = [
    'id',
    'name',
    'externalIdentifier'
];

public function residences()
{
    return $this->hasMany('AppModelsAddressesfrResidence', 'street_id', 'id');
}

City model:

protected $fillable = [
    'id',
    'name'
];

public function residences()
{
    return $this->hasMany('AppModelsAddressesfrResidence', 'city_id', 'id');
}

Residence model

protected $fillable = [
    'id',
    'number',
    'postalcode_id',
    'street_id',
    'city_id',
];

public function street()
{
    return $this->belongsTo('AppModelsAddressesfrStreet', 'street_id', 'id');
}

public function city()
{
    return $this->belongsTo('AppModelsAddressesfrCity', 'city_id', 'id');
}

2

Answers


  1. public function search($something)
    {
        $variable = Model::where('something','like','%'.$something.'%')->get();
        return $variable;
    }
    

    try this

    Login or Signup to reply.
  2. I’m not entirely sure I understand the question but have you tried looking into the
    ->distinct() method on the first query you mention? https://laravel.com/docs/9.x/queries

    If the second query you mention works as you want it to, but it is just too slow, have you tried adding an index to your table? https://www.youtube.com/watch?v=VcLfF7TwII8

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search