Page 1 of 1

Conditional Subquery?

Posted: Tue Oct 21, 2014 6:20 pm
by eric.graham
I need to know if the LSPS tool supports conditional subqueries and, if so, how to create one? In particular, I need to use the EXISTS Sub-Query.

SQL Example
Code: Select all
WHERE EXISTS ( -- This is my subquery that I want to conditionally be present.
    SELECT *
    FROM TableA AS a
    INNER JOIN TableB AS b ON b.aId =
    WHERE b.column1 = myValue

Java JPA Compliant Example
Code: Select all
CriteriaBuilder         builder = entityManager.getCriteriaBuilder();
CriteriaQuery<MyObject> query   = builder.createQuery(MyObject.class);

Root<MyObject> root = query.from(MyObject.class);;

List<Predicate> predicates = new ArrayList<>();
predicates.add(builder.equal(root.get("someFieldName"), someValue));
// Additional Predicates on the root query.

if (someCondition) {
    Subquery<MyObject> subQuery = query.subquery(MyObject.class);
    Root<MyObject>     subRoot  = subQuery.from(MyObject.class);;

   Join<MyObject, MyOtherObject> myOtherObjects = root.join("myOtherObjects");
   builder.where(builder.equal(myOtherObjects.get("someFieldName"), someOtherValue));
   // If required, we can have additional predicates on the sub-query.  Our actual use-case calls for only one.

query.where(predicates.toArray(new Predicate[predicates.size()]));



Re: Conditional Subquery?

Posted: Wed Oct 22, 2014 4:30 pm
by C. Tampitsikas
Hi Eric,

In general there are two ways to write a query in LSPS:

-The first way is to use the standard LSPS queries that access database data through shared records;
-The second way is to define native queries that are direct database queries.

Every time you click on "Add" query, you get a short list with the option to define a Query or a Native Query.
In case you select a native query you can easily write your SQL code for conditional queries exactly as you are doing at the SQL example of your post.

If you want to create a conditional query using the standard LSPS queries then you could follow the following approach:
-Define a standard query for your subquery (your INNER JOIN);
-Define a standard query for your main query that takes as a parameter the type of the results of your subquery;
-Pass as parameter the results of the subquery into the main query and at the condition field write a simple expression to see whether the parameter is empty or not.

If you decide to use the standard LSPS query approach you should keep in mind that the subquery is executed first and its results are saved in the memory before they are used as parameter for your main query