Выборка данных из нескольких неймспейсов

ResultIterator<Item> query = db.query("items_with_join", Actor.class)
        .innerJoin(
            db.query("actors", Actor.class)
                .where("is_visible", Condition.EQ, true)
                .on("actors_ids", Condition.SET, "id")
                .or()
                .on("actors_names", Condition.SET, "name");
            "actors"
        );

ResultIterator<Item> query1 = db.query("items_with_join", Actor.class)
    .where("id", Condition.RANGE, 0, 100)
    .or()
    .innerJoin(
        db.query("actors", Actor.class)
            .where("name", Condition.EQ, "ActorName")
            .on("actors_ids", Condition.SET, "id"),
        "actors"
    )
    .or()
    .innerJoin(
        db.query("actors", Item.class)
            .where("id", Condition.RANGE, 100, 200)
            .on("actors_ids", Condition.SET, "id"),
        "actors"
    )

ResultIterator<Item> query2 = db.query("items_with_join", Actor.class)
    .where("id", Condition.RANGE, 0, 100)
    .or()
    .openBracket()
        .innerJoin(
            db.query("actors", Actor.class)
                .where("name", Condition.EQ, "ActorName")
                .on("actors_ids", Condition.SET, "id"),
            "actors"
        )
        .innerJoin(
            db.query("actors", Actor.class)
                .where(("id", Condition.RANGE, 100, 200")
                .on("actors_ids", Condition.SET, "id"),
            "actors"
        )
    .closeBracket();

favorites = [
    {
        "name": "id",
        "json_paths": ["id"],
        "field_type": "int",
        'is_pk': True,
    },
    {
        "name": "content_id",
        "json_paths": ["content_id"],
        "field_type": "int",
        "index_type": "tree"
    }
]

for index in favorites:
    db.index_add("favorites", index)

media_items = [
    {
        "name": "id",
        "json_paths": ["id"],
        "field_type": "int",
        'is_pk': True,
    },
    {
        "name": "year",
        "json_paths": ["year"],
        "field_type": "int",
        "index_type": "tree",
    }
]

for index in media_items:
    db.index_add("media_items", index)

query2 = db.new_query("favorites")
query1 = (
    db.new_query("media_items")
         .where("year", CondType.CondGt, 1990)
         .inner_join(query2, "joined_favorites")
         .on("id", CondType.CondSet, "content_id")
)

import ru.rt.restream.reindexer.annotations.Transient;

public static class Favorite {
    @Reindex(name = "id", isPrimaryKey = true)
    private int id;

    @Reindex(name = "content_id", type = IndexType.TREE)
    private int contentId;

    public Favorite() {
    }

    public Favorite(int id, int contentId) {
        this.id = id;
        this.contentId = contentId;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getContentId() {
        return contentId;
    }

    public void setContentId(int contentId) {
        this.contentId = contentId;
    }

    @Override
    public String toString() {
        return "Favorite{" +
                "id=" + id +
                ", contentId=" + contentId +
                '}';
    }
}

public static class MediaItem {
    @Reindex(name = "id", isPrimaryKey = true)
    private int id;

    @Reindex(name = "year", type = IndexType.TREE)
    private int year;

    @Transient
    private List<Favorite> joinedFavorites;

    public MediaItem() {
    }

    public MediaItem(int id, int year, List<Favorite> joinedFavorites) {
        this.id = id;
        this.year = year;
        this.favorites = joinedFavorites;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getYear() {
        return year;
    }

    public void setYear(int year) {
        this.year = year;
    }

    public List<Favorite> getFavorites() {
        return joinedFavorites;
    }

    public void setFavorites(List<Favorite> joinedFavorites) {
    this.favorites = joinedFavorites;
    }

    @Override
    public String toString() {
        return "MediaItem{" +
                "id=" + id +
                ", year=" + year +
                ", joinedFavorites=" + joinedFavorites +
                '}';
    }
}

// ...

Query<MediaItem> query = db.query("media_item", MediaItem.class)
        .where("year", Condition.GT, 1990)
        .innerJoin(
                db.query("favorites", Favorite.class)
                        .on("id", Condition.SET, "content_id"),
                "joinedFavorites");

SELECT * FROM media_items WHERE year > 1990 INNER JOIN favorites ON (favorites.content_id IN media_items.id)

curl --location --request POST 'http://127.0.0.1:9088/api/v1/db/testdb/query' \
--header 'Content-Type: application/json' \
--data-raw '{
  "namespace": "media_items",
  "type": "select",
  "filters": [
    {
      "field": "year",
      "cond": "GE",
      "value": [
        1990
      ],
      "join_query": {
        "namespace": "favorites",
        "type": "INNER",
        "on": [
          {
            "left_field": "id",
            "right_field": "content_id",
            "cond": "EQ"
          }
        ]
      }
    }
  ]
}'

countries_indexes = [
    {
        "name": "id",
        "json_paths": ["id"],
        "field_type": "int",
        "index_type": "hash",
        "is_pk": True,
    },
    {
        "name": "active",
        "json_paths": ["active"],
        "field_type": "bool",
        "index_type": "-",
    },
]

for idx in countries_indexes:
    db.index_add("countries", idx)

locations_indexes = [
    {
        "name": "id",
        "json_paths": ["id"],
        "field_type": "int",
        "index_type": "hash",
        "is_pk": True,
    },
    {
        "name": "code",
        "json_paths": ["code"],
        "field_type": "int",
        "index_type": "tree",
    },
    {
        "name": "country_id",
        "json_paths": ["country_id"],
        "field_type": "int",
        "index_type": "hash",
    },
]

for idx in locations_indexes:
    db.index_add("locations", idx)

authors_indexes = [
    {
        "name": "id",
        "json_paths": ["id"],
        "field_type": "int",
        "index_type": "hash",
        "is_pk": True,
    },
    {
        "name": "age",
        "json_paths": ["age"],
        "field_type": "int",
        "index_type": "tree",
    },
    {
        "name": "location_id",
        "json_paths": ["location_id"],
        "field_type": "int",
        "index_type": "hash",
    },
]

for idx in authors_indexes:
    db.index_add("authors", idx)

books_indexes = [
    {
        "name": "id",
        "json_paths": ["id"],
        "field_type": "int",
        "index_type": "hash",
        "is_pk": True,
    },
    {
        "name": "price",
        "json_paths": ["price"],
        "field_type": "int",
        "index_type": "tree",
    },
    {
        "name": "author_id",
        "json_paths": ["author_id"],
        "field_type": "int",
        "index_type": "hash",
    },
]

for idx in books_indexes:
    db.index_add("books", idx)

query_countries = db.new_query("countries").where(
    "active", CondType.CondEq, True
)

query_locations = db.new_query("locations").where("code", CondType.CondGe, 1)
query_locations.inner_join(query_countries, "countries").on(
    "country_id", CondType.CondEq, "id"
)

query_authors = db.new_query("authors").where("age", CondType.CondGe, 40)
query_authors.inner_join(query_locations, "locations").on(
    "location_id", CondType.CondEq, "id"
)

query_books = db.new_query("books").where("price", CondType.CondGe, 10)
query_books.inner_join(query_authors, "authors").on(
    "author_id", CondType.CondEq, "id"
)

result = query_books.execute()
for item in result:
    print(item)

import ru.rt.restream.reindexer.annotations.Transient;

public static class Book {
    @Reindex(name = "id", isPrimaryKey = true)
    private int id;

    @Reindex(name = "title")
    private String title;

    @Reindex(name = "price", type = IndexType.TREE)
    private int price;

    @Reindex(name = "author_id")
    private int authorId;

    @Transient
    private List<Author> authors;

    public Book() {
    }

    public Book(int id, String title, int price, int authorId, List<Author> authors) {
        this.id = id;
        this.title = title;
        this.price = price;
        this.authorId = authorId;
        this.authors = authors;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public int getPrice() {
        return price;
    }

    public void setPrice(int price) {
        this.price = price;
    }

    public int getAuthorId() {
        return authorId;
    }

    public void setAuthorId(int authorId) {
        this.authorId = authorId;
    }

    public List<Author> getAuthors() {
        return authors;
    }

    public void setAuthors(List<Author> authors) {
        this.authors = authors;
    }

    @Override
    public String toString() {
        return "Book{" +
                "id=" + id +
                ", title='" + title + '\'' +
                ", price=" + price +
                ", authorId=" + authorId +
                '}';
    }
}

public static class Author {
    @Reindex(name = "id", isPrimaryKey = true)
    private int id;

    @Reindex(name = "name")
    private String name;

    @Reindex(name = "age", type = IndexType.TREE)
    private int age;

    @Reindex(name = "location_id")
    private int locationId;

    @Transient
    private List<Location> locations;

    @Transient
    private List<Book> books;

    public Author() {
    }

    public Author(int id, String name, int age, int locationId, List<Location> locations) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.locationId = locationId;
        this.locations = locations;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public int getLocationId() {
        return locationId;
    }

    public void setLocationId(int locationId) {
        this.locationId = locationId;
    }

    public List<Location> getLocations() {
        return locations;
    }

    public void setLocations(List<Location> locations) {
        this.locations = locations;
    }

    public List<Book> getBooks() {
        return books;
    }

    public void setBooks(List<Book> books) {
        this.books = books;
    }

    @Override
    public String toString() {
        return "Author{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", locationId=" + locationId +
                '}';
    }
}

public static class Location {
    @Reindex(name = "id", isPrimaryKey = true)
    private int id;

    @Reindex(name = "city")
    private String city;

    @Reindex(name = "code", type = IndexType.TREE)
    private int code;

    @Reindex(name = "country_id")
    private int countryId;

    @Transient
    private List<Country> countries;

    public Location() {
    }

    public Location(int id, String city, int code, int countryId, List<Country> countries) {
        this.id = id;
        this.city = city;
        this.code = code;
        this.countryId = countryId;
        this.countries = countries;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }

    public int getCode() {
        return code;
    }

    public void setCode(int code) {
        this.code = code;
    }

    public int getCountryId() {
        return countryId;
    }

    public void setCountryId(int countryId) {
        this.countryId = countryId;
    }

    public List<Country> getCountries() {
        return countries;
    }

    public void setCountries(List<Country> countries) {
        this.countries = countries;
    }

    @Override
    public String toString() {
        return "Location{" +
                "id=" + id +
                ", city='" + city + '\'' +
                ", code=" + code +
                ", countryId=" + countryId +
                '}';
    }
}

public static class Country {
    @Reindex(name = "id", isPrimaryKey = true)
    private int id;

    @Reindex(name = "name")
    private String name;

    @Reindex(name = "active")
    private boolean active;

    public Country() {
    }

    public Country(int id, String name, boolean active) {
        this.id = id;
        this.name = name;
        this.active = active;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public boolean isActive() {
        return active;
    }

    public void setActive(boolean active) {
        this.active = active;
    }

    @Override
    public String toString() {
        return "Country{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", active=" + active +
                '}';
    }
}

Query<Country> countryQuery = db.query("countries", Country.class)
        .where("active", Condition.EQ, true);

Query<Location> locationQuery = db.query("locations", Location.class)
        .where("code", Condition.GE, 1)
        .innerJoin(countryQuery.on("country_id", Condition.EQ, "id"), "countries");

Query<Author> authorQuery = db.query("authors", Author.class)
        .where("age", Condition.GE, 40)
        .innerJoin(locationQuery.on("location_id", Condition.EQ, "id"), "locations");

Query<Book> booksQuery = db.query("books", Book.class)
        .where("price", Condition.GE, 10)
        .innerJoin(authorQuery.on("author_id", Condition.EQ, "id"), "authors");

ResultIterator<Book> books = booksQuery.execute();

while (books.hasNext()) {
    Book book = books.next();
    for (Author author : book.getAuthors()) {
        for (Location location : author.getLocations()) {
            for (Country country : location.getCountries()) {
                System.out.printf("%s, %s в %s, %s\n",
                        book.getTitle(), author.getName(), location.getCity(), country.getName());
            }
        }
    }
}

books.close();

SELECT * FROM books
WHERE price >= 10
INNER JOIN (
    SELECT * FROM authors
    WHERE age >= 40
    INNER JOIN (
        SELECT * FROM locations
        WHERE code >= 1
        INNER JOIN (
            SELECT * FROM countries
            WHERE active = true
        ) ON locations.country_id = countries.id
    ) ON authors.location_id = locations.id
) ON books.author_id = authors.id

curl --location --request POST 'http://127.0.0.1:9088/api/v1/db/testdb/query' \
--header 'Content-Type: application/json' \
--data-raw '{
  "namespace": "books",
  "type": "select",
  "filters": [
    {
      "field": "price",
      "cond": "GE",
      "value": [10]
    },
    {
      "op": "AND",
      "join_query": {
        "namespace": "authors",
        "type": "INNER",
        "filters": [
          {
            "field": "age",
            "cond": "GE",
            "value": [40]
          },
          {
            "op": "AND",
            "join_query": {
              "namespace": "locations",
              "type": "INNER",
              "filters": [
                {
                  "field": "code",
                  "cond": "GE",
                  "value": [1]
                },
                {
                  "op": "AND",
                  "join_query": {
                    "namespace": "countries",
                    "type": "INNER",
                    "filters": [
                      {
                        "field": "active",
                        "cond": "EQ",
                        "value": [true]
                      }
                    ],
                    "on": [
                      {
                        "left_field": "country_id",
                        "right_field": "id",
                        "cond": "EQ"
                      }
                    ]
                  }
                }
              ],
              "on": [
                {
                  "left_field": "location_id",
                  "right_field": "id",
                  "cond": "EQ"
                }
              ]
            }
          }
        ],
        "on": [
          {
            "left_field": "author_id",
            "right_field": "id",
            "cond": "EQ"
          }
        ]
      }
    }
  ]
}'

q1 = db.new_query("books").where("price", CondType.CondGe, 10)
q1.inner_join(db.new_query("authors"), "authors").on(
    "author_id", CondType.CondEq, "id"
)

q2 = db.new_query("books").where("price", CondType.CondLt, 10)
q2.inner_join(db.new_query("authors"), "authors").on(
    "author_id", CondType.CondEq, "id"
)

q1.merge(q2)

Query<Author> mergeAuthors1 = db.query("authors", Author.class);
Query<Book> q1 = db.query("books", Book.class)
        .where("price", Condition.GE, 10)
        .innerJoin(mergeAuthors1.on("author_id", Condition.EQ, "id"), "authors");

Query<Author> mergeAuthors2 = db.query("authors", Author.class);
Query<Book> q2 = db.query("books", Book.class)
        .where("price", Condition.LT, 10)
        .innerJoin(mergeAuthors2.on("author_id", Condition.EQ, "id"), "authors");

Query<Book> merged = q1.merge(q2);

SELECT * FROM books
WHERE price >= 10
INNER JOIN authors ON books.author_id = authors.id
MERGE (
    SELECT * FROM books
    WHERE price < 10
    INNER JOIN authors ON books.author_id = authors.id
)

curl --location --request POST 'http://127.0.0.1:9088/api/v1/db/testdb/query' \
--header 'Content-Type: application/json' \
--data-raw '{
  "namespace": "books",
  "type": "select",
  "filters": [
    {
      "field": "price",
      "cond": "GE",
      "value": [10]
    },
    {
      "op": "AND",
      "join_query": {
        "namespace": "authors",
        "type": "INNER",
        "on": [
          {
            "left_field": "author_id",
            "right_field": "id",
            "cond": "EQ"
          }
        ]
      }
    }
  ],
  "merge_queries": [
    {
      "namespace": "books",
      "type": "select",
      "filters": [
        {
          "field": "price",
          "cond": "LT",
          "value": [10]
        },
        {
          "op": "AND",
          "join_query": {
            "namespace": "authors",
            "type": "INNER",
            "on": [
              {
                "left_field": "author_id",
                "right_field": "id",
                "cond": "EQ"
              }
            ]
          }
        }
      ]
    }
  ]
}'

query = (
    db.new_query("authors")
    .inner_join(db.new_query("locations"), "locations")
    .on("location_id", CondType.CondEq, "id")
    .inner_join(db.new_query("books"), "books")
    .on("id", CondType.CondEq, "author_id")
)

Query<Location> flatLocations = db.query("locations", Location.class);
Query<Book> flatBooks = db.query("books", Book.class);

Query<Author> flatQuery = db.query("authors", Author.class)
        .innerJoin(flatLocations.on("location_id", Condition.EQ, "id"), "locations")
        .innerJoin(flatBooks.on("id", Condition.EQ, "author_id"), "books");