Hi,
Georg Ringer wrote:
Am 14.11.2011 19:58, schrieb Xavier Perseguers:
The big problem comes from having to deal with comma-separated values to
reference records (here fe_groups) instead of real foreign keys.
so how should an ideal query look like instead?
> (tt_news.fe_group='' OR tt_news.fe_group
> IS NULL OR tt_news.fe_group='0'
No group
> OR (tt_news.fe_group LIKE '%,0,%' OR
> tt_news.fe_group LIKE '0,%' OR tt_news.fe_group LIKE '%,0' OR
> tt_news.fe_group='0') OR (tt_news.fe_group LIKE '%,-1,%' OR
> tt_news.fe_group LIKE '-1,%' OR tt_news.fe_group LIKE '%,-1' OR
> tt_news.fe_group='-1')
-1 is a virtual group used to "Hide content at any login" [1]
So basically we search news which are targeted at no, group, group "0"
(= no group), or that should be hidden at login.
With a proper structure, we would have something like
- table tt_news (uid, ...)
- table fe_groups (uid, ...)
- table tt_news_fegroups_mm (uid_local, uid_foreign)
uid_local is a foreign key to tt_news(uid)
uid_foreign is a foreign key to fe_groups(uid)
Thus search for news without any group:
SELECT * FROM tt_news WHERE NOT EXISTS(SELECT * FROM tt_news_fegroups_mm
WHERE uid_local=tt_news.uid)
Search news with the group "-1":
SELECT * FROM tt_news WHERE EXISTS(SELECT * FROM tt_nerws_fegroups_mm
WHERE uid_local=tt_news.uid AND uid_foreign=-1)
OK, when using DB constraints on foreign keys, we would need to have a
real fe_group entry for -1 and -2 (or 0) but I guess you get the point.
The latter query could be rewritten as INNER JOIN without subquery as well.
HTH
[1]
http://xavier.perseguers.ch/en/tutorials/typo3/articles/indexed-search-crawler.html#c868
--
Xavier Perseguers
Release Manager TYPO3 4.6
TYPO3 .... inspiring people to share!
Get involved: http://typo3.org
_______________________________________________
TYPO3-english mailing list
TYPO3-english@lists.typo3.org
http://lists.typo3.org/cgi-bin/mailman/listinfo/typo3-english
