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

Jaxel

Well-known member
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:

PHP:
    $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?
 

DragonByte Tech

Well-known member
How would I do that with the finder system?
See this block of code in XF/Purchasable/UserUpgrade as an example:
PHP:
        $quotedProfileId = $finder->quote($profileId);
        $columnName = $finder->columnSqlName('payment_profile_ids');

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

Jaxel

Well-known member
I went with this:

PHP:
    $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.

Well-known member
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.
 

DragonByte Tech

Well-known member
I dont know what you mean by this.
They mean the expression function, like so:
PHP:
            $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

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