Subquery in where clause

Data Abstract
1

I have situations where I need to use a subquery inside the where clause of a query.

For example:

SELECT * FROM MyTable WHERE SeriesId IN (SELECT SeriesId FROM MyTable WHERE Id = :ID)

In this example, multiple records can be part of a series and I want to retrieve all records which are part of the same series as the specified record.

I’m struggling to work out how to migrate this kind of query into DA as dynamic where doesn’t support subqueries. Do you have any suggestions?

2

this feature isn’t supported in DynamicWhere.

You can implement such functionality via custom method or use this workaround.

I’ve used Northwind.Customers table

  • add subquery parameter to your table in DASM
  • server-side:
procedure TDataService.DataAbstractServiceValidateDatasetAccess(Sender: TObject;
  const aConnection: IDAConnection; const aDatasetName: string;
  const aParamNames: array of string; const aParamValues: array of Variant;
  aSchema: TDASchema; var Allowed: Boolean);
var
  i: integer;
begin
  if (aDatasetName = 'Customers') then begin // we need to use 'subquery' param only for specific table
    for I := 0 to Length(aParamNames)-1 do
      if aParamNames[i] = 'subquery' then begin
         session['subquery'] := aParamValues[i];
         break;
     end;
  end;
end;
  • client-side:
procedure TMainForm.Button1Click(Sender: TObject);
const
  subtable = 'Customers';
var
  t: TDAMemDataTable;
  wb: TDAWhereBuilder;
begin
  t := ServerDataModule.tbl_Customers;
  wb :=t.DynamicWhere;
  if not t.Active then begin
    wb.Clear;
    wb.Expression := wb.NewBinaryExpression(
                        wb.NewField(t.LogicalName,'CustomerID'),
                        wb.NewMacro('session_subquery'), //'subquery' parameters will be read from Session parameter
                        dboIn);
    t.ParamByName('subquery').AsString :='select CustomerID from Customers where [CustomerID] <> ''ALFKI'''; // our subquery 
  end;
  t.Active := not t.Active;
end;

note: this solution will work with simple parameters only.
you can create additional parameter like subquery_sql what will contain subquery where conditional as DynamicWhere xml