Complex predicates with "boxed" predicates

[Frank]

Hi all,

I am using QueryDsl 3.2.3 on a project and have had great success so far.  I have now run into a query for which I cannot yet find an answer.

I have a need to create multiple complex predicates boxed on a single query.

Something like

  Select A from tableA where (tableA.name = "Test" and tableA.active = true) or (tableA.name = "Yippee!" and tableA.active = false) ...

I have tried many permutations of BooleanBuilder and it all ends up lumping everything together without needed grouping parentheses.

e.g.:
  compartmentDto.name = Test && compartmentDto.active = true || groupDto.name = Test && groupDto.active != false

what I am searching for:
  (compartmentDto.name = Test && compartmentDto.active = true) || (groupDto.name = Test && groupDto.active != false)

I know that building the raw SQL works to return the data.  I just cannot figure out how to define the Predicate to get this same result.

Thanks in advance!
Frank

[Ruben Dijkstra]

Hello,

That is strange, since and has precedence over or.

Could you shed some light on how you constructed the query, and for which database vendor(Templates) you construct the query?

[Timo Westkämper]

Hi.

How does your SQL query look like?

Br,

Timo

--
You received this message because you are subscribed to the Google Groups "Querydsl" group.
To unsubscribe from this group and stop receiving emails from it, send an email to querydsl+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Frank]

How does your SQL query look like?

My apologies for the delay.  I did not receive any email notifications that a response had been made.  I've rectified that for the future.

I am using CollQuery because I am trying to perform a query against a collection of objects already in memory.  To my previous mention of SQL, I can build up a SQL query to retrieve the data if I mock it up in a database, but I now think that I had used subqueries when doing that.

I am currently on a project that is using Oracle Label Security to protect their data in the database and they want to use a separate metadata tagging system to generate the OLS caveats.  I am trying to build a translation system between the OLS caveats and the metadata tags.  One looper here is that the metadata management is handled by a separate web service that I must call to get the pre-defined metadata combinations.  For performance reasons, I do not want to call that service for every persisted record.  Instead I create a Collection of the metadata objects to hold in cache and refresh regularly.

I do not have access to the Q classes on the other side of the service so I am also using alias and $() helpers.

So here are my classes:

class SecurityMetaData {
  private Long id;
  private ClassificationTag classification;
  // This collection holds all tags that are required (all) when using this metadata object.
  private Set<LabelTagsRequired> requiredTags;
  // This collection holds all tags that are optional (any, but user must have at least one) when using this metadata object.
  private Set<LabelTagsOptional> optionalTags;

  public String toString(){
    // Over simplified - there is parsing and formatting that happens to make this pretty.
    return classification.toString()+requiredTags.toString()+optionalTags.toString();
  }
}

class LabelTagsRequired extends LabelTag {
}

class LabelTagsOptional  extends LabelTag {

}

class ClassificationTag extends LabelTag {

}

abstract class LabelTag {
  protected Long id;
  protected String publicName;
  protected String publicDescription;
  protected String olsName;
}

So the issue I am having is when I want to query for a specific SecurityMetadata object. 

Sample data where tags are delimited by "/" and the fields are delimited by "|" :

sensitive|accounting/finance|proposals/businessdev
private|accounting/operations|activeCustomer/customerHistory
public|accounting/finance
public|finance/operations
sensitive|accounting/finance
sensitive|finance
sensitive|proposals

The tags are not guaranteed to ever come to me in a consistent order and some strings do not contain required tags while others contain a required tag and not an optional tag.

Here is what I am working with:

        // The following values are pre-parsed and collected:
        String classificationTag;
        List<String> requiredTags;
        List<String> optionalTags;
        BooleanBuilder builder = new BooleanBuilder();

        SecurityMetadata metadataAlias =
            alias(SecurityMetadata.class, "securityMetadata");
        LabelTagsRequired requiredAlias =
            alias(LabelTagsRequired.class, "labelTagsRequired");
        LabelTagsOptional optionalAlias = alias(LabelTagsOptional.class, "labelTagsOptional");

        boolean optionalFilterAdded = false;

        CollQuery query =
            new CollQuery().from($(metadataAlias), securityMetadataCache);
        query.leftJoin($(metadataAlias.getRequiredTags()), $(requiredAlias));
        query.leftJoin($(metadataAlias.getOptionalTags()), $(optionalAlias));

        builder.and($(metadataAlias.getClassificationTag().getPublicName()).eq(
            classificationTag));
        if (requiredTags != null && requiredTags.size() > 0)
        {
          BooleanBuilder requiredBuilder = new BooleanBuilder();
          if (optionalTags == null || optionalTags.isEmpty())
          {
            requiredBuilder.and($(requiredAlias.getPublicName())
                .in(requiredTags)
                .and(
                    $(requiredAlias.getPublicName())
                        .notIn(requiredTags).not())
                .and($(metadataAlias.getOptionalTags()).isEmpty()));
            requiredBuilder.or(($(optionalAlias.getPublicName())
                .in(requiredTags)).and(
                ($(optionalAlias.getPublicName()).notIn(requiredTags)).not())
                .and($(metadataAlias.getRequiredTags().isEmpty())));
            optionalFilterAdded = true;
          }
          else
          {
            requiredBuilder.and($(requiredAlias.getPublicName()).in(
                requiredTags).and(
                $(requiredAlias.getPublicName()).notIn(requiredTags)
                    .not()));
          }
          builder.and((requiredBuilder));
        }
        else
        {
          builder.and($(metadataAlias.getRequiredTags().isEmpty()));
        }
        if (!optionalFilterAdded)
        {
          BooleanBuilder optionalBuilder = new BooleanBuilder();
          if (optionalTags != null && optionalTags.size() > 0)
          {
            optionalBuilder.and(($(optionalAlias.getPublicName()).in(optionalTags))
                .and($(optionalAlias.getPublicName()).notIn(optionalTags).not()));
          }
          else
          {
            optionalBuilder.and($(metadataAlias.getOptionalTags().isEmpty()));
          }
          builder.and(optionalBuilder);
        }

        query.where(builder);
        for (SecurityMetadata data : query.list($(metadataAlias)))
        {
          returnVal = data;
        }

Here is the resulting builder.getValue() when searching for sensitive|finance.  The results come back with multiple records; all of the sensitive records that contain "finance" in the required tags.

securityMetadata.classificationTag.publicName = sensitive && labelTagsRequired.publicName = finance && !labelTagsRequired.publicName != finance && empty(securityMetadata.optionalTags) || labelTagsOptional.publicName = finance && !labelTagsOptional.publicName != finance && securityMetadata.requiredTags.empty

Here is the resulting builder.getValue() when searching for sensitive|proposals.  There are no results found despite my being able to review the collection and know that it does exist in there.

securityMetadata.classificationTag.publicName = sensitive && labelTagsRequired.publicName = proposals && !labelTagsRequired.publicName != proposals && empty(securityMetadata.optionalTags) || labelTagsOptional.publicName = proposals && !labelTagsOptional.publicName != proposals && securityMetadata.requiredTags.empty

I can interrogate the builder object and see that there are several levels of arguments and that they appear to break apart at the right places.  But I am not able to verify the boxing.

Any thoughts?

[timowest]

Hi Frank.

Sorry for the late reply. With the latest version of Querydsl the serialization of expressions should contain all the necessary parentheses.

Timo

[Frank]

Getting closer...

So I can now see a first set of enclosing parentheses but I am trying to embed another evaluation inside of the first. 

Going back to my previous example, my goal is below.  I left the parentheses provided by the latest version to show that they are there.

securityMetadata.classificationTag.publicName = sensitive && ((labelTagsRequired.publicName = finance && !(labelTagsRequired.publicName != finance) && empty(securityMetadata.optionalTags)) || (labelTagsOptional.publicName = finance && !(labelTagsOptional.publicName != finance) && empty(securityMetadata.requiredTags)))

Notice that the OR conditional should be evaluating complex predicates on either side.  I am not seeing or believing that it is being evaluated properly.

I have tried a couple of different ways to get the additional markings.  The one that seemed to make sense to me was to add separate builders because simply specifying a new set of parentheses has never seemed to do the trick for me, either.  An excerpt change from above:

BooleanBuilder requiredBuilder = new BooleanBuilder();

BooleanBuilder requiredBuilder1 = new BooleanBuilder();
BooleanBuilder requiredBuilder2 = new BooleanBuilder();

          if (optionalTags == null || optionalTags.isEmpty())
          {

            requiredBuilder1.and($(requiredAlias.getPublicName())

                .in(requiredTags)
                .and(
                    $(requiredAlias.getPublicName())
                        .notIn(requiredTags).not())
                .and($(metadataAlias.getOptionalTags()).isEmpty()));

            requiredBuilder2.or(($(optionalAlias.getPublicName())
                .in(requiredTags)).and(
                ($(optionalAlias.getPublicName()).notIn(requiredTags)).not())
                .and($(metadataAlias.getRequiredTags().isEmpty())));
            requiredBuilder.or((requiredBuilder1)).or((requiredBuilder2)); //Notice I kept the extra parentheses just in case it does make a difference.  It doesn't:(
            optionalFilterAdded = true;
                      }

builder.and(requiredBuilder);

More thoughts??

Thanks for the help!

[Frank]

FYI - I have also tried the .andAnyOf(requiredBuilder1, requiredBuilder2) because that seemed pretty logical, too.  Unfortunately, it still appears that the predicates are not evaluated / grouped as I was hoping.

Regards,
Frank

[timowest]

Did you try to simplify it?

securityMetadata.classificationTag.publicName = sensitive 

&& (

  (labelTagsRequired.publicName = finance && !(labelTagsRequired.publicName != finance) && empty(securityMetadata.optionalTags)) 

  || 

  (labelTagsOptional.publicName = finance && !(labelTagsOptional.publicName != finance) && empty(securityMetadata.requiredTags))

)

should be the same as

securityMetadata.classificationTag.publicName = sensitive 

&& (

  (labelTagsRequired.publicName = finance && empty(securityMetadata.optionalTags)) 

  || 

  (labelTagsOptional.publicName = finance && empty(securityMetadata.requiredTags))

)

Also are  you sure there are no null properties involved? 

Timo

[Frank]

On Thursday, August 14, 2014 11:37:23 AM UTC-4, timowest wrote:

Did you try to simplify it?

securityMetadata.classificationTag.publicName = sensitive 

&& (

  (labelTagsRequired.publicName = finance && !(labelTagsRequired.publicName != finance) && empty(securityMetadata.optionalTags)) 

  || 

  (labelTagsOptional.publicName = finance && !(labelTagsOptional.publicName != finance) && empty(securityMetadata.requiredTags))

)

should be the same as

securityMetadata.classificationTag.publicName = sensitive 

&& (

  (labelTagsRequired.publicName = finance && empty(securityMetadata.optionalTags)) 

  || 

  (labelTagsOptional.publicName = finance && empty(securityMetadata.requiredTags))

)

Also are  you sure there are no null properties involved? 

So you are hitting on the situations where the the simple approach would not work.  I did try that basic matching without the exclusion and without the OR conditional.  I received data but it was not a single record and not the one I wanted.
  - first, the queries need to be .in() and not .eq() because a I do need to match against multiple tags for a given match
  - second, I have to have the exclusion because there are some tag combinations that are, themselves, subsets of a larger.

e.g. If I only want sensitive data for the accounting and finance departments then a simple .in() would also return the tag containing management.
sensitive|accounting/finance|proposals/businessdev
sensitive|accounting/finance/management
sensitive|accounting/finance
sensitive|finance

  -I am trying to avoid NULL properties.  All of the accessors are generating an empty collection upon access on the class itself so that I should never run into a NPE.

So - back to the previous point - let's put this in a different light and break down my statements.

1 = securityMetadata.classificationTag.publicName = sensitive
2 = labelTagsRequired.publicName
3 = !(labelTagsRequired.publicName != finance)
4 = empty(securityMetadata.optionalTags)
5 = labelTagsOptional.publicName = finance
6 = !(labelTagsOptional.publicName != finance)
7 = empty(securityMetadata.requiredTags)
8 = 2 & 3 & 4
9 = 5 & 6 & 7
10 = 8 || 9

Element 1 is fine.
Elements 2, 3, 4 should all be evaluated together as a logical AND.  Identified as element 8.
Elements 5,6,7 should all be evaluated together as a logical AND.  Identified as element 9.
Elements 8, 9 should be evaluated as a logical OR. Identified as element 10.

The problem, as I see it, is that elements 8 and 9 are not being separately evaluated before the OR.  Unless I missed something, the code is stepping from 2 to 3 to 4 to 5 to 6 to 7.

I will take some time to set up a simple project to share exactly what I am running into.  Please bear with me for a few more days.