Lazarus

Programming => Databases => Topic started by: Nicole on September 16, 2022, 06:40:33 pm

Title: IBX filterRecords and calcFields
Post by: Nicole on September 16, 2022, 06:40:33 pm
A IBQuery populates a DBStingGrid.
This Query is filtered by OnFilterRecords and does as it should.
The data-fields are fine.

Next step:

After the filter there are some more fields which shall be calculated.
They behave strange.
On debugging it seems to be, that the already filtered rows are passed on for calculation.

I checked the manual.
There I found, I can set "SQL filter" to true and this is just for "tables".
?!
I am clueless about the details, and what I tried did not work.

And the bad news:
Filtering by SQL-statment right from the DB source will not be possible. The IBquery is a join-request filling half a page. It is too complex.
What does this property IBQuery "SQL filtered" mean in detail?
There is a hint in the manual about "server side filters", which does not say too much to me.
Can I use it at all?
and: I use a local server.

Or in other words:
What can I do, that the IBquery CalcFields just work with my already "FilteredRecords"?



Title: Re: IBX filterRecords and calcFields
Post by: rvk on September 16, 2022, 07:22:32 pm
Quote from: Nicole on September 16, 2022, 06:40:33 pm
After the filter there are some more fields which shall be calculated.
They behave strange.
On debugging it seems to be, that the already filtered rows are passed on for calculation.
Why would this be strange?
I can image you may want to filter on calculated values. So they first need to be calculated.

Quote from: Nicole on September 16, 2022, 06:40:33 pm
And the bad news:
Filtering by SQL-statment right from the DB source will not be possible. The IBquery is a join-request filling half a page. It is too complex.
Maybe the filtered SQL can be simplified.
(You might be amazed by how many SQL's can be simplified, if not direct in SQL then by stored procedure.)

BTW it's always preferred to not filter on client side. Because otherwise the complete table would need to be transferred to client-side to be filtered. Filtering server-side is much more efficient.

Quote from: Nicole on September 16, 2022, 06:40:33 pm
What does this property IBQuery "SQL filtered" mean in detail?
There is a hint in the manual about "server side filters", which does not say too much to me.
Server side filters (SQLFiltered) are just for dynamically adding WHERE to the SQL clause.
You can also do that manually in code yourself.

Quote from: Nicole on September 16, 2022, 06:40:33 pm
What can I do, that the IBquery CalcFields just work with my already "FilteredRecords"?
You could check in CalcField if the record is filtered, and if so, just skip the calculation.

But why is this a problem?
And why is using WHERE or even SQLFiltered a problem?
Title: Re: IBX filterRecords and calcFields
Post by: Nicole on September 16, 2022, 08:07:43 pm
Why I want to filter first and calculate after it?
Because it is quicker to calculate a bundle of figures instead of all of them.

If you say this is solvable, may be for you.
I am really poor at group and union or so. There SHOULD be a way, but I will not find it.
I tried, but failed.

The problem is this:
There a generated sorted groups of "comm". From each of this group (mostly 5) I would need the first 3 each 'comm'. The trick is that "first 3" may destroy the complex sort, which is the most important thing in the result.

This is the statement

Code: MySQL  [Select][+][-]
  1. select fk_jdatum, o, h, l, c, volume, openinterest,
  2. fk_trade, fk_kontrakt, id_kurszeilen,
  3. tbkontrakte.id_kontrakt, tbkontrakte.name_ as name_, tbkontrakte.comm as comm, tbkontrakte.jahr_aus_name as jahr
  4. from tbkurszeilen JOIN tbkontrakte on fk_kontrakt = tbkontrakte.id_kontrakt where scrolled is null and fk_jdatum = '03.08.2022'
  5. order by comm, jahr asc, name_ asc, openinterest desc  

I attach you a result sheet where you can see what I mean.
Title: Re: IBX filterRecords and calcFields
Post by: rvk on September 16, 2022, 10:59:06 pm
Quote from: Nicole on September 16, 2022, 08:07:43 pm
Why I want to filter first and calculate after it?
Because it is quicker to calculate a bundle of figures instead of all of them.
...
There a generated sorted groups of "comm".
What is the calculated field?
If COMM is the calculated field, it is absolutely needed that it is calculated for every record when filtering (because the filtering depends on it).

If the calculated field is on another field, how much time is the calculation really?
(fields are already in memory so calculation shouldn't take too long.)

But it might be preferable, as is the same with filtering, to do calculation on server side. It depends on the 'calculation'.

The query you want (limiting records to 3 per COMM) should be doable in Firebird.
But it is tricky. And if it can't be achieved with a SUB SELECT (select from (select)), it can always be done with an EXECUTE BLOCK.

In firebird 3.0+ (and MySql 8+) it's much easier because there is a ROW_NUMBER () OVER <window-specification>.
In that case you can just add the ROW_NUMBER per COMM change and limit it by doing a "row_number < 4".
An fiddle example in Firebird 3.0 is here: https://dbfiddle.uk/UZ4mNLbh (for MySQL it's here: https://dbfiddle.uk/RnvwyIZN)
The double sub-select was needed because in the select itself row_number isn't know yet.
You can play (fiddle) with it on that page.

(the Firebird version even has the possibility to do the ordering in the "over (partition)" part.)

In Firebird 2.5- it a lot trickier and needs to be done by SUB SELECTs or EXECUTE BLOCKs.
(although I advised against it earlier, for this feature it might be worth to upgrade, at least to test it out  :D)
Title: Re: IBX filterRecords and calcFields
Post by: Nicole on September 17, 2022, 01:05:14 pm
Thank you for the answer.
"Calculated" does not work yet, so you do cannot see a calculated field on the screenshot.

If the calculation field is performance consuming? Oh, yes. It is a complex date-situation, which calculates events for certain week days and working days etc. All the stuff, they kept from Babylon and which does not work decimal. I can feel the difference on scrolling because it takes nearly a second.

"comm" is just a field on my table, not calculated.
If tell you what comm means, you will understand at sudden:
Comm stands for commodity. So if you want to check, if your next heating period will be affordable anymore, you check for the rows where comm = "HO" (=heating oil). If you think "within a year can happen much", you will filter all rows of the year 2024 and spring 2023. This is the reason, why I need just three of "comm", but of every "comm".

Well, about Firebird 3. Yes, I will take it. But not now. You cannot upgrade all at the same time. Then you will never get out of the mess.

My Delphi had a solution with OnCalcFields and Filtering which works fine. Lazarus shall do this as well sooner or later.

To work on:
What does this property SQL-filter mean?
And is there a way to stop calculating and filtering for scrolling? Both just shall be done on the click event of one button. This button makes the software reading the (changed) filter-criteria new.

Or is there a way to DELETE rows of myquery in the filter event?
Title: Re: IBX filterRecords and calcFields
Post by: rvk on September 17, 2022, 07:54:16 pm
Quote from: Nicole on September 17, 2022, 01:05:14 pm
Or is there a way to DELETE rows of myquery in the filter event?
How about putting a line like this at the top of OnCalcField?

Code: Pascal  [Select][+][-]
  1. begin
  2.   if check_if_record_filtered then exit;

It will limit the actual calulation to the records not filtered out.

Quote from: Nicole on September 17, 2022, 01:05:14 pm
Or is there a way to DELETE rows of myquery in the filter event?
No. The only way is to not send the records to the client in the first place.
Title: Re: IBX filterRecords and calcFields
Post by: rvk on September 19, 2022, 11:54:25 am
BTW, this would be the server-side filtering only returning the first 3 records per COMM change.
https://dbfiddle.uk/SA5-bQCf

(I'm not sure if you can use EXECUTE BLOCK directly with the TIBQuery component. It could be that you'll need to set ParamCheck to false.)


Code: SQL  [Select][+][-]
  1. EXECUTE BLOCK RETURNS (FIELD1 INT, FIELD2 VARCHAR(20), FIELD3 VARCHAR(20), FIELD4 VARCHAR(20), NR INT)
  2. AS
  3. DECLARE VARIABLE OLD VARCHAR(20);
  4. BEGIN
  5.   NR = 0;
  6.   OLD = '';
  7.   FOR SELECT FIELD1, FIELD2, FIELD3, FIELD4
  8.       FROM DATA1
  9.       INTO :FIELD1, :FIELD2, :FIELD3, :FIELD4 DO
  10.   BEGIN
  11.     IF (OLD <> FIELD2) THEN NR = 0;
  12.     NR = NR + 1;
  13.     OLD = FIELD2;
  14.     IF (NR < 4) THEN SUSPEND;
  15.   END
  16. END
(You would need to change the FOR SELECT INTO according to your own SQL.)
Title: Re: IBX filterRecords and calcFields
Post by: tonyw on September 20, 2022, 05:55:36 pm
Quote from: Nicole on September 16, 2022, 06:40:33 pm
A IBQuery populates a DBStingGrid.
This Query is filtered by OnFilterRecords and does as it should.
The data-fields are fine.
The use of OnFillterRecords is really a bit of a hangover from Paradox and other non-SQL databases. The assumed model is that each dataset (e.g. TIBQuery) is a table and you want to select only a subset of the table's rows. You have to do this clientside with Paradox as the underlying database doesn't really give you much help. OnFilterRecords should still work with a modern database, such as Firebird, but it is far better to add a conditional clause (e.g. WHERE...) to your SQL query and get the database to do the work for you (this is server side filtering). This is even more important when using a remote server as using conditional SQL reduces the data over the network.

When using a TIBTable, IBX generates the query for you and, in this case, you can use the SQLFilterParams property to add one or more conditional statements to the generated query so that you can get the benefits of server side filters even with a basic TIBTable dataset.
Quote from: Nicole on September 16, 2022, 06:40:33 pm

Next step:

After the filter there are some more fields which shall be calculated.
They behave strange.
On debugging it seems to be, that the already filtered rows are passed on for calculation.
Not quite true. The sequence is that first the row is fetched from the database, OnCalcfields is then called to calculate the field values and then OnFilterRecord to filter out those you don't want. This allows your filter to work on calculated and well as data fields.
Quote from: Nicole on September 16, 2022, 06:40:33 pm

I checked the manual.
There I found, I can set "SQL filter" to true and this is just for "tables".
?!
I am clueless about the details, and what I tried did not work.

And the bad news:
Filtering by SQL-statment right from the DB source will not be possible. The IBquery is a join-request filling half a page. It is too complex.
What does this property IBQuery "SQL filtered" mean in detail?
There is a hint in the manual about "server side filters", which does not say too much to me.
Can I use it at all?
and: I use a local server.
See section 6.3.2.2 in the user guide.
The SQLFiltered Property turns on server side filtering. All this means is that when a dataset is opened, the Select query is modified by adding the text in SQLFilterParams property to the SQL query's WHERE clause. It is your responsibility to make sure that the text is a valid SQL conditional (e.g. Salary < 10000).

This is useful in:
a) TIBTable where this is your only means of adding conditional clauses to the select query.
b) Adding dynamic modifications to the select query e.g. by adding a drop down list to your form selecting different filters and each time the list changes, updating the SQLFilterParams property to match the selected filter and re-opening the dataset.
Quote from: Nicole on September 16, 2022, 06:40:33 pm

Or in other words:
What can I do, that the IBquery CalcFields just work with my already "FilteredRecords"?
That's one thing you can't do. The order is first calculate the field values and then apply the client side filter and that is hard wired. Use SQL filters (server side) if you want to calculate fields on a filtered dataset. Even better, also add calculated fields to your SQL query

e.g.

Select FullName, Salary, Salary/12 as MonthlySalary from EMPLOYEE;
TinyPortal © 2005-2018