1

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...

4

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.

  • thanks for your answer I was also of the opinion that it wasn't possible to do since I have had to unserialize the array in the first place. Having confirmation from someone as reputable as yourself is enough for me... – user1704524 Jan 6 '14 at 20:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.