top()#

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

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

Parameters#

NameTypeDescription
limitintThe maximum number of top-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 top() to limit Producer objects to a specified number of the largest 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=25)
    Person.add(id=3).set(name="John", age=30)
    Person.add(id=4).set(name="Jill", age=30)


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


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

print(response.results)
#    name
# 0  Jill
# 1  John


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

print(response.results)
#    name
# 0  Jill
# 1  John

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

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

print(response.results)
#    name
# 0  Jill

Like other aggregation functions, top() has a per parameter that can be used to group values and limit Producer objects to the specified number of largest 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 oldest friends.
with model.query() as select:
    person = Person()
    aggregates.top(1, person.friends.age, per=[person])
    response = select(person.name, alias(person.friends.name, "friend_name"))

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

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

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

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

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

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

See Also#