SQL Filtering a DBGrid in Delphi

Question

I want to have a DBGrid showing products filtered based on 4 dbLookupCombobox'es
If I select a Manufacturer, the Category1-3 will update/filter and only show existing categories for products for the selected manufacturer.
If I then select a Category1, then Category2-3 will update/filter and only show existing categories for products for the selected manufacturer and Category1.
Same with Category2 as category1 but includes choice of Category1.

Also I want to be able to only choose from Category1+2 with no Manufacturer selected.

Whatever I choose from these 4 Combos, I want the DBGrid to update and show the filtered products.

When done filtering, I want a Reset filter Button to start over.

Scenario:

1x FDConnection, Firebird.
5x FDQuery and Datasources
1x DBGrid
4x dbLookupComboBox
1x Button

The Queries:

FDQuery1: FDQ_Manufacturers:
  select distinct MANUFACTURERNAME from Products where
  upper (CATEGORYTEXT1) like upper(:CATEGORYTEXT1)
  and upper(CATEGORYTEXT2) like upper(:CATEGORYTEXT2)
  and upper(CATEGORYTEXT3) like upper(:CATEGORYTEXT3)

FDQuery2: FDQ_Category1
  select distinct CATEGORYTEXT1 from Products where
  upper(MANUFACTURERNAME) like upper(:MANUFACTURERNAME)

FDQuery3: FDQ_Category2
  select distinct CATEGORYTEXT2 from Products where 
  upper(MANUFACTURERNAME) like upper(:MANUFACTURERNAME)
  and upper(CATEGORYTEXT1) like upper(:CATEGORYTEXT1)

FDQuery4: FDQ_Category3
  select distinct CATEGORYTEXT3 from Products where
  upper(MANUFACTURERNAME) like upper(:MANUFACTURERNAME)
  and upper(CATEGORYTEXT1) like upper(:CATEGORYTEXT1)
  and upper(CATEGORYTEXT2) like upper(:CATEGORYTEXT2)

FDQuery5: FDQ_Products
  select first 100 * from Products where  
  and upper(MANUFACTURERNAME) like upper(:MANUFACTURERNAME) 
  and upper(CATEGORYTEXT1) like upper(:CATEGORYTEXT1) 
  and upper(CATEGORYTEXT2) like upper(:CATEGORYTEXT2) 
  and upper(CATEGORYTEXT3) like upper(:CATEGORYTEXT3) 
  and upper(DESCRIPTION) like upper(:DESCRIPTION)
  ORDER BY PRICE

The dbLookupComboBox'es:

dbLookupComboBox1: LCB_Manufacturers: OnSelect:  FilterProducts('MANUFACTURERNAME', LCB_Manufacturers.Text);
dbLookupComboBox2: LCB_Category1:     OnSelect:  FilterProducts('CATEGORYTEXT1', LCB_Category1.Text);
dbLookupComboBox3: LCB_Category2:     Onselect:  FilterProducts('CATEGORYTEXT2', LCB_Category2.Text);
dbLookupComboBox4: LCB_Category3:     OnSelect:  FilterProducts('CATEGORYTEXT3', LCB_Category3.Text);

ListSource is the DataSources for each FDQuery.
ListField and KeyField is respectively the MANUFACTURERNAME, CATEGORYTEXT1, CATEGORYTEXT2, CATEGORYTEXT3

I initialize the all Params with '%%'
I run FDQuery1-4 and now have all Manufacturers and categories filled in the Combos.
I run FDQuery5 and have a unfiltered result of products in the DBGrid.
Now we are ready to do the SQL Filtering.

Questions:
Is the dbLookupComboBoxes the right choice of component for this task?

Is it the right way to handle this kind of filtering?

I can't figure out the FilterProducts procedure. I can show my code, but I it is really no good, living its own life..

Sorry for bringing such long post. I have spend alot of time in this, and really need an advice now.

Marc Guillot · Accepted Answer · 2017-04-27 14:57:47Z

I personally would filter locally, on Delphi, not on the database. It would be lighter on the server, and probably faster (unless you really have a lot of products to filter).

You already start loading a FDQuery5 unfiltered, with all possible products. Now when the user choose an option to filter you can directly do it locally instead of running a new SQL statement.

Also, I would change your FilterProducts to have a different parameter for every choice, so the user can choose for more than once choice.

It would be something like :

procedure MyForm.FilterProducts(ManufacturerName: string; CategoryText1: string; CategoryText2: string; CategoryText3: string);
var Filter: string;
begin
  Filter := 'MANUFACTURERNAME like ' + QuotedStr(ManufacturerName);
  Filter := Filter + ' and CATEGORYTEXT1 like ' + QuotedStr(CategoryText1);
  Filter := Filter + ' and CATEGORYTEXT2 like ' + QuotedStr(CategoryText2);
  Filter := Filter + ' and CATEGORYTEXT3 like ' + QuotedStr(CategoryText3);

  FDQuery5.FilterOptions := [foCaseInsensitive];
  FDQuery5.Filter := Filter;
  FDQuery5.Filtered := True;
end;

Now all your comboboxes can call the same event handler :

dbLookupComboBox1: LCB_Manufacturers: OnSelect:  FilterProducts(LCB_Manufacturers.Text, LCB_Category1.Text, LCB_Category2.Text, LCB_Category3.Text);
dbLookupComboBox2: LCB_Category1:     OnSelect:  FilterProducts(LCB_Manufacturers.Text, LCB_Category1.Text, LCB_Category2.Text, LCB_Category3.Text);
dbLookupComboBox3: LCB_Category2:     Onselect:  FilterProducts(LCB_Manufacturers.Text, LCB_Category1.Text, LCB_Category2.Text, LCB_Category3.Text);
dbLookupComboBox4: LCB_Category3:     OnSelect:  FilterProducts(LCB_Manufacturers.Text, LCB_Category1.Text, LCB_Category2.Text, LCB_Category3.Text);

But if you still prefer to run a new SQL (due to not having all products on FDQuery5, just the first 100), then having passed all the choices to FilterProduts, retrieving your desired products on FDQuery5 should be really simple :

procedure MyForm.FilterProducts(ManufacturerName: string; CategoryText1: string; CategoryText2: string; CategoryText3: string);
begin
  FDQuery5.Close;
  FDQuery5.ParamsByName('MANUFACTURERNAME').Value := ManufacturerName;
  FDQuery5.ParamsByName('CATEGORYTEXT1').Value := CategoryText1;
  FDQuery5.ParamsByName('CATEGORYTEXT2').Value := CategoryText2;
  FDQuery5.ParamsByName('CATEGORYTEXT3').Value := CategoryText3;
  FDQuery5.Open;
end;

I have about 40.000 records, so loading them all into a dataset is not an option. And so is a local filter. The FilterProducts works perfect. Thank you. But if you read the first of my post, I want the 4 Comboboxes to update with valid choices based on choices of the other Comboboxes. - I had it running, but it kills my brain, because I cannot figure out the right codes for this. Eg. I Choose a manufacturer, and a Category 1+2. Then I deside to change Category 1. Boom, Manufacurer combo is now empty. And I have to re-initialize. Got any tricks for this?

Collectives™ on Stack Overflow

SQL Filtering a DBGrid in Delphi

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related