Laravel eloquent, search in a column with text like an array content (but not casted in model)-CodePudding

There is a model (product_stocks) that has a column named variant_avi contains text like [21,3], and I want to check if it contains any element from an array like [2,3,14,21].

Part of product_stocks table migration:

$table->text('variant_avi');

I know the simple way to do it, is to cast the column to array, but I can't change the model.

Is there any way to do it?

CodePudding user response：

One possibility use multiple LIKE operator to check if the variant_avi column contains any of the values in the array:

$values = [2, 3, 14, 21];
$results = ProductStock::where(function($query) use ($values) {
        foreach ($values as $value) {
            $query->orWhere('variant_avi', 'like', "%{$value}%");
        }
    })
    ->get();

Keep in mind that using the LIKE operator may not be as efficient as casting the column to an array. If possible, it would be best to change the model and cast the variant_avi column to an array so that you can use more efficient array comparison operators.

If you cannot change that model but the above solution would create performance issues, I would recommand to build another table that would act as a materialized view.

Building a materialized view to store the variant_avi column as JSON would be a good solution because it would allow you to use more efficient array comparison operators without changing the original model.

If you are using Postgres this is a native feature:

DB::statement('CREATE MATERIALIZED VIEW product_stocks_json AS
    SELECT id, variant_avi::JSON AS variant_avi
    FROM product_stocks
');

In MySQL it needs a bit of extra work: https://fromdual.com/mysql-materialized-views

You could also take advantage of model events in Laravel to keep a separate table up to date: https://laravel.com/docs/9.x/eloquent#events