How to achieve a LEFT JOIN between two tables

Q&A
1

Hi,

I have a table A and other B. Table B has an aggregated field referring table A.
I am trying to list all elements from table A not referenced in table B.
The goal is to show this list in a process Form.

How can I do it:

  • Through JPQL
  • Through Javascript

Thanks a lot !

2

@afotab

First as you guessed @gpscruise’s answer is for subscription only.

Second the database itself doesn’t matter, H2, PostgreSQL etc. What I was really trying to solicit was the I’m using BDM or I’m using MySQL part as this was not clear in the question (you said table rather than BDM, big difference as far as I’m concerned :slight_smile:

To answer the question, in this version of Bonitasoft, 7.4.x. Unfortunately it is NOT possible to build compound statements like you are asking.

BDM queries are strictly limited to the BDM where they are specified and cannot be built outside, which is where you would want to do this.

So for example:

select * from A - can only reference BDM A where is is built
select * from B - can only reference BDM B where is is built

but

Select A.f1, b.f2 from A join B where A.f3 = B.f3 cannot be built at all.

This is a function of how Bonitasoft have designed/built the BDM model as it stands.

I admit it is limited but most of my data is outside in PostgreSQL so this doesn’t matter to me.

However there is hope, maybe tomorrow this might be addressed in 7.5 beta (though possibly unlikely), it will be announced on the blog so keep an eye on it. No - I have no idea what is in 7.5 so can’t say.

If not then i would suggest adding an Idea for future development here http://community.bonitasoft.com/ideas, or create a bug here https://bonita.atlassian.net/secure/Dashboard.jspa, stating the issue and goals you are trying to achieve and as such why you consider it a bug.

regards
Seán

PS: While this may not be the complete answer you’re hoping for, it does indicate a possible solution, please mark as resolved.

3

this looks like a good start…
http://documentation.bonitasoft.com/?page=graphical-query-builder

select a., b. from a left join b on a.blah=b.blah

4

Table?

This is outside the scope of Bonitasoft and you should refer to the databases correct documentation, PostgreSQL, MySQL etc. and as such not a Bonitasoft question.

However I would write a either/or:

  • Custom REST API to get the data, or
  • AJAX piece of code calling a web service to read and return the data.

Are you able to be more specific about where the data is coming from?

regards
Seán

5

Thanks for the comments.

@gpscruise:

That would be maybe a good way to start, but as far as I know this tool is not available for Community version.
My problem is not writing the SQL sentence but how to access it from Bonita.
I am using the embedded H2 database, and creating custom queries using the provided Business Data Model Manager.

@Sean:
I am trying to do it through provided H2 database.
I’d like to avoid development of custom code - that’s one main reason for trying Bonita.

What I am trying to do is simply listing the elements of a Business object A excluding the ones that are being used in a Business object B, in an aggregated field referring object A.
I need to list this result in a table widget on a instantiation form of Business object B.

Can this so difficult to get ? I guess this should be so simple I’m just missing how to do it.

Thanks.

6

Hi Sean,

Couldn’t I in the form do the following? :

  • create two variables accessing the content of both BDM objects via REST API
  • create a variable with javascript where I’d iterate the previous variables and exclude the values if present in the second variable ?

Do you see this possible using javascript within the form ?

Thanks.

7

You could yes, but that would be very messy and fraught with update issues.

What I would do is use a database Connector, connect to the BDM database and read the data the way you want, with a join, using proper database methodologies…

You can save the returnset in a variable that is accessible by the form and then use that.

Again though I would add the idea to the Ideas forum for future inclusion as it does limit capabilities of BDMs

regards
Seán

8

Hi Seán,

Thanks again for your input.
I’ve managed to configure a connector doing a SELECT from a table, and put it in a process variable.
How can I access it from the form ?

The examples I’ve seen refer to using something as …/{{context.myBusinessData_ref.link}} but:

  • In a instantiation form I don’t think I have access to …//{{context}}
  • I don’t know if a process variable is considered myBusinessData

Can you point me in the right direction on how to access the process variable from an instantiation form ?

Thanks again.