Conditional Subquery?

Discussions about modeling, execution and management of process/application data.
Forum rules
Make sure every topic contains information about your LSPS version and if relevant also your server OS, client OS, database name and version, and application server name and version.
eric.graham
 
Posts: 58
Joined: Tue May 13, 2014 12:20 pm

Conditional Subquery?

Tue Oct 21, 2014 6:20 pm

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
SELECT *
FROM TableA
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 = a.id
    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);
query.select(root);

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);
    subQuery.select(subRoot);

   Join<MyObject, MyOtherObject> myOtherObjects = root.join("myOtherObjects");
   myOtherObjects.alias("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.
   
   predicates.add(builder.exists(subQuery));
}

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

query.getResultList();


References:
JPA, JPQL, MySQL, SQL Server

C. Tampitsikas
 
Posts: 30
Joined: Wed Oct 22, 2014 3:04 pm

Re: Conditional Subquery?

Wed Oct 22, 2014 4:30 pm

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

Return to Data modeling

Who is online

Users browsing this forum: No registered users and 0 guests