[TYPO3] Advanced query not advanced enough - part 2

[JoH]

> I use the advanced query to select FE user from certain groups.  The
> query looks like:
>
> SELECT username,usergroup,first_name,last_name,
> FROM fe_users
> WHERE
> (
> fe_users.disable != '1'
> AND (
>     fe_users.usergroup = '1'
>     OR fe_users.usergroup LIKE '%1,%' AND fe_users.usergroup LIKE '%%'
>     OR fe_users.usergroup LIKE '%,1%' AND fe_users.usergroup LIKE '%%'
>     OR fe_users.usergroup LIKE '%,1,%' AND fe_users.usergroup LIKE
> '%%' )) AND fe_users.deleted=0
> ORDER BY zip
>
> I came to this query with the help of Joey on this list.  But there is
> still a situation where the query does not generate the correct
>   result. Example:  a user is member of the groups 3,5,10 and you
> want the have the users from group 1 only.  This user with groups
> 3,5,10 will be in the query.  But he is not member of group 1.
>
> I don't see a way to get this right...

You are right, since there is no possibility to set "starts with" or "ends
with" for groups.
The correct query would be

fe_users.usergroup = '1'
OR fe_users.usergroup LIKE '1,%'
OR fe_users.usergroup LIKE '%,1'
OR fe_users.usergroup LIKE '%,1,%'

If I find the time during the next weeks, I will try to add these features.
Don't know if it will be available for 4.01 though ...

Joey

-- 
Wenn man keine Ahnung hat: Einfach mal Fresse halten!
(If you have no clues: simply shut your knob sometimes!)
Dieter Nuhr, German comedian
openBC: http://www.cybercraft.de