XF 2.1 A better way to use the finder within a blob?

[Jaxel]

So I have a column in my table, that is a blob of usergroup ids... ex: ["2","3","5","9"]

I would like to search this table, for results in which the user's group's match this column.

Currently I am doing it was follows:

    $visitor = \XF::visitor();
   
    $whereOr = [
        [' Category.category_groups', 'LIKE', '%"'.$visitor->user_group_id.'"%'],
    ];
   
    foreach ($visitor->secondary_group_ids AS $group)
    {
        $whereOr[] = ['Category.category_groups', 'LIKE', '%"'.$group.'"%'];
    }
   
    $finder->whereOr($whereOr);

This is working, really well too. And on my test forum, it seems to have no effect on performance... but depending on the number of groups a user belongs to, this could add a ton of %LIKE% clauses to the query. While I am querying a table that has 10,000+ rows, the joined category table that it's connect to only has about 15 rows. It's not unfathomable that this would have a significant effect on performance in a table that has over a million rows.

Is there a better way to do this? Also, would the performance be affected by the 10,000 rows of the main table, or the 15 rows of the joined category table?

 

[Lukas W.]

Use a list of comma-separated values and MySQLS FIND_IN_SET() function.

 

[Jaxel]

Use a list of comma-separated values and MySQLS FIND_IN_SET() function.

How would I do that with the finder system?

 

[DragonByte Tech]

How would I do that with the finder system?

See this block of code in XF/Purchasable/UserUpgrade as an example:

        $quotedProfileId = $finder->quote($profileId);
        $columnName = $finder->columnSqlName('payment_profile_ids');

        $upgrades = $finder->whereSql("FIND_IN_SET($quotedProfileId, $columnName)")->fetch();

 

[Jaxel]

I went with this:

    $columnName = $finder->columnSqlName('Category.category_groups');
    $visitor = \XF::visitor();
    
    $whereOr = [
        "$columnName = ''",
        "FIND_IN_SET(".$visitor->user_group_id.", $columnName)"
    ];
    
    foreach ($visitor->secondary_group_ids AS $group)
    {
        $whereOr[] = "FIND_IN_SET($group, $columnName)";
    }
    
    $finder->whereSql(implode($whereOr, ' OR '));

Is this adequate? Or would you recommend changes?

 

[Lukas W.]

I'd recommend using the buildExpression function of the finder object to construct individual expressions and then feed them into whereOr().

Fwiw, using the glue as the second parameter in implode is deprecated since php7.4, and should go as first argument.

 

[Jaxel]

I'd recommend using the buildExpression function of the finder object to construct individual expressions and then feed them into whereOr().

I dont know what you mean by this.

 

[DragonByte Tech]

I dont know what you mean by this.

They mean the expression function, like so:

            $columnName = $finder->columnSqlName('Category.category_groups');
            $whereOr = [];
            foreach ($visitor->secondary_group_ids AS $part)
            {
                $whereOr[] = $finder->expression('FIND_IN_SET(' . $finder->quote($part) . ', '. $columnName . ')');
            }
            if ($secondaryParts)
            {
                $finder->whereOr($whereOr);
            }

 

[Jaxel]

Oh, you mean for sanitation. Does $part really need to be sanitized and quoted? It's a pretty fixed dataset.