• State Not Answered
  • Replies 1 reply
  • Subscribers 98 subscribers
  • Views 42 views
  • Users 0 members are here
Options
Related

Angular Entity Filters - conditions

Ben

Is it possible to do and/or conditions with filters?

For example, the old Portal SQL had this clause:

IdentityType = 'Primary' AND IsInactive =0 AND UID_Person <> '{0}' AND (UID_PersonHead = (SELECT UID_PersonHead FROM Person WHERE UID_Person = '{0}') OR UID_PersonHead = '{0}')
So far, I've got some of it, but I need to AND => OR
public buildFilterForSubstitutees(): FilterData[] {
    const filter = [];
        // AND conditions
    filter.push({ColumnName: 'IdentityType', CompareOp: CompareOperator.Equal, Type: FilterType.Compare, Value1: 'Primary'});
    filter.push({ColumnName: 'IsInactive', CompareOp: CompareOperator.Equal, Type: FilterType.Compare, Value1: 0});
    filter.push({ColumnName: 'UID_Person', CompareOp: CompareOperator.NotEqual, Type: FilterType.Compare, Value1: this.uidPerson});
        // Group for OR conditons
        filter.push({ColumnName: 'UID_PersonHead', CompareOp: CompareOperator.Equal, Type: FilterType.Compare, Value1: this.personData.Entities[0]?.Columns?.UID_PersonHead?.Value });
        filter.push({ColumnName: 'UID_PersonHead', CompareOp: CompareOperator.Equal, Type: FilterType.Compare, Value1: this.uidPerson });
        // End OR and AND
    return filter;
}
btw, the Forum's Insert->Code wouldn't do anything on OK for the function - something was killing it.
Parents
  • 0

    Hello Ben,

    there are multiple solutions to your requirement. The two (frontend only) solutions I can think of, both require the use of an "ExpressionFilter" instead of the "CompareFilter" that you started with. The expression filters act almost like you'd expect a SQL where clause to work.

    Note: Make sure the "Show in wizards" flag is checked for all properties/columns used in the expression. 

        const uidPerson = this.uidPerson;
        const uidPersonHead = this.personData.Entities[0]?.Columns?.UID_PersonHead?.Value;

        // Approach 1: IN condition
        const filter1: FilterData[] = [
          {
            Type: FilterType.Expression,
            Expression: {
              LogOperator: LogOp.AND,
              Expressions: [
                { PropertyId: 'IdentityType', Operator: '=', Value: 'Primary', LogOperator: LogOp.AND },
                { PropertyId: 'IsInactive', Operator: '=', Value: 0, LogOperator: LogOp.AND },
                { PropertyId: 'UID_Person', Operator: '<>', Value: uidPerson, LogOperator: LogOp.AND },
                { PropertyId: 'UID_PersonHead', Operator: 'IN', Value: [uidPerson, uidPersonHead].filter(Boolean), LogOperator: LogOp.AND },
              ],
            },
          },
        ];

        // Approach 2: OR condition
        const filter2: FilterData[] = [
          {
            Type: FilterType.Expression,
            Expression: {
              LogOperator: LogOp.AND,
              Expressions: [
                { PropertyId: 'IdentityType', Operator: '=', Value: 'Primary', LogOperator: LogOp.AND },
                { PropertyId: 'IsInactive', Operator: '=', Value: 0, LogOperator: LogOp.AND },
                { PropertyId: 'UID_Person', Operator: '<>', Value: uidPerson, LogOperator: LogOp.AND },
                {
                  LogOperator: LogOp.OR,
                  Expressions: [
                    { PropertyId: 'UID_PersonHead', Operator: '=', Value: uidPerson, LogOperator: LogOp.AND },
                    { PropertyId: 'UID_PersonHead', Operator: '=', Value: uidPersonHead, LogOperator: LogOp.AND },
                  ].filter((expr) => !!expr.Value),
                },
              ],
            },
          },
        ];

    There are limits to what you can do with filter expressions (e.g. limited nesting, lack of joins or sub queries, etc.). If you already started modifying API endpoints or if you expect the filter to get more complicated, it might be worth considering adding the 3-5 lines for a custom endpoint that returns the filtered data. Here you can use plain SQL as a String or the safer expression builder.
    (IdentityManager.ApiSdk/doc/02-entity-methods.md at v93 · OneIdentity/IdentityManager.ApiSdk · GitHub)
Reply
  • 0

    Hello Ben,

    there are multiple solutions to your requirement. The two (frontend only) solutions I can think of, both require the use of an "ExpressionFilter" instead of the "CompareFilter" that you started with. The expression filters act almost like you'd expect a SQL where clause to work.

    Note: Make sure the "Show in wizards" flag is checked for all properties/columns used in the expression. 

        const uidPerson = this.uidPerson;
        const uidPersonHead = this.personData.Entities[0]?.Columns?.UID_PersonHead?.Value;

        // Approach 1: IN condition
        const filter1: FilterData[] = [
          {
            Type: FilterType.Expression,
            Expression: {
              LogOperator: LogOp.AND,
              Expressions: [
                { PropertyId: 'IdentityType', Operator: '=', Value: 'Primary', LogOperator: LogOp.AND },
                { PropertyId: 'IsInactive', Operator: '=', Value: 0, LogOperator: LogOp.AND },
                { PropertyId: 'UID_Person', Operator: '<>', Value: uidPerson, LogOperator: LogOp.AND },
                { PropertyId: 'UID_PersonHead', Operator: 'IN', Value: [uidPerson, uidPersonHead].filter(Boolean), LogOperator: LogOp.AND },
              ],
            },
          },
        ];

        // Approach 2: OR condition
        const filter2: FilterData[] = [
          {
            Type: FilterType.Expression,
            Expression: {
              LogOperator: LogOp.AND,
              Expressions: [
                { PropertyId: 'IdentityType', Operator: '=', Value: 'Primary', LogOperator: LogOp.AND },
                { PropertyId: 'IsInactive', Operator: '=', Value: 0, LogOperator: LogOp.AND },
                { PropertyId: 'UID_Person', Operator: '<>', Value: uidPerson, LogOperator: LogOp.AND },
                {
                  LogOperator: LogOp.OR,
                  Expressions: [
                    { PropertyId: 'UID_PersonHead', Operator: '=', Value: uidPerson, LogOperator: LogOp.AND },
                    { PropertyId: 'UID_PersonHead', Operator: '=', Value: uidPersonHead, LogOperator: LogOp.AND },
                  ].filter((expr) => !!expr.Value),
                },
              ],
            },
          },
        ];

    There are limits to what you can do with filter expressions (e.g. limited nesting, lack of joins or sub queries, etc.). If you already started modifying API endpoints or if you expect the filter to get more complicated, it might be worth considering adding the 3-5 lines for a custom endpoint that returns the filtered data. Here you can use plain SQL as a String or the safer expression builder.
    (IdentityManager.ApiSdk/doc/02-entity-methods.md at v93 · OneIdentity/IdentityManager.ApiSdk · GitHub)
Children
No Data