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!