Sorting tsqlquery with indexes working example(Yes it works now)
lazarus / 16-Databases

----------------------------------------------------------------------
snorkel · 2013-03-06 00:22

I am using this example:
http://wiki.lazarus.freepascal.org/How_to_write_in-memory_database_app
lications_in_Lazarus/FPC#Sorting_DBGrid_on_TitleClick_event_for_TBufDa
taSeta

But it seems to have issues with tsqlquery, it's not releasing the
indexes, and even if I set the max to 100 it will eventually error
out.

I can't seem to find a decent example of how to sort the tsqlquery
with a dbgrid.

----------------------------------------------------------------------
snorkel · 2013-03-06 20:30

In case anyone cares this works and displays the sort arrow from the
defined title image list.

(openquery in the code is a tsqlquery object)
Code: [Select]procedure TSQLForm.ResultsGridTitleClick(Column:
TColumn);
var
  ASC_IndexName,DESC_IndexName:string;
begin
     ASC_IndexName:='ASC_'+Column.FieldName;
     DESC_IndexName:='DESC_'+Column.FieldName;
     //indexes cant sort binary types such as ftmemo, should add blob
types to this as well
     if(Column.Field.DataType = ftmemo) or (Column.Field.DataType =
ftwidememo)then Exit;
     //check if a Ascending index already exists for this column, if
not create one
     if OpenQuery.IndexDefs.IndexOf(ASC_IndexName) = -1 then
        openquery.AddIndex(ASC_IndexName,column.FieldName,[]);
     //check if a Descending index already exists for this column, if
not create one
     if OpenQuery.IndexDefs.IndexOf(DESC_IndexName) = -1 then
       
openquery.AddIndex(DESC_IndexName,column.FieldName,[ixDescending]);
     //ensure index defs are up to date
     OpenQuery.IndexDefs.Updated:=false;  {<<<----It won't work
without this line}
     OpenQuery.IndexDefs.Update;
     //use the column tag to toggle ASC/DESC
     column.tag :=  not column.tag;
     if boolean(column.tag) then
      begin
           Column.Title.ImageIndex:=12;
           openquery.IndexName:=ASC_IndexName;
      end else
          begin
               Column.Title.ImageIndex:=13;
               OpenQuery.IndexName:=DESC_IndexName;
          end;
     if (flastcolumn <> nil) and (flastcolumn <> Column) then
        flastcolumn.Title.ImageIndex:=-1;
     flastcolumn:=column;
end;

The one in the wiki does not work properly and does not need all the
RTTI stuff anymore:
http://wiki.lazarus.freepascal.org/How_to_write_in-memory_database_app
lications_in_Lazarus/FPC#Sorting_DBGrid_on_TitleClick_event_for_TBufDa
taSeta

----------------------------------------------------------------------
goodname · 2013-03-06 21:58

Thanks for posting the response. Was looking in to doing this a little
while back but didn't go into the depth that you did. It would be good
if you could update the wiki with this information as well.

----------------------------------------------------------------------
snorkel · 2013-03-06 22:02

hi,
I was playing around with this but there appears to be a issue with
tsqlquery indexdefs in 2.6.2

even though I have the max indexes count set to 100 I get the max
number of indexes used error.
So put a loop to check what's in indexdefs and it only adds the
indexes for the first column I click on, then after that the indexes
just keep building up because the other columns are not in indexdefs
even after having been added.

Can anyone verify?

----------------------------------------------------------------------
Lacak2 · 2013-03-07 12:50

Do you set MaxIndexesCount before you open bufdataset ?
(I assume that yes)

Then you use AddIndex() and you get error :
"The maximum amount of indexes is reached" ?

Can you poste here small exampe which will show it ?

----------------------------------------------------------------------
snorkel · 2013-03-07 15:59

Yes, I set max indexes to 100 and then use the code I posted.

It works great if I only click on one column title to sort, as soon as
I add another index for another column it can't find them in the
indexdefs list anymore and so it keeps adding new indexes.

If I loop through index defs it  only shows 4 indexes, one called
"DEFAULT_ORDER", one with no name and the first two that where added
when I clicked on the first column I wanted to sort.

I am using TSQLQuery with SQLite by the way and the rest is in my
signature.

So I thought I would try indexdefs.clear, but that doesn't work
either, it does not clear out the indexes.

I will do a sample SQLite example in win32 to demonstrate this
behavior.

----------------------------------------------------------------------
snorkel · 2013-03-07 18:32

Well, I thought I would be clever and simply reuse the two initial
indexes as they are the only ones that are in indexdefs, but that does
not work either even though I change the fieldnames to the current
column, it seems to always be holding on the first ones added.
It seems there is a pointer issue with indexdefs, the list is not
holding the correct pointer to the actual index:

Code: [Select]procedure TSQLForm.ResultsGridTitleClick(Column:
TColumn);
var
  ASC_IndexName,DESC_IndexName:string;
  x:integer;
  aindex:TIndexDef;
begin
     ASC_IndexName:='ASC_';
     DESC_IndexName:='DESC_';
     //indexes cant sort binary types such as ftmemo, should add blob
types to this as well
     if(Column.Field.DataType = ftmemo) or (Column.Field.DataType =
ftwidememo)then Exit;
     //check if a Ascending index already exists for this column, if
not create one
     x:=OpenQuery.IndexDefs.IndexOf(ASC_IndexName);
     if x = -1 then
        openquery.AddIndex(ASC_IndexName,column.FieldName,[]) else
        begin
             aindex:= openquery.IndexDefs.Items[x];
             aindex.Fields:=column.FieldName;
        end;
     //check if a Descending index already exists for this column, if
not create one
     x:= OpenQuery.IndexDefs.IndexOf(DESC_IndexName);
     if x = -1 then
       
openquery.AddIndex(DESC_IndexName,column.FieldName,[ixDescending])
else
           begin
             aindex:= openquery.IndexDefs.Items[x];
             aindex.Fields:=column.FieldName;
           end;

     //ensure index defs are up to date
     OpenQuery.IndexDefs.Updated:=false;  {<<<<---Won't work without
this line}
     openquery.IndexDefs.Update;
     //use the column tag to toggle ASC/DESC
     column.tag :=  not column.tag;
     if boolean(column.tag) then
      begin
           Column.Title.ImageIndex:=12;
           openquery.IndexName:=ASC_IndexName;
      end else
          begin
               Column.Title.ImageIndex:=13;
               OpenQuery.IndexName:=DESC_IndexName;
          end;
     if (flastcolumn <> nil) and (flastcolumn <> Column) then
        flastcolumn.Title.ImageIndex:=-1;
     flastcolumn:=column;
end;

----------------------------------------------------------------------
Lacak2 · 2013-03-08 07:02

Try this:
Code: [Select]openquery.IndexDefs.Updated:=false;
openquery.IndexDefs.Update;
(Update does nothing if Updated is true ... )

----------------------------------------------------------------------
snorkel · 2013-03-08 15:32

Quote from: Lacak2 on March 08, 2013, 08:02:31 amTry this:
Code: [Select]openquery.IndexDefs.Updated:=false;
openquery.IndexDefs.Update;
(Update does nothing if Updated is true ... )

That did the trick.  Sorting works now with the original version I
posted.

Thanks Lacak2, I just assumed that update would update it regardless.

I will update the wiki with my example.

Thanks again for helping me with this, much appreciated.

I updated the wiki for the original example by adding this line to it:

  IndexDefs.Updated:=false; {<<<<---This line is critical as
IndexDefs.Update will do nothing on the next sort if it's already
true}

http://wiki.lazarus.freepascal.org/How_to_write_in-memory_database_app
lications_in_Lazarus/FPC#Sorting_DBGrid_on_TitleClick_event_for_TBufDa
taSet

----------------------------------------------------------------------
snorkel · 2013-03-08 16:36

I added a new section to the DBgrid reference page in the wiki, check
it out:

http://wiki.lazarus.freepascal.org/Grids_Reference_Page#Sorting_column
s_or_rows_in_DBGrid_with_sort_arrows_in_column_header

----------------------------------------------------------------------
tatamata · 2016-02-23 22:02

Quote from: snorkel on March 08, 2013, 04:32:35 pmQuote from: Lacak2
on March 08, 2013, 08:02:31 amTry this:
Code: [Select]openquery.IndexDefs.Updated:=false;
openquery.IndexDefs.Update;
(Update does nothing if Updated is true ... )

That did the trick.  Sorting works now with the original version I
posted.

Thanks Lacak2, I just assumed that update would update it regardless.

I will update the wiki with my example.

Thanks again for helping me with this, much appreciated.

I updated the wiki for the original example by adding this line to it:

  IndexDefs.Updated:=false; {<<<<---This line is critical as
IndexDefs.Update will do nothing on the next sort if it's already
true}

http://wiki.lazarus.freepascal.org/How_to_write_in-memory_database_app
lications_in_Lazarus/FPC#Sorting_DBGrid_on_TitleClick_event_for_TBufDa
taSet
Hi, Snorkel, I tried to use this enhanced wiki code with showing up
and down arrow (Column.Title.ImageIndex:=ImageArrowDown and
Column.Title.ImageIndex:=ImageArrowUp respectively) on sorting a
column, but no arrow is shown.
Can you look at this, why arrow sign is not shown in column title?
Thanks.
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i function
TDataModule1.SortBufDataSet(DataSet: TSQLQuery; var Column: TColumn;
const FieldName: String): Boolean;const  ImageArrowUp=0; //should
match image in imagelist  ImageArrowDown=1; //should match image in
imagelistvar  i: Integer;  IndexDefs: TIndexDefs;  IndexName: String; 
IndexOptions: TIndexOptions;  Field: TField;begin  Result := False; 
Field := DataSet.Fields.FindField(FieldName);  //If invalid field
name, exit.  if Field = nil then Exit;  //if invalid field type,
exit.  if {(Field is TObjectField) or} (Field is TBlobField) or   
{(Field is TAggregateField) or} (Field is TVariantField)     or (Field
is TBinaryField) then Exit;  //Get IndexDefs and IndexName using RTTI 
if IsPublishedProp(DataSet, 'IndexDefs') then    IndexDefs :=
GetObjectProp(DataSet, 'IndexDefs') as TIndexDefs  else    Exit;  if
IsPublishedProp(DataSet, 'IndexName') then    IndexName :=
GetStrProp(DataSet, 'IndexName')  else    Exit;  //Ensure IndexDefs is
up-to-date  IndexDefs.Updated:=false; {<<<<---This line is critical as
IndexDefs.Update will do nothing on the next sort if it's already
true}  IndexDefs.Update;  //If an ascending index is already in use, 
//switch to a descending index  if IndexName = FieldName + '__IdxA' 
then    begin      IndexName := FieldName + '__IdxD';     
IndexOptions := [ixDescending];     
Column.Title.ImageIndex:=ImageArrowDown;    end  else    begin     
IndexName := FieldName + '__IdxA';      IndexOptions := [];     
Column.Title.ImageIndex:=ImageArrowUp;    end;  //Look for existing
index  for i := 0 to Pred(IndexDefs.Count) do  begin    if
IndexDefs[i].Name = IndexName then      begin        Result := True; 
     Break      end;  //if  end; // for  //If existing index not
found, create one  if not Result then      begin        if
IndexName=FieldName + '__IdxD' then         
DataSet.AddIndex(IndexName, FieldName, IndexOptions, FieldName)      
else          DataSet.AddIndex(IndexName, FieldName, IndexOptions);  
    Result := True;      end; // if not  //Set the index 
SetStrProp(DataSet, 'IndexName', IndexName);end;

----------------------------------------------------------------------
Ericktux · 2016-04-09 01:52

Thanks   this is my code:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var  Form2:
TForm2;  FLastColumn: TColumn; //store last grid column we sorted on
 ..... procedure TForm2.DBGrid1TitleClick(Column: TColumn);const 
ImageArrowUp=0; //should match image in imagelist  ImageArrowDown=1;
//should match image in imagelistvar  ASC_IndexName,
DESC_IndexName:string;  procedure UpdateIndexes;  begin    // Ensure
index defs are up to date    SQLQuery1.IndexDefs.Updated:=false;
{<<<--This line is critical. IndexDefs.Update will not    update if
already true, which will happen on the first column sorted.}   
SQLQuery1.IndexDefs.Update;  end;begin 
ASC_IndexName:='ASC_'+Column.FieldName; 
DESC_IndexName:='DESC_'+Column.FieldName;  // indexes can't sort
binary types such as ftMemo, ftBLOB  if (Column.Field.DataType in
[ftBLOB,ftMemo,ftWideMemo]) then    exit;  // check if an ascending
index already exists for this column.  // if not, create one  if
SQLQuery1.IndexDefs.IndexOf(ASC_IndexName) = -1 then  begin   
SQLQuery1.AddIndex(ASC_IndexName,column.FieldName,[]);   
UpdateIndexes; //ensure index defs are up to date  end;  // Check if a
descending index already exists for this column  // if not, create
one  if SQLQuery1.IndexDefs.IndexOf(DESC_IndexName) = -1 then  begin 
 SQLQuery1.AddIndex(DESC_IndexName,column.FieldName,[ixDescending]); 
 UpdateIndexes; //ensure index defs are up to date  end;   // Use the
column tag to toggle ASC/DESC  column.tag := not column.tag;  if
boolean(column.tag) then  begin   
Column.Title.ImageIndex:=ImageArrowUp;   
SQLQuery1.IndexName:=ASC_IndexName;  end  else  begin   
Column.Title.ImageIndex:=ImageArrowDown;   
SQLQuery1.IndexName:=DESC_IndexName;  end;  // Remove the sort arrow
from the previous column we sorted  if (FLastColumn <> nil) and
(FlastColumn <> Column) then    FLastColumn.Title.ImageIndex:=-1; 
FLastColumn:=column;end;               

And in the Event Create:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm2.FormCreate(Sender: TObject);begin 
SQLQuery1.MaxIndexesCount:=100; // para poder ordenar las columnas   
//  ......   your codeend;
also we need a imagelist1 and put two 16x16 images, then link inside
properties DBgrid "Titleimagelist=imagelist"

Greetings from Peru

----------------------------------------------------------------------
tatamata · 2016-04-09 05:35

Pascalito, can you attach these two images of arrows up and down?
Thnx.

----------------------------------------------------------------------
Ericktux · 2016-04-12 14:31

of course my friend 
if you want more icons you can get them in "IconFinder"

----------------------------------------------------------------------
Vodnik · 2020-11-16 19:46

I have used just this code in my application, compiled with Lazarus
2.0.4 for Windows.
It was nice that when column callid is once sorted, newly loaded data
becomes sorted, too. 
It work fine for a couple of years, until I have upgraded Lazarus to
2.0.10.
Now it works the very first time only, attempt to load new data causes
exception
DBQuery1: Index 'ASC_callid' not found.
I didn't investigate how the indexes work. Maybe there is quite a
simple idea how to bring this code back to life?

----------------------------------------------------------------------
antoniocangiano76 · 2021-10-14 19:18

QuoteI have used just this code in my application, compiled with
Lazarus 2.0.4 for Windows.
It was nice that when column callid is once sorted, newly loaded data
becomes sorted, too.
It work fine for a couple of years, until I have upgraded Lazarus to
2.0.10.
Now it works the very first time only, attempt to load new data causes
exception
DBQuery1: Index 'ASC_callid' not found.
I didn't investigate how the indexes work. Maybe there is quite a
simple idea how to bring this code back to life?
Lazarus 2.0.12 FPC 3.2.0 Win10 21H1 64bit
Same problem...Here is my steps
1) I set index on first column (OK) 
2) Then set a filter on query q1 with a Tedit with this code :

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
Tf_param.te_filtroChange(Sender: TObject);begin   if
Trim(te_filtro.Text) <> '' then  begin    if not q1.Filtered then
q1.Filtered := True;    q1.Refresh;  end else    q1.Filtered :=
False;end;     
and ...

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
Tf_param.q1FilterRecord(DataSet: TDataSet; var Accept: Boolean);var 
i,k: integer;begin  for i := 0 to dbg1.Columns.Count -1 do begin    if
dbg1.Columns[i].Visible then begin;       Accept := Pos( iif(
togglebox1.checked,UpperCase(te_filtro.Text),te_filtro.Text ),       
             iif(
togglebox1.checked,UpperCase(dbg1.Columns[i].Field.AsString),        
            dbg1.Columns[i].Field.AsString) ) > 0;       if Accept
then exit;    end;  end;  //   if Accept = False then Accept :=
Pos(UpperCase(te_filtro.Text),
UpperCase(DataSet.FieldByName('DESC2').AsString)) > 0;end; 
3) When I insert first char in Tedit, I got "q1 _ Index 'ASC_DESC' not
found
4) Error occours in Tedit onchange at Code: Pascal
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i q1.Refresh;

Any ideas?
Thanks

----------------------------------------------------------------------
wp · 2021-10-14 21:28

Your description does not provide enough information. What kind of
query is q1? The title of the thread mentions sqlquery, but I cannot
believe this because then you'd filter the query result by the sql
clause, not by the OnFilterRecord event.

Ideally you should provide a simple small compilable project which
shows the error. Remove everything from your project which is not
related to this issue and check that the bug still exists, then upload
the .pas, .lfm, .lpi, .lpr and database file, all packed into a shared
zip.