We recently upgrade to Servoy 2025.03 (4042). After upgrading we started noticing problems related to queries no longer working. attempting to filter with the following methods all returns 0 records:
var query = datasources.db.prosoftcms.pork_carcass_result.createSelect();
query.where.add(query.columns.purchase_price_line_id.eq(1796))
var resultFs = datasources.db.prosoftcms.pork_carcass_result.loadRecords(query)
resultFs.loadRecords(query);
var resultFs = datasources.db.prosoftcms.pork_carcass_result.getFoundSet();
if (resultFs.find()){
resultFs.purchase_price_line_id = 1796;
resultFs.search()
}
var query = datasources.db.prosoftcms.pork_carcass_result.createSelect();
query.where.add(query.columns.purchase_price_line_id.eq(1796))
var ds = databaseManager.getDataSetByQuery(query,-1)
I’ve checked for other lingering filters being applied and nothing else is being applied besides the query being built above. But running this sql script:
SELECT *
FROM pork_carcass_result pcr
WHERE pcr.purchase_price_line_id = 1796
returns the expected 10 records when running within pgAdmin. The queries built should be returning the same 10 records but they’re all returning no records. I’ve check that the queries are being applied/ran successfully and theres no errors and all return true when indicating they were ran. These same code chunks worked without problem in 2023.09 and only have broken when updating versions. Any help would be appreciated.
What is the SQL that you see when you execute those queries on the admin page? So what is really send to the database
The actual sql sent is:
select carcass_result_id from pork_carcass_result where purchase_price_line_id = ? and owner = ? and (purchase_price_line_id != ? or owner != ?) order by carcass_result_id asc
I’m aware of the owner comparison as that is our multi-tenancy column that is set upon login
Another thing I’ve noticed is if I close servoy completely, and then re-launch, run the application and run a query, it works as expected and finds all the records. But any attempt to re-call the queries (even rerunning the application for a fresh instance) will continue to return no records until servoy is restarted.
but what is then the difference between that it works and when it doesn’t?
(and then i mean in the query)
because you said there where no filters, but there are filters its the tenant filter, that very likely filters out the results..
If you would do that query that you show here by hand and fill in the right values then for sure there are no results.
i do find the query a bit weird because it seems that it duplicates something.
If I use that same query but fill in the values it still finds the records:
I’m not 100% sure what the (purchase_price_line_id != ? or owner != ?) portion being added is, I’m assuming it’s a null check, but even running that as a null check manually within pgadmin, still returns records. As for the query duplicating things, I’m not sure whats really going on with that. To ensure that theres nothing else playing around and setting additional filters, I’ve launched the application and only ran the query via the command console and again it follows the pattern that it will work if the first time after completely restarting servoy, but any time after that it won’t work.
but you now do “is null” that is not what is in the query that you showed me..
that really has “!= ?”
thats not the same as “is null”
that is really with a value…
I do ‘is not null’ because ‘!= null’ doesn’t work in postgres because null can’t be compared with = or !=. Meaning if (purchase_price_line_id != ? or owner != ?) is a null check, then doing (purchase_price_line_id is not null or owner is not null) is the logical equivalent
I just did an additional check comparing against calling the first time servoy opens vs any subsequent calls.
The query circled in red is the query that was ran the first time after I re-started servoy which works. The query circled in blue is the query that was ran after the first time and doesn’t work. I’ve done nothing between the calls, yet they’re both different.
that is because you add some table or foundset or tenant filters..
thats why the query will fail
Also that != ? is not a null check, if we do a null check the query generator should sent “is null” not “!= ?”
it seems to me that some how filters are added on that table that does kind of the opposite of what you want it almost seems to do:
select x from y where line_id = 1 and owner = 2 and (line != 1 or owner != 2)
which is a bit weird…
We have a function we built that iterates over all applied filters on a foundset and removes them so I tried calling that between queries to see if there is a hidden filter being applied somewhere and nothing happens there either. Somehow the later portion of the query is being added without any sort of additional filters being applied on our end…
Here’s all the calls I made with their outputs as well as the calls being made to the queries:
Heres the db calls for the above post:
For reference, the actually deletion logic for that function is commented out and isn’t being ran. Currently the function is only filtering the foundset and that’s it.
there must be something added by code on that foundset.
if you just do this:
var resultFs = datasources.db.prosoftcms.pork_carcass_result.getFoundSet();
if (resultFs.find()){
resultFs.purchase_price_line_id = 1796;
resultFs.search()
}
then it is not a foundset filter param, because those should only be on the foundset instance what you don’t do
so this could be a tenant filter or a database datasource filter (so for all foundset on that datasource)
something ist just added somewhere, as you said at the beginning its fine
but if you do something in the solution it suddenly doesn;t work
then that “do something” is the cause
can you check if you have not saved deleted records?
so what happens if you do a databaseManager.saveData()
and then do the query?
This fixed it. I remember that in one of the webinars it was mentioned that record deletions aren’t auto saved anymore so in memory the records were deleted. Thanks for your help.
the weird part is a bit that “owner” thing
what is that exactly ? is that your tenant?
Yeah owner is our tenant column
so the id that you query in the first comment: 1796 as an fk value.
that was already through maybe that foundset or another foundset deleted? (like deleteAll() on a foundset like that that is loaded with just that fk value?
And then later on you want to load that record again with that same fk value (why is that you deleted it already)
so you still should get 0 records even after saveData() then because you deleted it?
or did you make after the delete (but before saveData()) you added new records on it again? (and did you save only those specifically?)
1796 is an fk value to pork_carcass_result which stores calculated monetary values. This process that we were having problems with is used if those values need to be re-calculated due to changes elsewhere in the evaluation process and because of requirements in this process we need to remove any previously calculated records so they can be replaced with new records that are then used in the full new evaluation process. So the problem we were having is that the process wasn’t deleting the records (due to not saving the deletions because 2023.09 auto pushed deletions), so in the full PO evaluation process was calculating the results of newly created records as well as the historic records that should have been deleted, so the PO total value was incorrect due to essentially aggregating the values and not just the new values.
