- Home
- LLBLGen Pro
- LLBLGen Pro Runtime Framework
Complex, multi-table queries
Posts
Posts: 21
Joined: 18-Sep-2006
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.