Use 'Contains()'

[vijaymandave]

Hi,

We have created order table and queryable_order in KSQL like below-
CREATE TABLE order (id INT PRIMARY KEY, eventtime BIGINT, ordertype INT , description VARCHAR) WITH (KAFKA_TOPIC='order', VALUE_FORMAT='PROTOBUF', PARTITIONS = 1);
CREATE TABLE queryable_order AS SELECT * FROM order;

We have written C# function as below-

using System;
using Kafka.DotNet.ksqlDB.KSql.Query;
using System.Reactive.Linq;
using Kafka.DotNet.ksqlDB.KSql.Linq.Statements;
using Kafka.DotNet.ksqlDB.KSql.Query.Context;
using Kafka.DotNet.ksqlDB.KSql.Linq;
using Kafka.DotNet.ksqlDB.KSql.Linq.PullQueries;

public static async void GetOrderStreamsAsync()
{
try
{
var url = @"http:\localhost:8088";
await using var context = new KSqlDBContext(new KSqlDBContextOptions(url));
var tablename = "queryable_order";
var orderTypes = new List { 1,3 };

	var result = context.CreateQueryStream<OrderData>(tablename)                
    .Where(o => o.OrderTime >= 1630886400 && o.OrderTime <= 1630887401 && orderTypes.Contains(o.OrderType));
	
	var query = asyncEnumerable.ToQueryString();
	Console.WriteLine(query);
	
    foreach (var o in result.ToObservable())
    {
        Console.WriteLine($"{o.Id} - {o.OrderTime} - {o.OrderType} - {o.Description}");
    }
}
catch (Exception ex)
{
    System.Console.WriteLine(ex.Message);
}

}

public class OrderData: Record
{
public int Id { get; set; }
public long OrderTime { get; set; }
public int OrderType { get; set; }
public string Description { get; set; }
}

After printing QueryString IN Query missing after AND clause -
SELECT * FROM queryable_order
WHERE ((OrderTime >= 1630886400) AND (OrderTime <= 1630886401)) AND EMIT CHANGES;

We are getting below runtime error for Contains-

extraneous input 'CHANGES' expecting ';'
Statement: SELECT * FROM queryable_order
WHERE ((OrderTime >= 1630886400) AND (OrderTime <= 1630887401)) AND EMIT CHANGES;

[tomasfabian]

hi @vijaymandave, would you expect the following sql to be generated?

SELECT * FROM queryable_order
WHERE ((OrderTime >= 1630886400) AND (OrderTime <= 1630887401)) AND
Array_Contains(Array[1, 3], ordertype)
EMIT CHANGES;

Is this what you would expect to be generated after the last AND clause?

Array_Contains(Array[1, 3], ordertype)

from C#:

orderTypes.Contains(o.OrderType)

Thank you for the detailed example.

Tomas

[vijaymandave]

Hello @tomasfabian,

Thanks to acknowledge our issue.
We want to generate query like below-
SELECT * FROM queryable_order WHERE ordertime >= 1630886400 AND ordertime <= 1630887401 AND ordertype IN (1,3)

Thank you,
Vijay

[tomasfabian]

hi @vijaymandave I implemented IN operator. Could you try it out please?

Install-Package Kafka.DotNet.ksqlDB -Version 1.6.0-rc.1

IList<T>.Contains:

var orderTypes = new List<int> { 1, 2, 3 };

Expression<Func<OrderData, bool>> expression = o => orderTypes.Contains(o.OrderType);

Enumerable extension:

IEnumerable<int> orderTypes = Enumerable.Range(1, 3);

Expression<Func<OrderData, bool>> expression = o => orderTypes.Contains(o.OrderType);

For both options the following SQL is generated:

OrderType IN (1, 2, 3)

[vijaymandave]

Thank you @tomasfabian, it works now. Appreciate your efforts.

[tomasfabian]

Not at all. I released v1.6.0. Enjoy.

[Markauto]

Hi,

Hope you are well!
Loving the library thanks for creating it!
Sorry If I am putting this comment in the wrong place.

For my class I am using GUID's as an id field and when I want to do a contains/IN its adding the GUID's but without the ' around the values.
For example:
SELECT * FROM Pages WHERE Id IN (77d6e8b9-e733-4465-b429-1a6e115daad7, aa9ab7f0-59cb-4e9a-b2a5-40cf5de3c6ab);
This should be:
SELECT * FROM Pages WHERE Id IN ('77d6e8b9-e733-4465-b429-1a6e115daad7', 'aa9ab7f0-59cb-4e9a-b2a5-40cf5de3c6ab');

Think this is because in the VisitConstant method in the KsqlVisitor class its failing this check:

if (inputValue != null && !this.isInContainsScope && (type.IsClass || type.IsStruct() || type.IsDictionary()))

because its in a contains scope, so the value doesn't get handled by the CreateKSqlValue().ExtractValue logic and therefore never gets the ' around it.

Not sure what the best fix for this would be.

Thanks
Mark

My classes:

public record Page(Guid Id, string Name, string Colour);
public class GameEventData : Record
{
   public GameEventData(Guid id, string colour, string tdm, string gameCategory, string name)
   {
       this.Id = id;
       this.Colour = colour;
       this.TDM = tdm;
       this.GameCategory = gameCategory;
       this.Name = name;
   }

   [Key]
   public Guid Id { get; set; }
   public string Colour { get; set; }
   public string TDM { get; set; }
   public string GameCategory { get; set; }
   public string Name { get; set; }
}

The Table:

 context.CreateTableStatement(TableName)
            .With(new CreationMetadata { KafkaTopic = this.streamHandler.Topic })
            .As<GameEventData>().GroupBy(c => c.Id).Select(window => new
            {
                Id = window.Key,
                Name =
                    window.LatestByOffset(gameEventData => gameEventData.Name),
                Colour = window.LatestByOffset(gameEventData =>
                    gameEventData.Colour)
            });

Then the pull query:

 public async Task<IEnumerable<Page>> GetPages(IEnumerable<Guid> ids)
    {
context.CreatePullQuery<Page>(TableName).Where(page =>ids.Contains(page.Id));
return await result.GetManyAsync().ToListAsync();
}

[tomasfabian]

Hi @Markauto,
could you please open a new issue with the above description please? Thanks!