Complex, multi-table queries

Posts   
 
    
Posts: 21
Joined: 18-Sep-2006
# Posted on: 12-Jan-2007 18:09:42   

Using LLBLGen 2.0 with the Adapter model.

I'm trying to formulate a single complex query related to security, and I keep getting an SQL error claiming that it can't find a field. This is likely because I haven't told it to include that table, but the only way to do that is via an INNER JOIN with LLBLGen, unless I've missed something.


public static void BuildPredicate(IRelationPredicateBucket predicate, UserEntity user, SecurityPermission operation)
        {
            RelationCollection relations = new RelationCollection();
            Predicate security = BindUserGlobalPermission(relations, user);



            //BindPatientGroups(predicate);
            //BindUserGroupGlobalPermission(predicate, user);

            predicate.Relations.AddRange(relations);
            predicate.PredicateExpression.AddWithAnd(security & BindPermission(relations, operation));
        }

        /// <summary>
        /// Constrains the query to the specified security permission.
        /// </summary>
        /// <param name="operation"></param>
        /// <returns></returns>
        private static Predicate BindPermission(IRelationCollection relations, SecurityPermission operation)
        {
            return (SecurityHistoryFields.SecurityPermission == operation);
        }

        private static Predicate BindUserGlobalPermission(IRelationCollection relations, UserEntity user)
        {
            relations.Add(UserSecurityEntity.Relations.GetSuperTypeRelation());
            relations.Add(SecurityGroupSecurityPrivilegeEntity.Relations.GetSuperTypeRelation());
            // Single permissions.
            PredicateExpression single = (UserSecurityFields.PUserId == user.ParentId)
                                       & (UserSecurityFields.IsCurrent == true);
            PredicateExpression group = (UserSecurityGroupFields.SecurityGroupId == SecurityGroupSecurityPrivilegeFields.SecurityGroupId)
                                      & (SecurityGroupSecurityPrivilegeFields.IsCurrent == true)
                                    
                                      & (UserSecurityGroupFields.PUserId == user.ParentId)
                                      & (UserSecurityGroupFields.IsCurrent == true);
            return (single | group);
        }


The query I get is something akin to the following, after removal of confusing brackets. This is on MySQL at present:


Query:
    SELECT `t_security_history`.securityPermission AS SecurityPermission FROM
        (
            ( `t_security_history` INNER JOIN `t_user_security` ON `t_security_history`.`id`=`t_user_security`.`id` )
            INNER JOIN `t_security_group_security_privilege` ON `t_security_history`.`id`=`t_security_group_security_privilege`.`id`
        )
        WHERE
        (
            (
                (
                    `t_user_security`.pUserId = @PUserId1 AND `t_security_history`.isCurrent = @IsCurrent2
                 OR 
                    (
                        `t_user_security_group`.securityGroupId = `t_security_group_security_privilege`.securityGroupId
                    AND `t_security_history`.isCurrent = @IsCurrent3
                    AND `t_user_security_group`.pUserId = @PUserId4
                    AND `t_history`.isCurrent = @IsCurrent5
                    )
                )
            AND `t_security_history`.securityPermission = @SecurityPermission6
            )
        )

Parameter: @PUserId1 : AnsiString. Length: 32. Precision: 0. Scale: 0. Direction: Input. Value: "00000000000000000000000000000001".
Parameter: @IsCurrent2 : Byte. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
Parameter: @IsCurrent3 : Byte. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
Parameter: @PUserId4 : AnsiString. Length: 32. Precision: 0. Scale: 0. Direction: Input. Value: "00000000000000000000000000000001".
Parameter: @IsCurrent5 : Byte. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.
Parameter: @SecurityPermission6 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.

MySQL's error was:


Unknown column 't_user_security_group.securityGroupId' in 'where clause'

I can't see any way of adding tables to the FROM clause without using INNER JOINs. However, adding INNER JOINs is going to break things later, as the query grows in size and starts referencing the same tables multiple times in different ways.

bclubb
User
Posts: 934
Joined: 12-Feb-2004
# Posted on: 13-Jan-2007 02:44:21   

You can add these relations with aliases and then use these aliases when creating your query.