Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Complex, multi-table queries
 

Pages: 1
LLBLGen Pro Runtime Framework
Complex, multi-table queries
Page:1/1 

  Print all messages in this thread  
Poster Message
Alex Davidson
User



Location:
Bristol, UK
Joined on:
18-Sep-2006 17:51:08
Posted:
21 posts
# 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.

Code:

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:

Code:

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:
Code:

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.
  Top
bclubb
User



Location:
Norman, Oklahoma
Joined on:
12-Feb-2004 22:18:04
Posted:
934 posts
# Posted on: 13-Jan-2007 02:44:21.  
You can add these relations with aliases and then use these aliases when creating your query.

  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.