SQL Multiple LIKE statements

Discussion of open issues, suggestions and bugs regarding EntityDAC
Post Reply
markdelphi
Posts: 16
Joined: Sat 08 Sep 2012 19:43

SQL Multiple LIKE statements

Post by markdelphi » Wed 21 Sep 2016 01:55

Hi, I'm trying to make a consultation with several Contains as follows:

Code: Select all

(DetInv.IdInventario = 32)  and (DetInv.Descripcion.Contains(''%PANT%''))  and (DetInv.Marca.Contains(''%EVIL%'')) 
Error

'Expression compilation error.

From(DetInv).Where().Select('*')


[TQueryConverter]

Invalid node type
{ 0 }'.
Process InvSiscab.exe (11624)

But I make a mistake, is there any way?

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: SQL Multiple LIKE statements

Post by AlexP » Wed 21 Sep 2016 06:31

Hello,

We can't reproduce the issue. Please specify the database you are working with, the exact versions of EntityDAC and IDE, and also provide the full text of LINQ query

markdelphi
Posts: 16
Joined: Sat 08 Sep 2012 19:43

Re: SQL Multiple LIKE statements

Post by markdelphi » Thu 22 Sep 2016 00:18

Hello
I am using Delphi Seattle with EntityDAC 1.4.8 and Firebird 2.1.3

I have to build a query with multiple fields but depends on the user to choose the fields by which you want to filter, then as I do not know what are the fields need to build the query at runtime. I was doing as follows:

Code: Select all

Condicion := '(DetInv.IdInventario = ' + IntToStr(FID_Inventario) + ') ';
for i := 0 to cboCampo.Properties.Items.Count -1 do
 begin
  Titulo := cboCampo.Properties.Items.Strings[i];
  Campo :=  TString(cboCampo.Properties.Items.Objects[i]).Str;
  if FCampoValor.TryGetValue(Titulo,Valor) then
     Condicion := Condicion + ' and (DetInv.' +  Campo + '.Contains(' + QuotedStr('%' + Valor.ValorReal + '%') + ')) '
 end;
 Query := Linq.From('DetInv').Where(Condicion).Select;
 Lista :=  Context.GetEntities<TDetInv>(Query);  
 for DetInv in Lista do
  begin
   Nodo := treeProductos.FindNodeByText(DetInv.Depto,clmDepto);
   if Nodo = nil then
     Nodo := AddNodeEncabezado(DetInv.Depto);
   AgregaProducto(DetInv,Nodo);
 end;

But I make the mistake that I mentioned

I had to do it follows that although it is not good is the only solution I found.

Code: Select all

procedure TfrmInventario.AplicaFiltro(ATipoCombo: TTipoComboFiltro);

 function rtnCondicion : TExpression;
  var
   i : integer;
   Campo,Titulo,Condicion : string;
   Valor : TValoresCampo;
   DI : IDetInvExpression;
   ExTemp : TExpression;
  begin
   with dmInventario do
    begin
     DI := Context.DetInv;
     ExTemp := TExpression.Create(etVariable);
     ExTemp := (DI.IdInventario = 32);
     for i := 0 to cboCampo.Properties.Items.Count -1 do
       begin
        Titulo := cboCampo.Properties.Items.Strings[i];
        Campo :=  TString(cboCampo.Properties.Items.Objects[i]).Str;
        if FCampoValor.TryGetValue(Titulo,Valor) then
         begin
          if Campo = 'Descripcion' then
           begin
            Condicion := '%' +  Valor.ValorReal + '%';
            ExTemp := ExTemp.LogicalAnd(ExTemp,(DI.Descripcion.Contains(Condicion)));
           end
          else if Campo = 'Marca' then
           begin
            Condicion := '%' +  Valor.ValorReal + '%';
            ExTemp := ExTemp.LogicalAnd(ExTemp,(DI.Marca.Contains(Condicion)));
           end
          else if Campo = 'Tipo' then
           begin
            Condicion := '%' +  Valor.ValorReal + '%';
            ExTemp := ExTemp.LogicalAnd(ExTemp,(DI.Tipo.Contains(Condicion)));
           end
          else if Campo = 'Color' then
           begin
            Condicion := '%' +  Valor.ValorReal + '%';
            ExTemp := ExTemp.LogicalAnd(ExTemp,(DI.Color.Contains(Condicion)));
           end
          else if Campo = 'Talla' then
           begin
            Condicion := '%' +  Valor.ValorReal + '%';
            ExTemp := ExTemp.LogicalAnd(ExTemp,(DI.Talla.Contains(Condicion)));
           end
          else if Campo = 'Modelo' then
           begin
            Condicion := '%' +  Valor.ValorReal + '%';
            ExTemp := ExTemp.LogicalAnd(ExTemp,(DI.Modelo.Contains(Condicion)));
           end
          else if Campo = 'Depto' then
           begin
            Condicion := '%' +  Valor.ValorReal + '%';
            ExTemp := ExTemp.LogicalAnd(ExTemp,(DI.Depto.Contains(Condicion)));
           end
          else if Campo = 'Depto' then
           begin
            Condicion := '%' +  Valor.ValorReal + '%';
            ExTemp := ExTemp.LogicalAnd(ExTemp,(DI.Depto.Contains(Condicion)));
           end
          else if Campo = 'Codigo' then
           begin
            Condicion := '%' +  Valor.ValorReal + '%';
            ExTemp := ExTemp.LogicalAnd(ExTemp,(DI.Codigo.Contains(Condicion)));
           end
          else if Campo = 'Almacen' then
            ExTemp := ExTemp.LogicalAnd(ExTemp,(DI.IdCatMulti = Valor.ValorReal))
          else if Campo = 'Ubicacion' then
            ExTemp := ExTemp.LogicalAnd(ExTemp,(DI.Ubicacion = Valor.ValorReal));

         end;
       end;
      Result := ExTemp;
    end;
  end;

var
 Valor : TValoresCampo;
 Query : ILinqQueryable;
 DI : IDetInvExpression;
 DetInv : TDetInv;
 Lista: IEntityEnumerable<TDetInv>;
 Nodo : TcxTreeListNode;
 Campo : string;
begin
 if ATipoCombo = tcfEditor then
  begin
   Valor.ValorReal := cboFiltro.Text;
   Valor.ValorMuestra := cboFiltro.Text;
  end
 else
  begin
   Valor.ValorReal := IntToStr(Integer(cboFiltro.Properties.Items.Objects[cboFiltro.ItemIndex]));
   Valor.ValorMuestra := cboFiltro.Properties.Items.Strings[cboFiltro.ItemIndex];
  end;
 if (Valor.ValorReal = '') then
  Valor.ValorReal := Null;
 FCampoValor.AddOrSetValue(cboCampo.Properties.Items.Strings[cboCampo.ItemIndex],Valor);

 chklstFiltro.Items.Clear;
 for Campo in FCampoValor.Keys do
  begin
   if FCampoValor.Items[Campo].ValorReal <> Null then
    begin
     with chklstFiltro.Items.Add do
      begin
       Checked := True;
       Text := Campo +  ' CONTIENE '  + FCampoValor.Items[Campo].ValorMuestra;
       ItemObject := TString.Create(Campo);
      end;
    end;
  end;
 advpnlFiltro.Visible := True;
 with dmInventario do
  begin
   try
     treeProductos.Clear;
     DI := Context.DetInv;
     Query := Linq.From('DetInv').Where(rtnCondicion).Select;
     Lista :=  Context.GetEntities<TDetInv>(Query);
     for DetInv in Lista do
      begin
       Nodo := treeProductos.FindNodeByText(DetInv.Depto,clmDepto);
       if Nodo = nil then
         Nodo := AddNodeEncabezado(DetInv.Depto);
       AgregaProducto(DetInv,Nodo);
      end;
   Finally

   end
  end;
end;

I also wish there was more documentation there are many things to be guessing how they work.

AudioPat
Posts: 10
Joined: Fri 19 Jun 2015 15:47

Re: SQL Multiple LIKE statements

Post by AudioPat » Thu 22 Sep 2016 08:48

For SQL Server:

Code: Select all

((Kernel.Database.UnitRadioDataModel.Artist.Name.Contains('%' + value.Text + '%')) and (Kernel.Database.UnitRadioDataModel.Media.Title.Contains('%' + value.Text + '%')) and (Kernel.Database.UnitRadioDataModel.Media.ItemCode.Contains('%' + valueText + '%')));

markdelphi
Posts: 16
Joined: Sat 08 Sep 2012 19:43

Re: SQL Multiple LIKE statements

Post by markdelphi » Thu 22 Sep 2016 13:48

For Firebird 2.1.3

Two conditions works but if I put a third no longer works.

works

Code: Select all

'(DetInv.IdInventario = 32) and (DetInv.Descripcion.Contains(''%PANT%''))' 
It does not work

Code: Select all

'(DetInv.IdInventario = 32) and (DetInv.Descripcion.Contains(''%PANT%'')) and (DetInv.Marca.Contains(''%EVIL%%''))'

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: SQL Multiple LIKE statements

Post by AlexP » Tue 01 Nov 2016 08:52

Please specify, which EntityProvider do you use to connect with Firebird?

Post Reply