XF 2.2 Operator RLIKE missing, any recommendation

[RisteDimitrievski]

So when i run query via finder method

$user->whereOr(['user_group_id','=',$groupid],['secondary_group_ids','LIKE',$groupid])->limitByPage($page,$perPage)->fetch()->toArray();

It will output 1 users that are in that secondary group, i noticed that when that secondary group id i'm searching is in first place it can be retrieved, otherwise no. But when i run query
Note LIKE operator only returns 1 result from the table.

SELECT * FROM `xf_user` WHERE `user_group_id` = 4 OR `secondary_group_ids` RLIKE 4

I get all members from that group. Any idea how to solve it from finder ? without needing to put custom query as will break current pagination

 

[Chris D]

The appropriate way to achieve this isn’t really an RLIKE query.

For an example please check:
src/XF/Searcher/User.php

You can mix the finder and inject raw SQL in some cases. You’ll see how we do this using FIND IN SET.

 

[Lukas W.]

The LIKE operation will actually create false positives as soon as you have user groups with more than one digit. For example LIKE 1 will match all users that have at least one user group that have a 1 at any position, e.g. 1, 100, 10, 21, etc. You will need to use MySQLs find_in_set instead.

XF\Searcher\User has somewhat of a reference implementation around Lines 155 to 182, which you can combine with the primary user group check to create a correct query.

 

[RisteDimitrievski]

The LIKE operation will actually create false positives as soon as you have user groups with more than one digit. For example LIKE 1 will match all users that have at least one user group that have a 1 at any position, e.g. 1, 100, 10, 21, etc. You will need to use MySQLs find_in_set instead.

XF\Searcher\User has somewhat of a reference implementation around Lines 155 to 182, which you can combine with the primary user group check to create a correct query.

No, FIND_IN_SET will not help me in my situation as i need to check in OR clause so i'm checking first in primary user group then in secondary group.
So RLIKE method is more appropriate.

 

[Lukas W.]

No, FIND_IN_SET will not help me in my situation as i need to check in OR clause so i'm checking first in primary user group then in secondary group.
So RLIKE method is more appropriate.

The FIND_IN_SET clause is correct. You need to pair it with an or condition to check the primary user group. Using RLIKE on the secondary groups filed will result in the problem I described above.

 

[RisteDimitrievski]

The FIND_IN_SET clause is correct. You need to pair it with an or condition to check the primary user group. Using RLIKE on the secondary groups filed will result in the problem I described above.

RLIKE will not make problem. The LIKE will make problem..

Did you read my first post?
read it again. FIND_IN_SET expects 2 parameters so the list of secondary user groups must known, in this case is overkill. I need to fetch every user if exists on primary user group if no then check in secondary user group.

 

[Jeremy P]

You can supply your own SQL for a where condition using $finder->whereSql(...), which is exactly what the references that Chris and Lukas pointed out to you do.

FIND_IN_SET expects 2 parameters so the list of secondary user groups must known,

You can just pass a column name as the second parameter (again, per the references provided above):

$groupId = 2;
$secondaryGroupColumn = $finder->columnSqlName('secondary_group_ids');
$finder->whereSql('FIND_IN_SET(' . $finder->quote($groupId) . ', ' . $secondaryGroupColumn . ')';

There's nothing preventing you from adding an OR clause in there. You could even use finder expressions to do some of the work for you:

$groupId = 2;
$primaryCondition = ['user_group_id', $groupId];
$secondaryCondition = $finder->expression('FIND_IN_SET(' . $finder->quote($groupId) . ', %s)', 'secondary_group_ids');
$finder->whereOr([$primaryCondition, $secondaryCondition]);

 

[Xon]

The xf_user_group_relation join table is probably a more efficient option to query rather than FIND_IN_SET.

It looks like this;

CREATE TABLE `xf_user_group_relation` (
  `user_id` int(10) unsigned NOT NULL,
  `user_group_id` int(10) unsigned NOT NULL,
  `is_primary` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`user_id`,`user_group_id`),
  KEY `user_group_id_is_primary` (`user_group_id`,`is_primary`)
)

 

[RisteDimitrievski]

The xf_user_group_relation join table is probably a more efficient option to query rather than FIND_IN_SET.

It looks like this;

CREATE TABLE `xf_user_group_relation` (
  `user_id` int(10) unsigned NOT NULL,
  `user_group_id` int(10) unsigned NOT NULL,
  `is_primary` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`user_id`,`user_group_id`),
  KEY `user_group_id_is_primary` (`user_group_id`,`is_primary`)
)

I have already relationships made between User entity and UserGroup, but not working well. The reason is one member can be in multiple groups, and i need to fetch 'em in my user group legend in groups all his members. It can be in primary or secondary groups. But the code of @Jeremy P has solved my issue.

 

[Jeremy P]

If the query is in a hot path, then you are better off using a condition that can hit an index (like what Xon suggested).

 

[RisteDimitrievski]

If the query is in a hot path, then you are better off using a condition that can hit an index (like what Xon suggested).

No it's not. Plus i've paginated the results so its limited to 17 per page.