DEV Community: Talemul Islam

Fuzzy String Matching in PostgreSQL with pg_trgm (Trigram Search Tutorial)

Talemul Islam — Wed, 01 Oct 2025 20:41:56 +0000

🔍 Fuzzy String Matching in PostgreSQL with `pg_trgm`

When working with user data — names, addresses, search queries — exact string matching isn’t always enough. People make typos, use alternate spellings, or abbreviations.

PostgreSQL provides a powerful extension called pg_trgm (trigram search) that allows you to perform fuzzy text matching and find “close enough” results.

🧩 What is a Trigram?

A trigram is simply a group of three consecutive characters from a string.

For example, the word:

Talemul

Would be split into trigrams (with start/end markers):

"  T", "Tal", "ale", "lem", "emu", "mul", "ul "

👉 Why the spaces?

PostgreSQL pads the string at the beginning and end with spaces to catch edge cases.

Now, when comparing two strings (Talemul vs Talimul), PostgreSQL compares how many trigrams they share:

Talemul: [" T","Tal","ale","lem","emu","mul","ul "]
Talimul: [" T","Tal","ali","lim","imu","mul","ul "]

They share trigrams like [" T","Tal","mul","ul "].

More shared trigrams = higher similarity score.

This makes trigram search excellent for catching typos or small spelling differences.

✅ Setup

Enable the extension:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

Check installed extensions:

\dx

📊 Example: Finding Similar Names

Let’s say we have a table of names:

CREATE TABLE people (
    id SERIAL PRIMARY KEY,
    name TEXT
);

INSERT INTO people (name) VALUES
('Talemul'),
('Talimul'),
('Talimul Islam'),
('Tamim'),
('Talim');

Now, search for names similar to "Talemul":

SELECT name, similarity(name, 'Talemul') AS score
FROM people
WHERE name % 'Talemul'   -- % means "is similar to"
ORDER BY score DESC;

🖥 Actual Output:

     name       |    score
----------------+-------------
 Talemul        | 1.00000000
 Talimul        | 0.45454547
 Talimul Islam  | 0.29411766
 Talim          | 0.27272728

👉 Notice how "Talimul Islam" and "Talim" get lower scores, and may even be excluded depending on the similarity threshold.

⚡ Boost Performance with Indexes

For large datasets, use a GIN index to speed up similarity searches:

CREATE INDEX idx_people_name_trgm ON people USING gin (name gin_trgm_ops);

Now your searches with % and similarity() will scale much better.

🎯 Real-World Use Cases

User search: Find users by name despite typos (Jon vs John).
Autocomplete: Suggest terms even when slightly misspelled.
Deduplication: Detect near-duplicate entries.
Search engines: Power “Did you mean?” features.

📝 Wrap Up

Trigrams are a simple but powerful idea: break words into three-character chunks, compare overlaps, and compute similarity.

PostgreSQL’s pg_trgm extension brings this technique directly into SQL, letting you build typo-tolerant search features without needing an external search engine.

🔗 References:

PostgreSQL Docs – pg_trgm

🔓 How to Call Protected Methods from Plugin Classes (PHP, Java, C#, Python)

Talemul Islam — Fri, 11 Jul 2025 16:14:42 +0000

🔍 TL;DR — Protected methods aren’t meant to be accessed directly, but sometimes you must — for testing, plugin hooks, or legacy migrations. Here's how to do it (safely) in PHP, Java, C#, and Python.

🤔 What’s a Protected Method?

In Object-Oriented Programming, protected methods are only accessible from within the class or its subclasses.

So why break the rules?

🧪 Unit testing inner logic
🔌 Plugin customization
🛠️ Legacy system debugging
🔁 Data migration tasks

While it’s not best practice to poke around protected members, there are legitimate edge cases.

🚀 PHP — Using `ReflectionMethod`

class Plugin {
    protected function setup() {
        return 'initialized';
    }
}

$ref = new ReflectionMethod(Plugin::class, 'setup');
$ref->setAccessible(true);

$plugin = new Plugin();
echo $ref->invoke($plugin);  // initialized

✅ Helper Function

function callProtected($obj, $method, array $args = []) {
    $ref = new ReflectionMethod($obj, $method);
    $ref->setAccessible(true);
    return $ref->invokeArgs($obj, $args);
}

☕ Java — `getDeclaredMethod` + `setAccessible(true)`

class Plugin {
    protected void initialize() {
        System.out.println("Initialized!");
    }
}

Plugin plugin = new Plugin();
Method m = Plugin.class.getDeclaredMethod("initialize");
m.setAccessible(true);
m.invoke(plugin);  // Initialized!

⚠️ In Java 9+, you may need:
--add-opens your.module/package=ALL-UNNAMED

🧪 C# — Binding Flags to the Rescue

class Plugin {
    protected void Configure() => Console.WriteLine("Configured!");
}

var plugin = new Plugin();
var m = typeof(Plugin).GetMethod("Configure",
            BindingFlags.Instance | BindingFlags.NonPublic);
m.Invoke(plugin, null);  // Configured!

🐍 Python — It’s Just a Convention

class Plugin:
    def _setup(self):
        return "initialized"

plugin = Plugin()
print(plugin._setup())  # initialized

Python doesn’t enforce access levels like other languages — just don’t start the method with two underscores.

🧰 Laravel Example

Let's wrap PHP reflection in a clean Laravel helper:

// app/Helpers/ReflectionHelper.php
namespace App\Helpers;

class ReflectionHelper {
    public static function call(object $obj, string $method, array $params = []) {
        $ref = new \ReflectionMethod($obj, $method);
        $ref->setAccessible(true);
        return $ref->invokeArgs($obj, $params);
    }
}

Usage:

$discount = \App\Helpers\ReflectionHelper::call(
    $plugin,
    'calculateDiscount',
    [$user, $cart]
);

🛡️ Should You Really Be Doing This?

Scenario	Safe to Reflect?
Unit / integration testing	✅
One-off migration tool	✅ (caution)
Production business logic	❌ Avoid
Public SDKs	❌ Avoid

📌 If you must do this in prod: cache, guard, and document it clearly!

🔐 Safe Reflection Practices

✅ Wrap reflection in a utility class or trait.
✅ Catch ReflectionException and fail gracefully.
✅ Cache reflection metadata if used repeatedly.
📝 Document clearly why reflection was used.

🧭 Quick Reference Table

Language	Trick	One-liner
PHP	`ReflectionMethod`	`$m->setAccessible(true)`
Java	`Method#setAccessible()`	`m.invoke(obj)`
C#	`BindingFlags.NonPublic`	`mi.Invoke(obj, null)`
Python	Convention only	`obj._method()`

🧠 Final Thoughts

Reflection is a power tool — like a chainsaw in the hands of a surgeon.

Use it when:

🔍 Testing internals
🔧 Fixing legacy systems
🧩 Customizing closed-source plugins

Don’t use it when:

❗ Building production-critical logic
🔓 Bypassing encapsulation carelessly

💬 Have Questions or Stories?

Have you ever had to “crack open” a class to call a protected method?

Got bitten by an API update that broke your reflection hack?

Drop your thoughts and stories in the comments below 👇

📌 If this helped you, consider following me for more backend, Laravel, and low-level tricks.

⚠️ ⚠️ Important Note:
Do NOT use this in production business logic!
Using reflection to bypass protected methods can lead to:

🔥 Code that breaks silently after plugin/library updates

🐛 Fragile systems that are hard to maintain or debug

🔓 Violations of encapsulation and OOP design principles

🚫 Unintended side effects or security risks

🧠 Automatically Track created_by and updated_by in All Models

Talemul Islam — Fri, 27 Jun 2025 05:33:53 +0000

🎬 Background: Real Problem from a Real Project

A few days ago, a client from Upwork assigned me a task:
“I need to see who created or last updated specific tasks or orders in the system.”
So I dove into the Laravel project... and found that:
• No tables had created_by or updated_by columns.

• No model had logic to store the user ID on create/update.

• No Blade template had access to the creator or updater names.

That Raised Some Questions:

• Should I manually add these columns and save logic in every controller?

• Should I add listeners to each model manually?

• Should I create global logic in AppServiceProvider?

That’s when I stepped back and designed a better, reusable, scalable solution using traits and a helper script — saving me hours of tedious work.

🔍 What I'll Build

• Add created_by and updated_by columns via migration

• Automatically populate these fields using traits

• Make relationships accessible in Blade (e.g., $order->creator->name)

• Auto-attach the tracking trait to all models via a PHP script

🛠 Step 1: Add Audit Columns via Migration

Create the migration file:

php artisan make:migration add_audit_columns_by_to_all_tables

Example content:

Schema::table('orders', function (Blueprint $table) {
    $table->unsignedBigInteger('created_by')->default(0);
    $table->unsignedBigInteger('updated_by')->default(0);
});

For multiple tables, use this:

private array $tables = [
    'customers',
    'orders',
    'order_details',
];

public function up(): void
{
    foreach ($this->tables as $table) {
        if (!Schema::hasColumn($table, 'updated_by')) {
            Schema::table($table, function (Blueprint $tableBlueprint) {
                $tableBlueprint->unsignedBigInteger('updated_by')->default(0);
            });
        }
    }
    foreach ($this->tables as $table) {
        if (!Schema::hasColumn($table, 'created_by')) {
            Schema::table($table, function (Blueprint $tableBlueprint) {
                $tableBlueprint->unsignedBigInteger('created_by')->default(0);
            });
        }
    }
}

public function down(): void
{
    foreach ($this->tables as $table) {
        if (Schema::hasColumn($table, 'updated_by')) {
            Schema::table($table, function (Blueprint $tableBlueprint) {
                $tableBlueprint->dropColumn('updated_by');
            });
        }
    }
}

Get your table names with:

SHOW TABLES;

Then run:

php artisan migrate

🧩 Step 2: Create the Traits

App\Traits\AuditTrailUpdater.php

<?php
/**
 * Author: Talemul Islam
 * Website: https://talemul.com
 */

namespace App\Traits;

use App\Models\User;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\Log;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

trait AuditTrailUpdater
{
    public static function bootAuditTrailUpdater(): void
    {
        static::creating(function (Model $model) {
            try {
                if (Schema::hasColumn($model->getTable(), 'created_by')) {
                    $model->created_by = Auth::id() ?? 0;
                }
                if (Schema::hasColumn($model->getTable(), 'updated_by')) {
                    $model->updated_by = Auth::id() ?? 0;
                }
            } catch (\Exception $e) {
                Log::error('Error setting created_by for ' . get_class($model) . ': ' . $e->getMessage());
            }
        });

        static::updating(function (Model $model) {
            try {
                if (Schema::hasColumn($model->getTable(), 'updated_by')) {
                    $model->updated_by = Auth::id() ?? 0;
                }
            } catch (\Exception $e) {
                Log::error('Error setting updated_by for ' . get_class($model) . ': ' . $e->getMessage());
            }
        });
    }

    public function updater(): BelongsTo
    {
        return $this->belongsTo(User::class, 'updated_by');
    }

    public function creator(): BelongsTo
    {
        return $this->belongsTo(User::class, 'created_by');
    }
}

✅ Step 3: Use the Trait in Models

use App\Traits\AuditTrailUpdater;

class Order extends Model
{
    use AuditTrailUpdater;
}

❓ Common Questions

Q: Will it break if a table doesn’t have the columns?

A: No, it checks with Schema::hasColumn() before setting the value.

Q: Will it handle both create and update?

A: Yes, creating and updating hooks are used.

But doing this for 50+ models manually? Painful. So…

⚙️ Step 4: Automate Trait Injection

add-audit-trail-updater.php:

<?php
/**
 * Author: Talemul Islam
 * Website: https://talemul.com
 */

$directory = __DIR__ . '/app/Models';

function addTraitToModel($filePath)
{
    $content = file_get_contents($filePath);

    if (strpos($content, 'use AuditTrailUpdater;') !== false) return;

    if (strpos($content, 'use Illuminate\Database\Eloquent\Model;') !== false) {
        $content = str_replace(
            'use Illuminate\Database\Eloquent\Model;',
            "use Illuminate\Database\Eloquent\Model;\nuse App\\Traits\\AuditTrailUpdater;",
            $content
        );
    }

    $content = preg_replace(
        '/class\s+\w+\s+extends\s+Model\s*{/',
        "$0\n    use AuditTrailUpdater;",
        $content
    );

    file_put_contents($filePath, $content);
    echo "Updated: $filePath\n";
}

$rii = new RecursiveIteratorIterator(new RecursiveDirectoryIterator($directory));

foreach ($rii as $file) {
    if ($file->isFile() && $file->getExtension() === 'php') {
        addTraitToModel($file->getPathname());
    }
}

Run:

php add-audit-trail-updater.php

🧾 In Blade

{{ $order->updater->name ?? 'System' }}

🚀 Wrap-Up

✅ Saves time on audit tracking

✅ Works for any number of models

✅ Clean, DRY, reusable

CONCAT_WS in SQL

Talemul Islam — Thu, 24 Mar 2022 13:58:23 +0000

Today I learn a new function in SQL. CONCAT_WS and CONCAT function returns a string resulting from the concatenation or joining, of two or more string values in an end-to-end manner. But CONCAT_WS separates those concatenated string values with the delimiter specified in the first function argument. CONCAT_WS requires a separator argument and a minimum of two other string value arguments; otherwise, CONCAT_WS will raise an error.
If CONCAT_WS receives arguments with all NULL values, it will return an empty string of type varchar(1).

Syntax

CONCAT_WS(separator, string1, string2, ...., string_n)

Example.

SELECT CONCAT_WS('+', 'It', 'add', 'with','plus sign.');
Result:
It+add+with+plus sign.
For
SELECT CONCAT('It',' only', ' concat',' string.');
Result:
It only concat string.

You can get more details about it by following the link.

CONCAT_WS
SQL Server CONCAT_WS() Function

Find position of an element of array using binary search

Talemul Islam — Sun, 13 Mar 2022 16:02:41 +0000

Here is details of problem description.

/** write a function to search a value in an array and return index or position of array element,
 * if not found return -1.
    * for example, you have given an array like $array=[1,2,3,5,9,11,14,19];
 * $search=11; find the position of 11 in the array.
 * use binary search only.
 */
function binaySearch($array, $value)
{
    // write your code from here

    return 0;
}

$array = [1, 2, 3, 5, 9, 11, 14, 19];
$search = 11;
echo binaySearch($array, $search);

Here one important thing is that "Binary search can be implemented only on a sorted list of items". If the elements are not sorted already, we need to sort them first.
Here is the solution.

function binaySearch($array, $value)
{
    // write your code from here
    $position = -1;
    //check the array is empty or not. If the array is empty then, return -1;
    if (count($array) == 0) {
        return $position;
    }
    $low = 0;// declare low as zero which is first index
    $high = count($array) - 1;//declare high which is last index
    while ($low <= $high) {
        $mid = floor(($low + $high) / 2);//declare middle and assign average of $low and $high
        if ($array[$mid] == $value) {
            $position = $mid;//found the searching value
            break;
        } elseif ($array[$mid] > $value) {
            $high = $mid - 1;//reassign high, may be the searching value is on left side from the middle.
        } else {
            $low = $mid + 1;////reassign low, may be the searching value is on right side from the middle.
        }
    }
    return $position;
}

If you run this code then you will get 5 as output. change the searching value and run again to understand the logic.
If you found any issue contact with me (talemulwi@gmail.com).
Here is full source code link- https://github.com/talemul/Problem-Solving/blob/main/find-position-using-binary-search.php
Thank you.

MessageBag in Laravel - Laravel tips

Talemul Islam — Thu, 03 Mar 2022 15:24:22 +0000

This article is only for laravel developer.

MessageBag is a great tools in laravel php framework to send error message in previous page. In few days ago i fall in a problem to send some error message to view page. But i can't use it on validation of laravel and send error. i have several check of work. i have to overwrite MessageBag. so i use this simple syntax like below.

$message = new MessageBag([ 'field_name' => ['Invalid input, Please try again.'], ]);
And send it on like this.

return redirect()->back()->withInput()->withErrors($message);
I have already have a view page where i want to show after submit this.

<div class="form-group{{ $errors->has('field_name') ? ' has-error' : '' }}"> <h3 class="payment-tittle">Insert your Input here</h3> <input class="is_required validate account_input form-control {{ ($errors->has('field_name'))?"input-error":"" }}" type="tel" name="otp" value="{{ old('field_name') }}" required> @if ($errors->has('field_name')) <span class="help-block"> {{ $errors->first('field_name') }} </span> @endif </div>
But i suggest all of you that use laravel validation process. But this process can be help you.