Autoquery multiple joins and order by problem

ServiceStack Auto Query
1

Hi.

I have a service method which process autoquery request. Is there any way to make order by part to contain automatically generated alias as it is already for select statement? The order by field paramter is sent from client just as a field name without any alias.The expectation is just Autoquery will add the alias automatically (based on FROM table).

image
image1831×731 125 KB

2

Hi @diskman ,

It is a little hard to follow with out some of the definitions of your related models, but to clarify, are you already using [Alias] for the tables in question? Rather than screenshots, could you provide the related code in your post as well as the SQL output you are getting and what you expected? That will help me better understand the problem and help where I can. Thanks.

3

Hi @layoric, lets if possible ignore the model for the moment. The question primary is, if Autoquery can correctly autogenerate alias of primary table (in this case PozycjeBudzProj with schema dbsync) for ORDER BT clause. I do not use table aliases in the Entities definition. At this moement I use Sql Lite in memory provider.

The expected is that autoquery should order by as "ORDER BY "dbsync_PozycjeBudzProj".Nazwa" and not only "Nazwa" which results in exception below


SQL logic error
      ambiguous column name: Nazwa
      System.ArgumentException: SQL logic error
      ambiguous column name: Nazwa
       ---> code = Error (1), message = System.Data.SQLite.SQLiteException (0x87AF001F): SQL logic error
      ambiguous column name: Nazwa
         at ServiceStack.OrmLite.OrmLiteExecFilter.Exec[T](IDbConnection dbConn, Func`2 filter) in /home/runner/work/ServiceStack/ServiceStack/ServiceStack.OrmLite/src/ServiceStack.OrmLite/OrmLiteExecFilter.cs:line 137
         at ServiceStack.TypedQuery`2.ExecuteAsync[Into](IDbConnection db, ISqlExpression query) in /home/runner/work/ServiceStack/ServiceStack/ServiceStack/src/ServiceStack.Server/AutoQueryFeature.cs:line 1651
         --- End of inner exception stack trace ---
         at ServiceStack.TypedQuery`2.ExecuteAsync[Into](IDbConnection db, ISqlExpression query) in /home/runner/work/ServiceStack/ServiceStack/ServiceStack/src/ServiceStack.Server/AutoQueryFeature.cs:line 1661
         at ServiceStack.AutoQuery.ExecuteAsync[From,Into](IQueryDb`2 model, SqlExpression`1 query, IRequest req, IDbConnection db) in /home/runner/work/ServiceStack/ServiceStack/ServiceStack/src/ServiceStack.Server/AutoQueryFeature.cs:line 999
....
         at ServiceStack.Host.ServiceRunner`1.ExecuteAsync(IRequest req, Object instance, TRequest requestDto) in /home/runner/work/ServiceStack/ServiceStack/ServiceStack/src/ServiceStack/Host/ServiceRunner.cs:line 149
4

Hi @diskman ,

When I try to create a minimal reproduction of your issue, I can get AutoQuery to correctly use the table name in the query. Eg

    public IAutoQueryDb AutoQuery { get; set; }

    public async Task<object> Any(QueryBookings request)
    {
        using var db = AutoQuery.GetDb(request, base.Request);
        var q = AutoQuery.CreateQuery(request, base.Request, db);
        
        q = q.Join<Booking,Coupon>((b,c) => b.CouponId == c.Id);

        q.OrderBy(x => x.BookingStartDate);
        
        return await AutoQuery.ExecuteAsync(request, q);
    }

The resultant SQL from calling this service is:

SELECT "Booking"."Id", "Booking"."Name", "Booking"."BookingStartDate", "Booking"."BookingEndDate", "Booking"."Cost", "Booking"."CouponId
", "Booking"."CreatedBy" 
FROM "Booking" INNER JOIN "Coupon" ON ("Booking"."CouponId" = "Coupon"."Id")
WHERE "Booking"."DeletedDate" IS NULL AND (1=1)
ORDER BY "Booking"."BookingStartDate"

As you can see the ORDER BY uses the table name Booking as expected.

Without being able to successfully reproduce the issue, it is hard to tell what is going on with your application. If you can provide the code to reproduce the problem, or best yet a minimal reproduction in a public GitHub repository, I will take a closer look and be able to help more.

5

@layoric can you remove q.OrderBy(x => x.BookingStartDate) and set the request.OrderBy = “Id”` ?

6

@diskman The problem there is AutoQuery has no knowledge of the Join since the query is created using the request parameters and info from the Request DTOs before the q.Join is added. Eg

// AutoQuery takes request details to create details about the query
var q = AutoQuery.CreateQuery(request, base.Request, db);

// Join is added to the query `q` after AutoQuery has finished creating query from request details
q = q.Join<Booking,Coupon>((b,c) => b.CouponId == c.Id);

The `OrderBy was constructed when there was no additional joins. If you want to enable joins you can use IJoin<T1,T2>, this will correctly add the table identifier on each use of a column including in the order by statement. I would show you an example using your code, but I need a reproduction of the issue you were seeing. Again, if you share your code rather than screenshots, I can dig into the issue more and provide more examples/options about what you might be able to do to resolve your problem.

7

Hi. I was not able to use IJoin in my case. Finally I have solved the issue by creating a view so that now all sort fields are unque and complex joins are not a problem.

1 Like