| 7/3/2006 7:00:05 PM |
Hi,
I'm trying to create a query but I'm having some difficulties with the query.
I have two table, one with translations of Role names and one with cultures in the system.
In the cultures table (Core_CmsLanguages) there is a field called IsDefault that is only true for one of the languages.
In my translation table I always have one row for the default language, and optionally translations for the other cultures.
When I do a retrieve from the table I want to get one row for each different role where I specifiy the preferred culture.
I a translation doesn't exist with the specified culture I want to get one in the default language.
I've been able to do it manually, but I really want to do it with Opath.
The problem, as I see it, is that when I traverse the relation-tree I get some unwanted where clauses. Specifically "WHERE (b.Culture = c.Culture)" from the end of the generated query. If I remove that I can get the result I want.
So I guess the question comes down to this, how can I traverse the relation without getting those where clauses?
The opath I used to generate the query was the following:
Culture == '{0}' || CmsLanguageObject[IsDefault == True && !CmsLocalizedRoleList[RoleId = ^.^.RoleId && Culture == '{0}']]
Any tips on how to do this would be greatly appreciated.
Here is the query that I want:
SELECT a.[RoleId], a.[Culture], a.[DisplayName], a.[Description]
FROM [Core_CmsLocalizedRoles] a
WHERE ((a.[Culture] = 'en-us') OR EXISTS
(
SELECT *
FROM [Core_CmsLanguages] b
WHERE (a.[Culture] = b.[Culture]) AND ((b.[IsDefault] = 1) AND NOT EXISTS
(
SELECT *
FROM [Core_CmsLocalizedRoles] c
WHERE ((c.[RoleId] = a.[RoleId]) AND (c.[Culture] = 'en-us'))
))
));
But what I get is this:
SELECT a.[RoleId], a.[Culture], a.[DisplayName], a.[Description]
FROM [Core_CmsLocalizedRoles] a
WHERE ((a.[Culture] = 'en-us') OR EXISTS
(
SELECT *
FROM [Core_CmsLanguages] b
WHERE (a.[Culture] = b.[Culture]) AND ((b.[IsDefault] = 1) AND NOT EXISTS
(
SELECT *
FROM [Core_CmsLocalizedRoles] c
WHERE (b.Culture = c.Culture) AND ((c.[RoleId] = a.[RoleId]) AND (c.[Culture] = 'en-us'))
))
));
|
| 7/10/2006 6:09:14 AM |
Sorry for the delayed reply. IE7 decided to stop checking the RSS feed for this forum and I didn't discover it until today.
I'm stumped on this one. At first I thought it could be done, but I just keep running into walls. You won't be able to get rid of that unwanted (b.Culture = c.Culture) predicate in the last select as long as you are traversing a relationship. However, if there was a way to create a self-relation on the Core_CmsLocalizedRoles on the RoleID column it would inject (c.RoleId = a.RoleId) and you would just need to test the culture field. The problem is… WORM doesn't let you specify both PK and FK of the relation (really wish it would), so this idea is out. If you could do it though, the query would look something like this:
Culture == '{0}' || CmsLanguageObject.IsDefault == True && !(^.SelfRelationOnRoleID.Culture == '{0}')
The next release of WORM will add the EXISTS keyword, but it also requires a relationship traversal. If I updated the engine so EXISTS clauses didn't have to be related to the rest of the query by a relationship, something like the following would work:
Culture == '{0}' || CmsLanguageObject.IsDefault == True && !EXISTS<Core_CmsLocalizedRoles>(RoleId == ^.RoleId && Culture == '{0}')
I'd need to extend the language to allow specifying the entity/table you want to query in the EXISTS, since there is no way to know without a relationship.
Anyway, sorry I can't help you more with this. On the bright side, it got me thinking about extending the languange.
- Jeff Lanning
|