ServiceStack AutoQuery Filtering by Many-to-Many Relationships

I am writing this post as I have used AutoQuery in many projects now and have had a lot of difficulties with it, some which I have overcome, some which I cannot figure out a good solution to.

Quick Disclaimer: I enjoy ServiceStack and really want to get AutoQuery to a point where it is useful and saves me time so hopefully this post will garner some suggestions on how to improve my approach.

The main issues I have are with filtering and paging. The documentation only discusses filtering by a single table but in real world projects you need to be able to filter by relations.

I have built a test project that highlights the real-world issues I have run into using this in clients projects where I have common front end requirements I have to meet.

Sample Project

In this project I will be creating a book search. The book search will have requirements from the front end to filter on various different attributes that mirror real requests I get from the front end in my work.

Full code is here: https://github.com/GuerrillaCoder/AqFilters

First time it is run it will generate a database. If you don’tr want to wait import db.sql

Firstly I will have a book class that will be main holder of the book information.

    public class Book
    {
        [AutoIncrement]
        public int Id { get; set; }
        public string Title { get; set; }
        public List<Author> Authors {get; set;}
        public List<Category> Categories { get; set; }
        public List<BookFormat> BookFormats { get; set; }

        [Reference]
        public List<BookCategory> BookCategories { get; set; }
        [Reference] 
        public List<BookAuthor> BookAuthors { get; set; }
        [Reference] 
        public List<BookBookFormat> BookBookFormats { get; set; }
    }

Books can come in multiple formats like paperback, hardback, ebook etc.. so there is a BookFormat class. Also each book can have many different authors and categories.

For ease of querying I have created blobbed lists on the main class. PostgreSQL (my database of choice) does support querying blobbed data like this if marked as JsonB but AutoQuery does not so I have blobbed data for easy querying and also created junction tables so I can filter with AutoQuery. The data in the relational tables and blobbed fields is the same.

The junction tables look like this:

Book <= BookAuthor => Author
Book <= BookCategory => Category
Book <= BookBookFormat => BookFormat

Here are the rest of the model definitions:

    public class Author
    {
        [AutoIncrement]
        public int Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
        public string Natiionality { get; set; }
    }

    public class Category
    {
        [AutoIncrement]
        public int Id { get; set; }
        [Index(Unique =true)]
        public string Name { get; set; }
        public string Code { get; set; }
    }

    public class BookFormat
    {
        [AutoIncrement]
        public int Id { get; set; }
        public string Name { get; set; }
    }

    //Junction tables
    public class BookAuthor
    {
        [AutoIncrement]
        public int Id { get; set; }
        [ForeignKey(typeof(Book))]
        public int BookId { get; set; }
        [ForeignKey(typeof(Author))]
        public int AuthorId { get; set; }
    }

    public class BookCategory
    {
        [AutoIncrement]
        public int Id { get; set; }
        [ForeignKey(typeof(Book))]
        public int BookId { get; set; }
        [ForeignKey(typeof(Category))]
        public int CategoryId { get; set; }
    }

    public class BookBookFormat
    {
        [AutoIncrement]
        public int Id { get; set; }
        public decimal Price { get; set; }
        [ForeignKey(typeof(Book))]
        public int BookId { get; set; }
        [ForeignKey(typeof(BookFormat))]
        public int BookFormatId { get; set; }
    }

Joining Data

As covered in the AutoQuery documentation I can join tables using the IJoin interface. This fits our purpose here as books have different formats and the format price is stored in the junction table. By joining them we can create a dataset where each record represent a product for sale (this is typical type of thing where I use joins in projects).

I define the join in the request object:

    public class BookSearchRequest : QueryDb<Book, BookProduct>, IJoin<Book, BookBookFormat>
    {
    }

Because I have joined 2 entities I have created a new DTO BookProduct to hold the combination of the fields and added it to the QueryDb declaration.

    public class BookProduct
    {
        public int BookId { get; set; }
        public string Title { get; set; }
        public decimal Price { get; set; }
        public int BookFormatId { get; set; }
        public List<Author> Authors { get; set; }
        public List<Category> Categories { get; set; }
        public List<BookFormat> BookFormats { get; set; }
    }

Querying The Data

Now we have everything setup to use the AutoQuery magic. The front end can grab any data they like and filter it by properties on the table.

For instance:

http://localhost:5001/html/reply/BookSearchRequest?Price%3E=50&include=Total

This query will bring back all books of all formats priced over 50.

This is great but in the real world the front end will be asking to filter on multiple fields that appear in multiple different tables.

Filtering The Data

The ServiceStack AutoQuery documentation only covers joins so initially I tried to join all the tables that had fields I wanted to filter by. This works but it breaks paging as all the joins multiply the number of records. To get the right number of records returning I tried adding a GroupBy but the performance was really slow. It also has further bad performance because the join is put onto every query whether it’s needed or not. Here we don’t want to get the data on the related tables, we just want to use it as a filter.

I came up with a partial solution which is to use a second AutoQuery query in a sub-query.

So if we want to be able to query by Author fields we can first generate an AuthorRequest object

    public class AuthorRequest : QueryDb<Author>
    {
    }

Then create a custom implementation of AutoQuery

	public object Any(BookSearchRequest request)
	{
		var q = AutoQuery.CreateQuery(request, base.Request);

		if (q.Params.Count < 1) q.Where(x => 1 == 1);

		var authorRequest = new AuthorRequest();
		var authorAuto = AutoQuery.CreateQuery(authorRequest, base.Request)
			.Join<BookAuthor>()
			.Select<BookAuthor>(x => x.BookId)
			.ClearLimits();

		var catRequest = new CategoryRequest();
		var catAuto = AutoQuery.CreateQuery(catRequest, base.Request)
			.Join<BookCategory>()
			.Select<BookCategory>(x => x.BookId)
			.ClearLimits();


		if (authorAuto.Params.Count > 0) q.And<Book>(x => Sql.In(x.Id, authorAuto));
		if (catAuto.Params.Count > 0) q.And<Book>(x => Sql.In(x.Id, catAuto));

		var results = AutoQuery.Execute(request, q);

		return results;
	}

This creates a sub-query of all Ids that match the filters that apply to the Author table and then joins it to the junction table so we can select the list of BookIds. Then we add a where clause to existing query that checks if the BookId exists in the sub query. It does the same for categories.

This works. Because you can check for parameters on the query you can conditionally add the sub-query in so it doesn’t run on every query.

With the above code we can now filter by all author and category data such as:

http://localhost:5001/html/reply/BookSearchRequest?price%3E=50&AuthorNameContains=Jeff&CategoryNameContains=tool&include=Total

Mixing AND/OR Across Multiple Tables

One thing that is a really common request from the front-end is for multi-column search. It is a trend now where there is just one input box and it searches multiple columns.

For instance a typical request might be:

  • Search for a keyword in Title OR Author Name OR Nationality
  • All other filters should be treated as AND

I would like to be able to setup Autoquery in such a way that this can be achieved when filtering with multiple tables.

It can be done on single table requests with data annotations but I am finding making it work across multiple tables tricky.

To keep things simple lets try to structure filter across multiple tables with this rule:

  • Book properties = OR
  • Author properties = OR
  • Category properties = AND

I’ll define the OR requests and change default behaviour

    [QueryDb(QueryTerm.Or)]
    public class BroadCategoryBookSearchRequest : QueryDb<Book, BookProduct>, IJoin<Book, BookBookFormat>
    {
    }

    [QueryDb(QueryTerm.Or)]
    public class AuthorRequestOr : QueryDb<Author>
    {
    }

Then create a service with the AND/OR methods implemented

	public object Any(BroadCategoryBookSearchRequest request)
	{
		var q = AutoQuery.CreateQuery(request, base.Request);

		if (q.Params.Count < 1) q.Where(x => 1 == 1);

		var authorRequest = new AuthorRequestOr();
		var authorAuto = AutoQuery.CreateQuery(authorRequest, base.Request)
			.Join<BookAuthor>()
			.Select<BookAuthor>(x => x.BookId)
			.ClearLimits();

		var catRequest = new CategoryRequest();
		var catAuto = AutoQuery.CreateQuery(catRequest, base.Request)
			.Join<BookCategory>()
			.Select<BookCategory>(x => x.BookId)
			.ClearLimits();

		if (authorAuto.Params.Count > 0) q.Or<Book>(x => Sql.In(x.Id, authorAuto));
		if (catAuto.Params.Count > 0) q.And<Book>(x => Sql.In(x.Id, catAuto));

		var results = AutoQuery.Execute(request, q);

		return results;
	}

This gets very close to working. If querying this URL:

http://localhost:5001/html/reply/BroadCategoryBookSearchRequest?TitleContains=Frozen&AuthorNameContains=jeff&CategoryId=19&include=Total

It generates this SQL

SELECT "book_book_format"."book_id",
       "book"."title",
       "book_book_format"."price",
       "book_book_format"."book_format_id",
       "book"."authors",
       "book"."categories",
       "book"."book_formats"
FROM   "book"
       INNER JOIN "book_book_format"
               ON ( "book"."id" = "book_book_format"."book_id" )
WHERE  Upper("book"."title") LIKE Upper('%Frozen%')

OR "book"."id" IN (SELECT "book_author"."book_id"
                   FROM   "author"
                          INNER JOIN "book_author"
                                  ON ( "author"."id" =
                                       "book_author"."author_id" )
                   WHERE  Upper("author"."name") LIKE Upper('%jeff%'))

AND "book"."id" IN (SELECT "book_category"."book_id"
                     FROM   "category"
                            INNER JOIN "book_category"
                                    ON ( "category"."id" = "book_category"."category_id" )
                    WHERE  "category"."id" = 19)

But to get the desired effect there would need to be a group set like this (notice the extra parenthesis)

SELECT "book_book_format"."book_id",
       "book"."title",
       "book_book_format"."price",
       "book_book_format"."book_format_id",
       "book"."authors",
       "book"."categories",
       "book"."book_formats"
FROM   "book"
       INNER JOIN "book_book_format"
               ON ( "book"."id" = "book_book_format"."book_id" )
WHERE  (Upper("book"."title") LIKE Upper('%Frozen%')

OR "book"."id" IN (SELECT "book_author"."book_id"
                   FROM   "author"
                          INNER JOIN "book_author"
                                  ON ( "author"."id" =
                                       "book_author"."author_id" )
                   WHERE  Upper("author"."name") LIKE Upper('%jeff%')))

AND "book"."id" IN (SELECT "book_category"."book_id"
                     FROM   "category"
                            INNER JOIN "book_category"
                                    ON ( "category"."id" = "book_category"."category_id" )
                    WHERE  "category"."id" = 19)

I have tried but I can’t figure out how to add a grouping like this with OrmLite fluent API

I think I need to cast it to SQL then use that but I can’t seem to figure it out.

Anyone know how to do this? Is it possible?

Leave a comment

Your email address will not be published. Required fields are marked *