[TYPO3] Advanced query not advanced enough - part 2

[Bernd Wilke]

On Fri, 12 May 2006 15:20:50 +0200, JoH wrote
with subject "Re: [TYPO3] Advanced query not advanced enough - part 2":

> > 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 ...

a short time ago there was a similar thread and the solution was like this:

WHERE
fe_users.disable != '1'
AND fe_users.deleted=0
AND concat(concat(','.fe_users.usergroup),',') LIKE '%,1,%' 
ORDER BY zip

I don't remember it exactly. I hope you can see the structur of this
simplification.

Bernd



-- 
----------------
Bernd Wilke     
Annweilerstr.20 
40229 Düsseldorf
0211/229 2800