StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL Server: transforming query for materialized path traversing in a view
primarykey
Id
20430130
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
3
CommunityOwnedDate
CreationDate
2013-12-06T17:36:23.493
FavoriteCount
0
LastActivityDate
2013-12-21T11:22:51.303
LastEditDate
2013-12-21T11:22:51.303
LastEditorUserId
1380014
OwnerUserId
1380014
ParentId
0
PostTypeId
1
Score
0
ViewCount
218
LastEditorDisplayName
text
Body
I would like to transform this query in a view that I can call passing the UserId as a parameter to select all groups (including subtrees of those groups) related to that user. Infact I have 2 tables: - "EVA_Roles" wich contains the roles tree, defined through a materialized path in the RoleHid field as varchar(8000) - "EVA_UsersInRoles" wich related users to roles through the field UserId Problem here is that only some roles may be related to the user in the EVA_UsersInRoles table, but maybe those roles are parents to other roles in the tree hierarchy so I have to retrieve multiple subtrees for each user. Finally I came up with this query wich seems to work fine, but I would like to transform it in a View. The problem I'm facing of course is that the UserId parameter, wich is the one I would use to filter the view results, is inside the subquery. Any hint to refactor this into a view? <pre><code>SELECT A.RoleId, E.EndDate FROM EVA_Roles A INNER JOIN EVA_Roles B ON A.RoleHid LIKE B.RoleHid + '%' AND B.RoleHid IN (SELECT RoleHid FROM EVA_Roles C LEFT JOIN EVA_UsersInRoles D ON C.RoleId = D.RoleId WHERE (D.Userid = @0 OR C.RoleId = @1) AND C.ApplicationId = @2) LEFT JOIN EVA_UsersInRoles E ON A.RoleId = E.RoleId AND E.UserId = @0 WHERE A.ApplicationId = @2 ORDER BY A.RoleId </code></pre> I left parameters where I should pass values to the view. I think it may be impossible to refactor in a view. It was just to exploit my micro-ORM (PetaPoco) in a more friendly way, otherwise I have to use the SQL in my code but it's ok, don't loose your mind on this. About the tables definition: <pre><code>EVA_Roles RoleId INT - Primary Key RoleHid VARCHAR(800) - Here I store the materialized path of the tree using nodes ids... An example on this later. RoleLevel INT - Security level of the role RoleName INT - Name of the role (admin, guest, ...) ApplicationID INT - Id of the application (in a multi app scenario) EVA_UsersInRoles RoleId - INT UserId - INT </code></pre> The materialized path in RoleHid follows this logic. Consider this data where RoleId 2 is child of RoleId 1: RoleId 1 RoleHid "1." RoleId 2 RoleHid "1.2." WIth the query above I'm able to retrieve all subtrees tied to a specific user and application.
Tags
<sql-server><view><tree><subquery><materialized-path-pattern>
Title
SQL Server: transforming query for materialized path traversing in a view
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USManight
UserOwnerUserId
1. USManight
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.