Resolved: Join table with Query Builder on aggregate alias

[joe26]

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=?"

[Bernd.N]

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.

[joe26]

Bernd, that is the current inception.

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

--Joe.

[Bernd.N]

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.

[rgansevles]

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

[joe26]

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]

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???

[rgansevles]

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.

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.

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

[joe26]

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,