XF 2.1 Finder Where In Array or Empty?

[Jaxel]

I have a blob column, lets call it my_blob... This is a JSON_ARRAY in my entity.
The array will contain a list of numbers, something like ["1","2","4","8"].

I want to then compare this array, to another array in a finder. Lets say my array $numbers = ["1","5"].

Basically, I want to search for any entities where my_blob matches items in the array from items in the array from $numbers

$this->finder('My\Addon:MyEntity')
    ->where('my_blob', $numbers)
    ->fetch();

Is it possible to do this with the finder system?

In addition, is there a way to also return all entities where my_blob is an empty array?

 

[Jaxel]

I ended up doing it as follows:

$whereOr = [
    ['my_blob', ''],
    ['my_blob', '[]'],
];

foreach ($numbers AS $number)
{
    $whereOr[] = ['my_blob', 'LIKE', '%"'.$number.'"%'];
}

$this->finder('My\Addon:MyEntity')
    ->whereOr($whereOr)
    ->fetch();

Seems like a terrible way to do this... anyone got better suggestions?

 

[Lukas W.]

If XF will bump the minimum MySQL version in the future, you can use MySQLs JSON functions to do that. For now, you cant.

Your current match will produce false positives. 11, 12, 153, 215 for example will all match LIKE %1%. You either have to change it to a specific three point match (LIKE "[1," OR LIKE ",1," OR LIKE ",1]") or preferably outsource the data to a relational table, which should be preferred for efficiency anyway.

 

[Jaxel]

It wont match false positives. I specifically told it to match LIKE %"1"%, not LIKE %1%.

 

[Lukas W.]

You also said your array holds a list of numbers.

Regardless, for database queries on it, turn it into a relational table. Blob colums are not designed for these types of operations.

 

[Jeremy P]

I'm not sure what your use case is, but you can store them as a LIST_COMMA column, and review how the user searcher finds matches for secondary_group_ids (\XF\Searcher\User::applySpecialCriteriaValue()).