I think I need a subquery to do this job, but I'm not that experienced with them... here is my task.

I have a group of faculty users accounts, of which some have membership to specific roles while others own those roles. This allows a teacher (of foreign languages) to set up a collection of roles which other teachers (substitute teachers perhaps) can have a membership to.

Faculty - facultyId - pk - fName - lName

Membership - membership - pk - facultyId - roleId - isOwner - isMember

Role - roleId - name - desc

Example: Let's say Suzanne teaches French, and has ownership of the roles 'Substitute Teachers' and 'Teacher Assistants'. Peter is a faculty member too and occasioanally substitute teaches for Suzanne, so Suzanne has assigned Peter a membership to 'Substitute Teachers'. Amy sometimes helps Suzanne out with grading homework so Suzanne assigns Amy a membership to 'Teacher Assistants'. Suzanne's memory is bad and she has forgotten who she has assigned memberships, and wants to perform a search for all faculty members who have membership to all the roles she has set up and owns.

I saw the FieldCompareSetPredicate in the docs but don't see how to make it work in this scenario.

Any help is greatly appreciated.

Isz

I think you may Join the Membership table twice with roleID = roleID & membershipID <> membershipID. as follwos:

SELECT Faculty.facultyId, Faculty.fName, Faculty.lName, Role.Name
FROM Faculty 
INNER JOIN Membership M1 ON M1.facultyID = Faculty.facultyID
INNER JOIN Role ON Role.roleID = M1.roleID
INNER JOIN Membership M2 ON M2.roleID = M1.roleID AND M2.membership <> M1.membership
AND M2.facultyID = 1 AND M2.IsOwner = 1

assuming Suzanne facultyID = 1

which is the same as

SELECT Faculty.facultyId, Faculty.fName, Faculty.lName, Role.Name
FROM Faculty 
INNER JOIN Membership M1 ON M1.facultyID = Faculty.facultyID
INNER JOIN Role ON Role.roleID = M1.roleID
WHERE M1.roleID IN 
(SELECT roleID FROM Membership M2 
WHERE 
M2.facultyID = 1 AND M2.IsOwner = 1 AND M2.membership <> M1.membership
)

Yes... thats exactly the idea I am looking for, now I am trying to apply it using self-servicing and here's what I got so far:

//  This part should be the predicate to get the roles owned by the teacher
IPredicateExpression facultyOwnerFilters = new PredicateExpression();
IPredicate facultyOwnerFilter = PredicateFactory.CompareValue(FacultyUserLogTankFieldIndex.FacultyId, ComparisonOperator.Equal, DEFAULT_FACULTY_ID);
IPredicate facultyRoleTypeFilter = PredicateFactory.CompareValue(FacultyMembershipTankFieldIndex.RoleTypeId, ComparisonOperator.Equal, (int)Enumerations.RoleType.Ownership);
IPredicate facultyMembershipTank = PredicateFactory.CompareValue(FacultyMembershipTankFieldIndex.IsFaculty, ComparisonOperator.Equal, true);
facultyOwnerFilters.Add(facultyOwnerFilter);
facultyOwnerFilters.AddWithAnd(facultyRoleTypeFilter);
facultyOwnerFilters.AddWithAnd(facultyMembershipTank);

//  Here are all my realtions that should support the endeavor
IEntityRelation facultyUserLogTankRelation = FacultyUserLogTankEntity.Relations.FacultyEntityUsingFacultyId;
IEntityRelation facultyMembershipTankRelation = FacultyMembershipTankEntity.Relations.FacultyUserLogTankEntityUsingFacultyUserLogTankId;
IEntityRelation roleTypeRelation = RoleTypeEntity.Relations.FacultyMembershipTankEntityUsingRoleTypeId;
IEntityRelation roleRelation = RoleEntity.Relations.FacultyMembershipTankEntityUsingRoleId;
IEntityRelation userLogRelation = UserLogEntity.Relations.FacultyUserLogTankEntityUsingUserLogId;
facultyGroupRelations.Add(facultyUserLogTankRelation);
facultyGroupRelations.Add(facultyMembershipTankRelation);
facultyGroupRelations.Add(roleTypeRelation);
facultyGroupRelations.Add(roleRelation);
facultyGroupRelations.Add(userLogRelation);

//  Here is a predicate that should get the faculty members who have memberships
IPredicateExpression facultyGroupFilters = new PredicateExpression();
IPredicate facultyMembershipFilter = PredicateFactory.CompareValue(FacultyMembershipTankFieldIndex.RoleTypeId, ComparisonOperator.Equal, (int)Enumerations.RoleType.Membership);
facultyGroupFilters.Add(facultyMembershipFilter);

//  GetMulti
UserLogCollection userLogCollection = new UserLogCollection();
userLogCollection.GetMulti(facultyMembershipFilter , 0, null, facultyGroupRelations);

I am thinking a CustomFilter added to one of the relations might be needed but I can't see how to apply this functionality, like which predicate would makeup the CustomFilter and to waht realtion I would add it. Also, I am missing the part where you do the following...

INNER JOIN Membership M2 ON M2.roleID = M1.roleID AND M2.membership <> M1.membership

Please note, if not already obvious, that there is a FacultyUserLogTank table not metioned in the first post which is a (m:n I think) table that holds facultyId from the real Faculty table and UserLogId fron the UserLog table. I am trying to return UserLogCollection for those faculty accounts who have memberships to the roles owned by DEFAULT_FACULTY_ID.

Thanks so much for your assistance!

Isz

speaking about this Query:

SELECT Faculty.facultyId, Faculty.fName, Faculty.lName, Role.Name
FROM Faculty 
INNER JOIN Membership M1 ON M1.facultyID = Faculty.facultyID
INNER JOIN Role ON Role.roleID = M1.roleID
INNER JOIN Membership M2 ON M2.roleID = M1.roleID AND M2.membership <> M1.membership
AND M2.facultyID = 1 AND M2.IsOwner = 1

To have a self join, specify an alias for the relation "aliasRelationEndEntity" to the following overload:

RelationCollection.Add Method (IEntityRelation relationToAdd, String aliasRelationEndEntity)

And the custom filter you need is (AND M2.membership <> M1.membership AND M2.facultyID = 1 AND M2.IsOwner = 1) and should be added to this relation.

I am still a bit lost here... I see the logic in what is required, but I a have only worked with simpe predicates thus far.

Here is where I am at:

int DEFAULT_FACULTY_ID = 1;

string FACULTY_MEMBERSHIP_ALIAS_M1 = "M1";
string FACULTY_MEMBERSHIP_ALIAS_M2 = "M2";

IPredicateExpression facultyfilters = new PredicateExpression();
IPredicate filterFacultyIdM2 = PredicateFactory.CompareValue(FacultyUserLogTankFieldIndex.FacultyId, ComparisonOperator.Equal, DEFAULT_FACULTY_ID, FACULTY_MEMBERSHIP_ALIAS_M2);
IPredicate filterIsOwnerM2 = PredicateFactory.CompareValue(FacultyMembershipTankFieldIndex.RoleTypeId, ComparisonOperator.Equal, (int)SuzanneBConrad.Utilities.Enumerations.RoleType.Ownership, FACULTY_MEMBERSHIP_ALIAS_M2);

IEntityField entityFieldM1 = EntityFieldFactory.Create(FacultyMembershipTankFieldIndex.FacultyUserLogTankId).SetObjectAlias(FACULTY_MEMBERSHIP_ALIAS_M1);
IEntityField entityFieldM2 = EntityFieldFactory.Create(FacultyMembershipTankFieldIndex.FacultyUserLogTankId).SetObjectAlias(FACULTY_MEMBERSHIP_ALIAS_M2);
IPredicate filterM2FacultyIdNotEqualM1FacultyId = PredicateFactory.CompareExpression(FacultyMembershipTankFieldIndex.FacultyUserLogTankId, ComparisonOperator.NotEqual, new Expression(entityFieldM1), FACULTY_MEMBERSHIP_ALIAS_M2);

facultyfilters.Add(filterFacultyIdM2);
facultyfilters.AddWithAnd(filterIsOwnerM2);
facultyfilters.AddWithAnd(filterM2FacultyIdNotEqualM1FacultyId);


IEntityRelation facultyViewAllFacultyUserLogTankRelation = FacultyUserLogTankEntity.Relations.FacultyEntityUsingFacultyId;
IEntityRelation facultyUserLogToMembershipTankRelation = FacultyMembershipTankEntity.Relations.FacultyUserLogTankEntityUsingFacultyUserLogTankId;
IEntityRelation facultyMembershipToRoleTankRelation = FacultyMembershipTankEntity.Relations.RoleEntityUsingRoleId;
IEntityRelation roleTypeRelation = RoleTypeEntity.Relations.FacultyMembershipTankEntityUsingRoleTypeId;
IEntityRelation roleRelation = RoleEntity.Relations.FacultyMembershipTankEntityUsingRoleId;
IEntityRelation userLogRelation = UserLogEntity.Relations.FacultyUserLogTankEntityUsingUserLogId;

IRelationCollection facultyRelations = new RelationCollection();
facultyRelations.Add(facultyViewAllFacultyUserLogTankRelation);
facultyRelations.Add(facultyUserLogToMembershipTankRelation);
facultyRelations.Add(facultyMembershipToRoleTankRelation, FACULTY_MEMBERSHIP_ALIAS_M2).CustomFilter = facultyfilters;
facultyRelations.Add(roleTypeRelation);
facultyRelations.Add(roleRelation);
facultyRelations.Add(userLogRelation);

UserLogCollection userLogCollection = new UserLogCollection();
userLogCollection.GetMulti(null, facultyRelations);

This code is probably way off track, as I get the exception that M2 doesn't match the table name or alias used in the query, or typically get Bad Alias. How does one create expressions like

AND M2.membership <> M1.membership
AND M2.facultyID = 1 AND M2.IsOwner = 1

Maybe if I could see an example of this, I could learn how to do it.

Thanks so much for your valued assistance!

Isz

Here is the T-SQL that does exactly what I am looking for. As you can see it implements your SQL example just like you said, but I added a join to faculty_user_log_tank which is an intermediary between faculty and user_log. Also, role_type_id = 2 really means IsOwner = true... (fyi)

SELECT *
FROM user_log
INNER JOIN faculty_user_log_tank ON faculty_user_log_tank.user_log_id = user_log.user_log_id
INNER JOIN faculty_membership_tank M1 ON M1.faculty_user_log_tank_id = faculty_user_log_tank.faculty_user_log_tank_id
INNER JOIN role ON role.role_id = M1.role_id
INNER JOIN faculty_membership_tank M2 ON M2.role_id = M1.role_id AND M2.faculty_membership_tank_id <> M1.faculty_membership_tank_id
AND M2.faculty_user_log_tank_id = 1 AND M2.role_type_id = 2

If you could help me write the LLBL code I think I could begin to understand how to implement this join-whithout-sub-query strategy elsewhere in my searches.

Thanks so much for getting me started on the right track!

Isz wrote:

Here is the T-SQL that does exactly what I am looking for. As you can see it implements your SQL example just like you said, but I added a join to faculty_user_log_tank which is an intermediary between faculty and user_log. Also, role_type_id = 2 really means IsOwner = true... (fyi)

SELECT *
FROM user_log
INNER JOIN faculty_user_log_tank ON faculty_user_log_tank.user_log_id = user_log.user_log_id
INNER JOIN faculty_membership_tank M1 ON M1.faculty_user_log_tank_id = faculty_user_log_tank.faculty_user_log_tank_id
INNER JOIN role ON role.role_id = M1.role_id
INNER JOIN faculty_membership_tank M2 ON M2.role_id = M1.role_id AND M2.faculty_membership_tank_id <> M1.faculty_membership_tank_id
AND M2.faculty_user_log_tank_id = 1 AND M2.role_type_id = 2

If you could help me write the LLBL code I think I could begin to understand how to implement this join-whithout-sub-query strategy elsewhere in my searches.

Thanks so much for getting me started on the right track!

The relations should be added to the relationcollection. As you're using additional clauses for the ON clause for the join of faculty_membership_tank, you should set for that relation the CustomFilter. Please see: "Using the generated code -> Selfservicing -> Filtering/sorting -> Advanced filter usage -> Custom filters for EntityRelations. "

Thank you for directing me to this part of the help file. I have already been through this part a few times and unfortunately it has not helped. My problem, I think, is not how to append the custom filter to a relation, or what realtion to append it to, I think I got that part right, but it is how to write the filter predicates used by the custom filter, especially as it relates to using the required alias...

INNER JOIN faculty_membership_tank M2 ON M2.role_id = M1.role_id AND M2.faculty_membership_tank_id <> M1.faculty_membership_tank_id

Stumped, but grateful.

Thanks!

Isz

Please check this function overloadin the LLBLGen Pro v1.0.2005.1 reference manual:

public FieldCompareExpressionPredicate( IEntityFieldCore field, IFieldPersistenceInfo persistenceInfo, ComparisonOperator operatorToUse, IExpression expressionToCompare, string objectAlias );

where

objectAlias Alias for the object the field belongs to. Used to identify which entity to use when the entity is present multiple times in a relation collection. Alias has to match an alias specified in the relation collection or should be left empty if no alias is specified (or no relation collection is used). In that case, use another overload.

You may also check these threads: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=4356 http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3672

I don’t think I could have done this without finally adding the SQL output in the config file. Without it, it’s like operating blind folded. Anyway, I thought you would be interested in the solution.

Problem: Select all the users who have a membership to the roles that are owned by another user. As this relates to my mom’s app, basically teachers are permitted to set up roles which other students and other teachers can be have membership. A teacher may own the following roles:

1. Homework Graders
2. Web Assistants
3. Substitute Teachers
4. Teacher Assistants

All of the above roles may be generically created by a faculty member that can additionally assign permissions that define each role. Part of my app has a search function which allows the faculty member owning the roles [like a teacher] to search for all users who have a membership to the roles the faculty member owned. This sounded like a sub-query until reading the tiny forum where Otis ans Walaa always say a sub-query should be rewritten as a join.

Solution: Here is the SQL code that I knew would work:

SELECT DISTINCT user_log.user_log_id
FROM user_log
INNER JOIN faculty_user_log_tank ON user_log.user_log_id = faculty_user_log_tank.user_log_id
INNER JOIN faculty_membership_tank ON faculty_membership_tank.faculty_user_log_tank_id = faculty_user_log_tank.faculty_user_log_tank_id
INNER JOIN role ON role.role_id = faculty_membership_tank.role_id
INNER JOIN faculty_membership_tank M2 ON
M2.role_id = faculty_membership_tank.role_id 
AND M2.faculty_membership_tank_id <> faculty_membership_tank.faculty_membership_tank_id
AND M2.role_type_id = 2
INNER JOIN faculty_user_log_tank F2 ON M2.faculty_user_log_tank_id = F2.faculty_user_log_tank_id
AND F2.faculty_id = 1

Here is the corresponding LLBL code I finally figured out how to transpose:

string FACULTY_USER_LOG_ALIAS = "FacultyUserLogAlias";
string FACULTY_MEMBERSHIP_ALIAS = "FacultyMembershipAlias";

IPredicateExpression facultyfilters1 = new PredicateExpression();
IPredicate filterIsOwner = PredicateFactory.CompareValue(FacultyMembershipTankFieldIndex.RoleTypeId, ComparisonOperator.Equal, (int)SuzanneBConrad.Utilities.Enumerations.RoleType.Ownership, FACULTY_MEMBERSHIP_ALIAS);
IPredicate filterM2RoleIDIsEqualM1RoleId = 
    PredicateFactory.CompareExpression(FacultyMembershipTankFieldIndex.RoleId, 
    ComparisonOperator.Equal, new Expression(EntityFieldFactory.Create(FacultyMembershipTankFieldIndex.RoleId)), FACULTY_MEMBERSHIP_ALIAS);
IPredicate filterM2FacultyUserLogTankIdNotEqualM1FacultyUserLogTankId = 
    PredicateFactory.CompareExpression(FacultyMembershipTankFieldIndex.FacultyUserLogTankId, 
    ComparisonOperator.NotEqual, new Expression(EntityFieldFactory.Create(FacultyMembershipTankFieldIndex.FacultyUserLogTankId)), FACULTY_MEMBERSHIP_ALIAS);
facultyfilters1.AddWithAnd(filterIsOwner);
facultyfilters1.AddWithAnd(filterM2RoleIDIsEqualM1RoleId);
facultyfilters1.AddWithAnd(filterM2FacultyUserLogTankIdNotEqualM1FacultyUserLogTankId);

IPredicateExpression facultyfilters2 = new PredicateExpression();
IPredicate filterFacultyId = PredicateFactory.CompareValue(FacultyUserLogTankFieldIndex.FacultyId, ComparisonOperator.Equal, FacultyCourseServer.DEFAULT_FACULTY_ID, FACULTY_USER_LOG_ALIAS);
facultyfilters2.AddWithAnd(filterFacultyId);

IEntityRelation facultyUserLogTankRelation2 = FacultyUserLogTankEntity.Relations.UserLogEntityUsingUserLogId;
IEntityRelation facultyMembershipTankRelation2 = FacultyMembershipTankEntity.Relations.FacultyUserLogTankEntityUsingFacultyUserLogTankId;
IEntityRelation roleRelation2 = RoleEntity.Relations.FacultyMembershipTankEntityUsingRoleId;
IEntityRelation facultyMembershipTankToRoleRelation2 = RoleEntity.Relations.FacultyMembershipTankEntityUsingRoleId;
IEntityRelation facultyMembershipTankToFacultyUserLogTankRelation2 = FacultyMembershipTankEntity.Relations.FacultyUserLogTankEntityUsingFacultyUserLogTankId;

IRelationCollection facultyRelations = new RelationCollection();
facultyRelations.Add(facultyUserLogTankRelation2);
facultyRelations.Add(facultyMembershipTankRelation2);
facultyRelations.Add(roleRelation2);
facultyRelations.Add(facultyMembershipTankToRoleRelation2, FACULTY_MEMBERSHIP_ALIAS).CustomFilter = facultyfilters1;
facultyRelations.Add(facultyMembershipTankToFacultyUserLogTankRelation2, FACULTY_MEMBERSHIP_ALIAS, FACULTY_USER_LOG_ALIAS, JoinHint.Inner).CustomFilter = facultyfilters2;

UserLogCollection userLogCollection = new UserLogCollection();
userLogCollection.GetMulti(null, facultyRelations);

SUMMARY: Long & the short of it is it required two aliases and consequently two customFilters. The last relation required both aliases as I needed to know who the faculty member that owned the roles was. Adding the JoinHint was simply a requirement to match the overload when using two Aliases within the Add method. Doing this gets tricky because adding an alias to a relation requires the correct entity to be joined on. For example:

IEntityRelation facultyMembershipTankToRoleRelation2 = FacultyMembershipTankEntity.Relations.RoleEntityUsingRoleId;

Which is equivalent to:

INNER JOIN [dbo].[role] [LPA_F1]  ON  [LPA_F1].[role_id]=[dbo].[faculty_membership_tank].[role_id])

Instead, I had to use:

IEntityRelation facultyMembershipTankToRoleRelation2 = RoleEntity.Relations.FacultyMembershipTankEntityUsingRoleId;

Which is equivalent to:

INNER JOIN [dbo].[faculty_membership_tank] [LPA_F1]  ON  [dbo].[role].[role_id]=[LPA_F1].[role_id]

FacultyMembershipTankEntity.Relations.RoleEntityUsingRoleId;

And

RoleEntity.Relations.FacultyMembershipTankEntityUsingRoleId;

Seem to me to be equivalent, but apparently this makes a big difference when specifying an alias.

Rewriting a subquery to a join was before subquery support was added to the framework. Subqueries can be slower than joins which also can be a reason to rewrite it to use a join instead, and it can be simpler to write down.

You needed multiple aliases. Keep in mind this simple rule: the relation you add to the relationcollection has a start entity and an end entity. Example: CustomerEntity.Relations.OrderEntityUsingCustomerId here, customer is the start entity, order is the end entity. So if I want to alias Customer to 'C', I have to specify 'C' as the alias for the start entity when adding the relation to the relationcollection. For order, the same, I then have to specify the alias for the end entity when adding the relation to the relationcollection.

Alias only if you join an entity multiple times. aliasing is required to be able to specify which entity your filter works on.

CustomFilter filters aren't always necessary. Often you can move the filters in the ON clause also to the WHERE clause, which means to the normal predicate expression.

I think those were the problems you were facing earlier in this thread.

About needing the trace log: the tracing is there to check if you're doing it right when you get results you didn't expect. if you think solely in entities, their fields, the relations, the filters on entities, it's tic-tac-toe and you don't need the sql. I'm not familiar with the relations in your model so I can't clearly say which entities you had to walk to get the results. Say your model looks like:

User 1:n UserRole m:1 Role (for membership) and User 1:n OwnerRole m1: Role (for ownership)

so if I want to see all users in the roles owner by user 1, I have to read the roles owned by user 1 and use them to read the users from userrole.

so pseudo: select * from user where userid in ( select userid from userrole where roleid in (select roleid from ownerrole where userid = @ownerid))

always start from the entity you want to fetch and work down to the entity/entities you want to filter on. So this gives

UserCollection users = new UserCollection();
PredicateExpression filter = new PredicateExpression();
filter.Add(new FieldCompareSetPredicate(
    UserFields.UserId, UserRoleFields.UserId, SetOperator.In,
        // ownerrole subquery
        new FieldCompareSetPredicate(
            UserRoleFields.RoleId, OwnerRoleFields.RoleId, SetOperator.In,
                (OwnerRoleFields.UserId == 1))));
users.GetMulti(filter);

Thank you for the excellent counsel... this was a good learning experience for me.