Resolved: Join table with Query Builder on aggregate alias

Questions and answers on designing your Servoy solutions, database modelling and other 'how do I do this' that don't fit in any of the other categories

Resolved: Join table with Query Builder on aggregate alias

Postby joe26 » Fri Apr 01, 2016 1:09 am

HI,

I'm building a summary table with the Query Builder and have gotten an error attempting to join two QB queries. I'm getting a 'could not resolve column' issue on the join,
as it will not recognize the aliased 'total_marks' column on a join. What might i be doing wrong?

I've tried other attempts with various QB commands, and extra columns have been removed in the code below. Tried placing a 'total_marks' calculation on the table, also to no avail.
I'm building a table to review one table, constrained by elements of other tables.

Additional solution stuff...

Joins can only be made within the same server, between different inmem-datasources is also possible.

Rob Gansevles (thanks! Couldn't get out of the forest for the trees.)

So this one is out.

Returned to the original joins and finally resolved to the fact that a table will have unique rows, upon join, there is a possibility of duplicate column data within rows which necessitates the inclusion of said rows into a groupBy statement, which only exist on QBSelect statements, not QBJoins.

Creating a foundset from a datasource can be summed up from a Query Builder statement, as such:
var pmST = databaseManager.createDataSourceByQuery('pmSTxx',st,-1);//st is the createselect query, um, root...?
var fsST = databaseManager.getFoundSet(pmST);
fsST.loadAllRecords(); // foundset is memory-based, but is form independent

---------- current working QB Statements -------------
/** @type {QBSelect<db:/stsservoy/sheets>} */
var st = databaseManager.createSelect('db:/stsservoy/sheets');
st.sort.add(st.columns.sheet_number);
st.where.add(st.columns.delete_flag.isNull);
st.where.add(st.columns.job_id.eq(jobId));
st.where.add(st.columns.tenant_uuid.eq(globals.secCurrentTenantID));
if (criteria.sheetnuma && criteria.sheetnuma.length > 0){
st.where.add(st.columns.sheet_number.isin(criteria.sheetnuma));
}
/** @type {QBSelect<db:/stsservoy/marks>} */
var pmm = st.joins.add('db:/stsservoy/marks');
pmm.on.add(st.columns.sheet_id.eq(pmm.columns.sheet_id));
if (criteria.marksa && criteria.marksa.length == 1){
st.where.add(pmm.columns.mark.like("%"+criteria.marksa.pop()+"%"));
}
if (criteria.marksa && criteria.marksa.length == 2){ // I'd really like an iteration here of some type
st.where.add(st.or
.add(pmm.columns.mark.like("%"+criteria.marksa.pop()+"%"))
.add(pmm.columns.mark.like("%"+criteria.marksa.pop()+"%"))
)
}
st.result.distinct;
st.sort.add(pmm.columns.mark);
st.sort.add(pmm.columns.parent_mark);
st.groupBy.add(pmm.columns.mark_id);
st.groupBy.add(st.columns.sheet_number);
st.groupBy.add(st.columns.sheet_id);
st.where.add(pmm.columns.delete_flag.isNull);
/** @type {QBJoin<db:/stsservoy/routings>} */
var rt1 = pmm.joins.add('db:/stsservoy/routings',JSRelation.RIGHT_OUTER_JOIN,'r');
rt1.on.add(pmm.columns.e_route_code_id.eq(rt1.columns.routing_id));
/** @type {QBJoin<db:/stsservoy/idfiles>} */
var id1 = st.joins.add('db:/stsservoy/idfiles');
st.where.add(pmm.columns.mark_id.eq(id1.columns.mark_id));
st.result.add(id1.columns.summed_quantity.sum,'total_marks');

var pmTable = databaseManager.getTable('stsservoy','marks');
var pmCols = pmTable.getColumnNames();
for (var index = 0;index < pmCols.length;index++){
st.result.add(pmm.columns[pmCols[index]]);
}


//pmDS = databaseManager.createDataSourceByQuery('pmSTxx',st,-1)
//pmFS = databaseManager.getFoundSet(pmDS)
//pmFS.loadAllRecords()
yields when criteria.marksa=["ZYX","MMM"]:

Record[DATA:Row(mem:pmSTxx)[DATA:_sv_rowid=61,total_marks=1.0,mark_id=BBBC6A7D-FE08-4619-AE27-534EDEE9B856,sheet_id=121F34A8-8DED-410F-A07D-9278D0541609,parent_mark=ZYX,mark=ZYX,material_class=null,fireproof_cubic_feet=null,e_route_code_id=F8008976-50A7-4BCB-A78F-13F5426971FF,
Record[DATA:Row(mem:pmSTxx)[DATA:_sv_rowid=62,total_marks=1.0,mark_id=B33B9417-A8F3-4E4E-BAFB-AE80BDF417F5,sheet_id=121F34A8-8DED-410F-A07D-9278D0541609,parent_mark=MMM,mark=MMM,material_class=null,fireproof_cubic_feet=null,e_route_code_id=F8008976-50A7-4BCB-A78F-13F5426971FF,

Obviously cut a bunch of junk out.

Thanks!
Joe


/** @type {QBSelect<db:/stsservoy/sheets>} */
var st = databaseManager.createSelect('db:/stsservoy/sheets');
st.where.add(st.columns.job_id.eq(jobId));

/** @type {QBSelect<db:/stsservoy/marks>} */
var pmm = st.joins.add('db:/stsservoy/marks');
pmm.on.add(st.columns.sheet_id.eq(pmm.columns.sheet_id));
st.result.add(pmm.columns.mark_id);

//pmDS = databaseManager.createDataSourceByQuery('stDSxxx',st,-1)
//pmFS = databaseManager.getFoundSet(pmDS)
//pmFS.loadAllRecords()

yields:
Record[DATA:Row(mem:stDSxxx)[DATA:_sv_rowid=1,mark_id=1290C2CC-EBC5-437B-B8CD-39FA2D89862E,
Record[DATA:Row(mem:stDSxxx)[DATA:_sv_rowid=2,mark_id=47BFB275-FFAE-4AB3-ABBB-D55C69C94211,
Record[DATA:Row(mem:stDSxxx)[DATA:_sv_rowid=3,mark_id=BBBC6A7D-FE08-4619-AE27-534EDEE9B856,
Record[DATA:Row(mem:stDSxxx)[DATA:_sv_rowid=4,mark_id=77336B2E-2F84-43F2-BC66-5448E56CECBF,
Record[DATA:Row(mem:stDSxxx)[DATA:_sv_rowid=5,mark_id=B33B9417-A8F3-4E4E-BAFB-AE80BDF417F5,
Record[DATA:Row(mem:stDSxxx)[DATA:_sv_rowid=6,mark_id=7B290EDB-2E23-43BC-AD00-586C03FCE670,
Record[DATA:Row(mem:stDSxxx)[DATA:_sv_rowid=7,mark_id=FB6D2F88-E835-4224-89DC-26742EB1FE90,

/** @type {QBSelect<db:/stsservoy/idfiles>} */
var id = databaseManager.createSelect('db:/stsservoy/idfiles');
id.result.add(id.columns.mark_id);
id.result.add(id.columns.summed_quantity.sum, 'total_marks');
id.groupBy.add(id.columns.mark_id);
id.where.add(id.columns.mark_id.isin(st));
/** @type {QBSelect<db;/stsservoy/idfiles>} */
var idds = databaseManager.createDataSourceByQuery('idDS',id,-1);

yields:
pRecord[DATA:Row(mem:stDSy)[DATA:_sv_rowid=1,mark_id=BBBC6A7D-FE08-4619-AE27-534EDEE9B856,total_marks=1.0,
Record[DATA:Row(mem:stDSy)[DATA:_sv_rowid=2,mark_id=B33B9417-A8F3-4E4E-BAFB-AE80BDF417F5,total_marks=1.0,
Record[DATA:Row(mem:stDSy)[DATA:_sv_rowid=3,mark_id=1290C2CC-EBC5-437B-B8CD-39FA2D89862E,total_marks=16.0,
Record[DATA:Row(mem:stDSy)[DATA:_sv_rowid=4,mark_id=FB6D2F88-E835-4224-89DC-26742EB1FE90,total_marks=1.0,
Record[DATA:Row(mem:stDSy)[DATA:_sv_rowid=5,mark_id=7B290EDB-2E23-43BC-AD00-586C03FCE670,total_marks=1.0,
Record[DATA:Row(mem:stDSy)[DATA:_sv_rowid=6,mark_id=47BFB275-FFAE-4AB3-ABBB-D55C69C94211,total_marks=80.0,
Record[DATA:Row(mem:stDSy)[DATA:_sv_rowid=7,mark_id=77336B2E-2F84-43F2-BC66-5448E56CECBF,total_marks=1.0,

/** @type {QBJoin} */
var idfls = st.joins.add(idds,JSRelation.INNER_JOIN,'table');
var columnT = idfls.getColumn('table','total_marks');
var columnP = idfls.getColumn('table','mark_id');
idfls.on.add(pmm.columns.marks_id.eq(columnP));
st.result.add(columnT);

yields:
"Error during evaluation:Wrapped java.lang.RuntimeException: com.servoy.j2db.persistence.RepositoryException: java.lang.IllegalStateException: Could not resolve column TEMP_13_A1003EFE-2138-49B2-9253-099DBC3A68A1<mem:idDS>#table.1073748223=?"
Last edited by joe26 on Fri Apr 01, 2016 10:36 pm, edited 1 time in total.
joe26
 
Posts: 77
Joined: Wed Jun 19, 2013 10:30 pm

Re: Join table with Query Builder on aggregate alias

Postby Bernd.N » Fri Apr 01, 2016 1:43 pm

I regret I can not comment on the Query Builder, but could it be an alternative to use a plain good old SQL statement together with databaseManager.getDataSetByQuery() ?
I mean an alternative to analyze this now for hours.
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 497
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: Join table with Query Builder on aggregate alias

Postby joe26 » Fri Apr 01, 2016 4:19 pm

Bernd, that is the current inception.

QB is *supposed* to make the SQL more portable.

--Joe.
joe26
 
Posts: 77
Joined: Wed Jun 19, 2013 10:30 pm

Re: Join table with Query Builder on aggregate alias

Postby Bernd.N » Fri Apr 01, 2016 4:52 pm

Sure, but it should be possible to create a SQL statement that all major DBs will accept, when you orient yourself close to the SQL standard.
However I do not know for how many different DBs you are creating your solution, so it might not be as easy as I initially thought.
Bernd Korthaus
LinkedIn
Servoy 7.4.9 SC postgreSQL 9.4.11 Windows 10 Pro
User avatar
Bernd.N
 
Posts: 497
Joined: Mon Oct 21, 2013 5:57 pm
Location: Langenhorn, North Friesland, Germany

Re: Join table with Query Builder on aggregate alias

Postby rgansevles » Fri Apr 01, 2016 5:30 pm

Hi Joe,

I think what goes wrong here is that you want to create a join between your in-mem datasource (idDS) and tables in the stsservoy server.
That is not possible.

Joins can only be made within the same server, between different inmem-datasources is also possible.

Rob
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1861
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL

Re: Join table with Query Builder on aggregate alias

Postby joe26 » Fri Apr 01, 2016 5:59 pm

Rob,

The in-memory datasource was used because I couldn't get the correct configuration for a groupBy clause, to get a summed count from mark_ids.

I couldn't suss out the QB statement to get a groupBy on a Join between marks and idfiles.

--Joe.
joe26
 
Posts: 77
Joined: Wed Jun 19, 2013 10:30 pm

Re: Join table with Query Builder on aggregate alias

Postby joe26 » Fri Apr 01, 2016 8:48 pm

Resolved. Will update as it unfolds...

Is there an iteration capability within Query Builder to add items?

i.e.

if (criteria.piecemarka && criteria.marksa.length == 2){
st.where.add(st.or
.add(pmm.columns.mark.like("%"+criteria.marksa.pop()+"%"))
)
}

Cheated with:

if (criteria.marksa && criteria.marksa.length == 1){
st.where.add(pmm.columns.mark.like("%"+criteria.marksa.pop()+"%"));
} else if (criteria.marksa && criteria.marksa.length > 1){
st.where.add(st.or
.add(pmm.columns.mark.like("%"+criteria.marksa.pop()+"%"))
.add(pmm.columns.mark.like("%"+criteria.marksa.pop()+"%"))
.add(pmm.columns.mark.like("%"+criteria.marksa.pop()+"%"))
.add(pmm.columns.mark.like("%"+criteria.marksa.pop()+"%"))
.add(pmm.columns.mark.like("%"+criteria.marksa.pop()+"%"))
.add(pmm.columns.mark.like("%"+criteria.marksa.pop()+"%"))
.add(pmm.columns.mark.like("%"+criteria.marksa.pop()+"%"))
.add(pmm.columns.mark.like("%"+criteria.marksa.pop()+"%"))
.add(pmm.columns.mark.like("%"+criteria.marksa.pop()+"%"))
.add(pmm.columns.mark.like("%"+criteria.marksa.pop()+"%"))
.add(pmm.columns.mark.like("%"+criteria.marksa.pop()+"%"))
.add(pmm.columns.mark.like("%"+criteria.marksa.pop()+"%"))
.add(pmm.columns.mark.like("%"+criteria.marksa.pop()+"%"))
.add(pmm.columns.mark.like("%"+criteria.marksa.pop()+"%"))
.add(pmm.columns.mark.like("%"+criteria.marksa.pop()+"%"))
.add(pmm.columns.mark.like("%"+criteria.marksa.pop()+"%"))
.add(pmm.columns.mark.like("%"+criteria.marksa.pop()+"%"))
)
}

Can I specify a where clauses for a joined table to remove those matches from the main query? in process

What is root to a query and the need to access parent?

Why do all manuals just show a base case? What is the Query Builder based upon? Why is the sky blue???
joe26
 
Posts: 77
Joined: Wed Jun 19, 2013 10:30 pm

Re: Join table with Query Builder on aggregate alias

Postby rgansevles » Sat Apr 02, 2016 7:56 pm

joe26 wrote:
Is there an iteration capability within Query Builder to add items?

Just like you did, you can add stuff to a query while looping over your data.
You can use query.where.add(query.columns.orderid.in([1,2,3,4,5,6])) to add a in-clause for a list, but for like-operators you need to loop yourself.

joe26 wrote:Can I specify a where clauses for a joined table to remove those matches from the main query? in process

query.joins.orders_to_orderids.on is an object that you can add conditions to, just like query.where.
These conditions will be part of the join condition.

joe26 wrote:What is root to a query and the need to access parent?

these are added to make fluent api easier.
The root member always points to the query, so you can continue extending the query in a fluent-api style.
query.where.add(..).root.results.add(...)

Hope this helps,

Rob
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1861
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL

Re: Resolved: Join table with Query Builder on aggregate ali

Postby joe26 » Mon Apr 04, 2016 9:24 pm

Thanks again,Rob.

Kind of had some of that worked out. Been bouncing on this particular issue since my post:

I'm attempting to get an indication that a particular status is true, even when there are multiple matches on for the same parent id. I'm not getting a distinct result from the subquery and it results in an incorrect result. The same status, PAINT, is done more than once on one record, but I just need to know that it has been done at least once, hence, distinct. It is giving me two hits on the same idfile_id regardless of the distinct entry in the subquery.

The plain SQL fires just fine, giving me a column of distinct values from the transactions table.

// statusa contains ["PAINT"]
// st is my main query for the databaseManager


/** @type {QBJoin<db:/stsservoy/idfiles>} */
var id1 = st.joins.add('db:/stsservoy/idfiles');
st.where.add(pmm.columns.mark_id.eq(id1.columns.mark_id));

if (criteria.statusa && criteria.statusa.length > 0){
st.groupBy.add(id1.columns.idfile_id);
/** @type {QBJoin<db:/stsservoy/transactions>} */
var tr1 = st.joins.add('db:/stsservoy/transactions');
tr1.root.result.distinct; // I would expect his query to have no more than one line with 'PAINT'. Query works otherwise.
tr1.root.result.add(tr1.columns.trans_status); // contains "PAINT", and some others
tr1.root.groupBy.add(tr1.columns.trans_status);

st.where.add(tr1.columns.trans_status.isin(criteria.statusa));
st.where.add(id1.columns.idfile_id.eq(tr1.columns.idfile_id));
}

total_marks in each join record should have total_marks = 1
this yields:

criteria [PAINT]
matching records count 6

the first record should be total_marks=1.0...

Record[DATA:Row(mem:pmSTxxxxxxxx)[DATA:_sv_rowid=13,route_code=STD,trans_status=PAINT,total_marks=2.0,mark_id=1290C2CC-EBC5-437B-B8CD-39FA2D89862E,sheet_id=AE49636B-9C15-496C-98D7-561C6C96AC90,parent_mark=,mark=,bom_source_file=null,cost_of_work_code=null,

Record[DATA:Row(mem:pmSTxxxxxxxx)[DATA:_sv_rowid=14,route_code=STD,trans_status=PAINT,total_marks=1.0,mark_id=1290C2CC-EBC5-437B-B8CD-39FA2D89862E,sheet_id=AE49636B-9C15-496C-98D7-561C6C96AC90,parent_mark=,mark=,bom_source_file=null,cost_of_work_code=null,

Record[DATA:Row(mem:pmSTxxxxxxxx)[DATA:_sv_rowid=15,route_code=STD,trans_status=PAINT,total_marks=1.0,mark_id=1290C2CC-EBC5-437B-B8CD-39FA2D89862E,sheet_id=AE49636B-9C15-496C-98D7-561C6C96AC90,parent_mark=,mark=,bom_source_file=null,cost_of_work_code=null,

Record[DATA:Row(mem:pmSTxxxxxxxx)[DATA:_sv_rowid=16,route_code=STD,trans_status=PAINT,total_marks=1.0,mark_id=1290C2CC-EBC5-437B-B8CD-39FA2D89862E,sheet_id=AE49636B-9C15-496C-98D7-561C6C96AC90,parent_mark=,mark=,bom_source_file=null,cost_of_work_code=null,

Record[DATA:Row(mem:pmSTxxxxxxxx)[DATA:_sv_rowid=17,route_code=STD,trans_status=PAINT,total_marks=1.0,mark_id=1290C2CC-EBC5-437B-B8CD-39FA2D89862E,sheet_id=AE49636B-9C15-496C-98D7-561C6C96AC90,parent_mark=,mark=,bom_source_file=null,cost_of_work_code=null,

Record[DATA:Row(mem:pmSTxxxxxxxx)[DATA:_sv_rowid=18,route_code=STD,trans_status=PAINT,total_marks=1.0,mark_id=1290C2CC-EBC5-437B-B8CD-39FA2D89862E,sheet_id=AE49636B-9C15-496C-98D7-561C6C96AC90,parent_mark=,mark=,bom_source_file=null,cost_of_work_code=null,
joe26
 
Posts: 77
Joined: Wed Jun 19, 2013 10:30 pm


Return to Programming with Servoy

Who is online

Users browsing this forum: Bing [Bot] and 3 guests