Axeia

Can I recycle my earlier query to the WhereNotIn to avoid duplicates?

Posted 2 months ago by Axeia

Hello,

I have a 404 page for when people enter a /name in the url that isn't in my database it lists the names of the closest matches.

This is the code for finding results somewhat matching the given input.

            //Escapes underscores and percentage signs in case user enters these
            $likeEscapedName = $this->escapeLike($name);

            //Try to find by matching part of the string (that isn't the start or end).
            $queryLikePartOf = Pokemon::select(\DB::raw('name, "part_of" AS like_type'))
                ->where('name', 'NOT LIKE', $likeEscapedName.'%') //Doesn't start with
                ->where('name', 'NOT LIKE', '%'.$likeEscapedName) //Doesn't end on
                ->where('name', 'LIKE', '%'.$likeEscapedName.'%'); //Is part of

            //Try to find by seeing if the name starts with $name
            $queryLikeStartsWith = Pokemon::select(\DB::raw('name, "starts_with" AS like_type'))
                ->where('name', 'LIKE', $likeEscapedName.'%');

            //Try to find by seeing if the name ends on $name
            $queryLikeEndsOn = Pokemon::select(\DB::raw('name, "ends_on" AS like_type'))
                ->where('name', 'LIKE', '%'.$likeEscapedName);
                
            //Try to find by using "SOUNDS LIKE"
            $queryPhoneticallySimilar = Pokemon::select(\DB::raw('name, "sounds_like" AS like_type'))
                ->where('name', 'SOUNDS LIKE', $name);

                
            //The raw name is sometimes needed as levenshtein is a custom function that Laravel isn't aware of. 
            //Raw opens everything up to injection so its quoted/escaped.
            $escapedName = \DB::connection()->getPdo()->quote($name);
            $sqlLevenshtein = "levenshtein($escapedName, name)";
        
            //Try to find by levenshtein and union on the rest
            $queryLevenshteinAndUnionAll = Pokemon::select(\DB::raw('name, "name_looks_like" AS like_type'))
            ->where(\DB::raw($sqlLevenshtein), "<", 3)
            //If we already found it with SOUNDS LIKE, let's not get duplicates with levenshtein.
            ->whereNotIn('name', function($query) use($name){
                $query->select('name')
                    ->from('pokemons')
                    ->where('name', 'SOUNDS LIKE', $name);
            })
            ->orderBy(\DB::raw($sqlLevenshtein))
            ->unionAll($queryPhoneticallySimilar)
            ->unionAll($queryLikeStartsWith)
            ->unionAll($queryLikeEndsOn)
            ->unionAll($queryLikePartOf);

            //Send it all as one big query to the server
            $pokemons = $queryLevenshteinAndUnionAll->get();

Now my question is since $queryPhoneticallySimilar is basically the same as the subquery in $queryLevenshteinAndUnionAll. Can I replace it with something like below?

->whereNotIn('name', $queryPhoneticallySimilar)

This doesn't work but I'd like something similar to that syntax instead of basically having rewritten the same query to make it function in the whereNotIn. Other comments are welcome as well, I'm still learning :) The UNION ALLs are a performance optimalisation, instead of sending out 5 queries I only send out one and I'll take the slightly messier code over the performance penalty 5 seperate queries would give.

Please sign in or create an account to participate in this conversation.

Reply to

Use Markdown with GitHub-flavored code blocks.