Is there a way to query Magento Database for part OR all of an unserialized string?

Question

I would like to know how to query a column for all or part of a string. I have created a module which uses data additional_information column from the Sales_Flat_Order_Payment table which I have been able to unserialize and display in a grid. However, when serching/filtering this column the user has to enter the full value which can be time consuming so I would like to filter using ANY value.

At the moment my filter is setup using strlen but I would like to know if there is a better way for me to do it please?

  protected function _paymentFilter($collection, $column)
    {
                case "additional_information_column":
                {

                    $this->getCollection()->getSelect()->where(
                        "sfop.additional_information like ?"
                        , '%column";s:'.strlen($value).':"%'.$value.'%');
                    break;
                }
    }

Any ideas are much appreciated...

Answer 1

Unfortunately, no, you can't do this any better/efficiently than your current solution.

The algorithm used to serialize a variable in PHP is proprietary to PHP. MySQL (or the other RDMS) know nothing about it, so there's no query tools other than a raw string LIKE that work with the fields.

In theory it would be possible (via object listeners) to save the serialized data into join tables, and then alter your grid collections to pull the data you want from that join table — but this approach is complicated, fraught with problems and would require testing/iterations to work out potential bugs.