Laravel Eloquent: JSON comparison-CodePudding

I have the following model:

info is a JSON be and the results would look something like this:

{
  "cas": "none",
  "uom": "6",
  "url": "http://www.foobar.com",
  "size": "10.00",
  "type": "Chemical",
  "unit": {
    "id": "6",
    "name": "gram",
    "plural": "grams",
    "abbreviation": "g",
    "display_order": "6",
    "additional_aliases": "gr, gm"
  },
  "owner": null,
  "vendor": "Sigma Aldrich",
  "quantity": 8,
  "product_name": "Nafion(R), trimethylsilylated",
  "catalog_number": "392928-10G",
  "ghs_pictograms": [
    1
  ],
  "low_level_warning": 3,
  "high_level_warning": 7
}

info.quantity tells us how much of this item is left in inventory.

info.low_level_warning / info.high_level_warning is a user entered value where we want to filter by.

So I tried something like this, but it doesn't work:

} else if ($filters['filter'] === 'show_below_min_threshold') {
    $query->where(function ($query) {
        return $query->where('info->quantity', '<', 'info->low_level_warning');
    });
} else if ($filters['filter'] === 'show_above_max_threshold') {
    $query->where(function ($query) {
        return $query->where('info->quantity', '>', 'info->high_level_warning');
    });
}

Any thoughts on what I am doing wrong?

CodePudding user response：

You are comparing two columns. where is for comparing a column to a value.

where('info->quantity', '<', 'info->low_level_warning') is trying to find rows where info->>quantity is less than the string "info->low_level_warning", which doesn't make much sense.

You should use whereColumn

} else if ($filters['filter'] === 'show_below_min_threshold') {
    $query->whereColumn('info->quantity', '<', 'info->low_level_warning');
} else if ($filters['filter'] === 'show_above_max_threshold') {
    $query->whereColumn('info->quantity', '>', 'info->high_level_warning');
}