bottom()#

relationalai.std.aggregates
#bottom(limit: int, *args: Producer, per: list[Producer] = []) -> Expression

Limits values produced by one or more Producer objects to the smallest limit number of values and returns an Expression that produces the rank of each value in ascending order. Pass a list of Producer objects to the optional per keyword argument to group values and limit to the smallest limit number of values per group. Must be called in a rule or query context.

Parameters#

NameTypeDescription
limitintThe maximum number of bottom-ranked values to return.
*argsProducerOne or more Producer objects.
perlist[Producer]A list of Producer objects for grouping values. (Default: [])

Returns#

An Expression object.

Example#

Use bottom() to limit Producer objects to a specified number of the smallest values:

#import relationalai as rai
from relationalai.std import aggregates


# =====
# SETUP
# =====

model = rai.Model("MyModel")
Person = model.Type("Person")

with model.rule():
    Person.add(id=1).set(name="Joe", age=20)
    Person.add(id=2).set(name="Jane", age=20)
    Person.add(id=3).set(name="John", age=25)
    Person.add(id=4).set(name="Jill", age=30)


# =======
# EXAMPLE
# =======


# Get the names of the youngest people.
with model.query() as select:
    person = Person()
    # Filter the person.age property to only keep people with the minimum age.
    aggregates.bottom(1, person.age)
    response = select(person.name)

print(response.results)
#    name
# 0  Jane
# 1   Joe


# Alternatively, you can use the min() function to achieve the same result.
with model.query() as select:
    person = Person()
    person.age == aggregates.min(person.age)
    response = select(person.name)

print(response.results)
#    name
# 0  Jane
# 1   Joe

When you pass multiple Producer objects to bottom(), the aggregation is performed over the expansion of the arguments into rows of the form (v1, v2, ..., vn) where 1, v2, …, vn are the values produced by the Producer objects. The rows are sorted lexicographically in ascending order, and each Producer is limited to values in the specified number of rows with the smallest sort order:

## Get the name of one of the youngest people.
with model.query() as select:
    person = Person()
    # Filter (person.age, person) pairs and keep the smallest one, lexicographically.
    # When an Instance is passed to bottom(), the primary key hash for each object
    # it produces is used to sort the pairs.
    aggregates.bottom(1, person.age, person)
    response = select(person.name)

print(response.results)
#    name
# 0  Jane

Like other aggregation functions, bottom() has a per parameter that can be used to group values and limit Producer objects to the specified number of smallest values per group:

## Set a multi-valued friends property for each person according to the following diagram:
#
#     Joe (20) ---- Jane (20)
#       |        /    |
#       |      /      |
#   John (25) ---- Jill (30)
#
with model.rule():
    joe = Person(name="Joe")
    jane = Person(name="Jane")
    john = Person(name="John")
    jill = Person(name="Jill")
    joe.friends.extend([jane, john])
    jane.friends.extend([joe, john, jill])
    john.friends.extend([joe, jane, jill])
    jill.friends.extend([jane, john])

# Get the names of each person and their youngest friends.
with model.query() as select:
    person = Person()
    bottom(1, person.friends.age, per=[person])
    response = select(person.name, alias(person.friends.name, "friend_name"))

print(response.results)
#    name friend_name
# 0  Jane         Joe
# 1  Jill        Jane
# 2   Joe        Jane
# 3  John        Jane   <-- John has two friends with the smallest age.
# 4  John         Joe

bottom() returns an Expression object that produces the rank of each value, or row of values, in ascending order:

## Get the sames of three youngest people and sort them by age.
with model.query() as select:
    person = Person()
    rank = bottom(3, person.age, person)
    response = select(alias(rank, "rank"), person.name, person.age)

print(response.results)
#    rank  name  age
# 0     1  Jane   20
# 1     2   Joe   20
# 2     3  John   25

# Alternatively, you can use rank_asc() to achieve the same result.
with model.query() as select:
    person = Person()
    rank = aggregates.rank_asc(person.age, person)
    rank <= 3
    response = select(alias(rank, "rank"), person.name, person.age)

print(response.results)
#    rank  name  age
# 0     1  Jane   20
# 1     2   Joe   20
# 2     3  John   25

See Also#