Querying: Intersection

Let's consider a case where we have a T-Shirt class:

class TShirtType:
    def __init__(self, color: str = None, size: str = None):
        self.color = color
        self.size = size


class TShirt:
    def __init__(
        self, Id: str = None, release_year: int = None, manufacturer: str = None, types: List[TShirtType] = None
    ):
        self.Id = Id
        self.release_year = release_year
        self.manufacturer = manufacturer
        self.types = types

We will fill our database with a few records:

session.store(
    TShirt(
        Id="tshirts/1",
        manufacturer="Raven",
        release_year=2010,
        types=[
            TShirtType(color="Blue", size="Small"),
            TShirtType(color="Black", size="Small"),
            TShirtType(color="Black", size="Medium"),
            TShirtType(color="Gray", size="Large"),
        ],
    )
)

session.store(
    TShirt(
        Id="tshirts/2",
        manufacturer="Wolf",
        release_year=2011,
        types=[
            TShirtType(color="Blue", size="Small"),
            TShirtType(color="Black", size="Large"),
            TShirtType(color="Gray", size="Medium"),
        ],
    )
)

session.store(
    TShirt(
        Id="tshirts/3",
        manufacturer="Raven",
        release_year=2011,
        types=[TShirtType(color="Yellow", size="Small"), TShirtType(color="Gray", size="Large")],
    )
)

session.store(
    TShirt(
        Id="tshirts/4",
        manufacturer="Raven",
        release_year=2012,
        types=[TShirtType(color="Blue", size="Small"), TShirtType(color="Gray", size="Large")],
    )
)

Now we cn use the intersect method to return all the T-shirts that are manufactured by Raven and contain both Small Blue and Large Gray types.

results = list(
    session.query_index_type(
        TShirts_ByManufacturerColorSizeAndReleaseYear,
        TShirts_ByManufacturerColorSizeAndReleaseYear.Result,
    )
    .where_equals("Manufacturer", "Raven")
    .intersect()
    .where_equals("Color", "Blue")
    .and_also()
    .where_equals("Size", "Small")
    .intersect()
    .where_equals("Color", "Gray")
    .and_also()
    .where_equals("Size", "Large")
)

class TShirts_ByManufacturerColorSizeAndReleaseYear(AbstractIndexCreationTask):
    class Result:
        def __init__(self, manufacturer: str = None, color: str = None, size: str = None, release_year: int = None):
            self.manufacturer = manufacturer
            self.color = color
            self.size = size
            self.release_year = release_year

    def __init__(self):
        super().__init__()
        self.map = (
            "from tshirt in docs.TShirts from type in tshirt.types select new {"
            "  manufacturer = tshirt.manufacturer,"
            "  color = tshirt.color,"
            "  size = type.size,"
            "  release_year = tshirt.release_year"
            "}"
        )

from index 'TShirts/ByManufacturerColorSizeAndReleaseYear' 
where intersect(Manufacturer = 'Raven', Color = 'Blue' and Size = 'Small', Color = 'Gray' and Size = 'Large')

The above query will return tshirts/1 and tshirts/4 as a result.
The document tshirts/2 will not be included because it is not manufactured by Raven, and tshirts/3 is not available in Small Blue so it does not match all the sub-queries.