jordan-dev's avatar

Theoretical question Eloquent Builder and MySQL

I creation product properties table. At first glance, this is a very simple task. "Product prorepty" table structure like this

$table->id();
$table->unsignedBigInteger('product_id');
$table->string('key');
$table->text('value');

No problems, but we want query by property. No problem too. like this:

Product::whereHas('property', function($query) {
	$query->where('key', 'color');
	$query->where('value', 'blue');
})->get()

BUT! If I want to query it by numeric value something like

->where('weight', '>', '1000')

table column has text or string type, but I want to use it like it is integer or double... Laravel Metable package: https://github.com/plank/laravel-metable fix it by cast field as decimal

$q->whereHas('meta', function (Builder $q) use ($key, $operator, $value, $field) {
            $q->where('key', $key);
            $q->whereRaw("cast({$field} as decimal) {$operator} ?", [(float)$value]);
        });

And finaly my question is:

  1. With this approach like laravel-metable do, how about query processing speed?
  2. In my ecommerce progect it will be many many queries by property, and processing speed is important, and maybe I should create table with few colums for every type and query by specific column like this ?:
$table->id();
$table->unsignedBigInteger('product_id');
$table->string('key');
$table->integer('int_value');
$table->text('string_value');
$table->double('double_value');

Can anybody say something about query processing speed in the case I described ?

0 likes
1 reply
tylernathanreed's avatar

In general, the more joins and subqueries you have, the slower the query will be.

However, if you're properly paginating results and keeping your overall result set somewhat limited, performance shouldn't be an issue.

Another possible implementation would be to use a json field for all of your extraneous properties. This would allow for a more flexible schema, and also allow you to make multiple meta constraints without needing a separate subquery for each of them.

In practice, when I've gone for a meta approach, I first decide that everything is going to be meta. I keep that tune until I see a meta-related performance problem that I can't avoid. At that point, you could easily make a migration to elevate a meta property to an official column, which saves you a join/subquery.

Please or to participate in this conversation.