Annotating Queries

Annotating queries in Edgy presents some unique challenges due to the use of hash functions with prefix (select_related) paths for generating stable join names. However, if you primarily use subqueries, these issues are less likely to arise.

Annotating a Child with Parent Columns

When working with nested relationships, you might need to access parent columns from a child element. The reference_select function addresses this, functioning similarly to Django's F() function but with broader applicability.

ModelStrictModel

import sqlalchemy

import edgy

models = edgy.Registry(database=...)


class User(edgy.Model):
    name = edgy.CharField(max_length=100)

    class Meta:
        registry = models


class Profile(edgy.Model):
    user = edgy.fields.OneToOne(User, related_name="profile")
    name = edgy.CharField(max_length=100)

    class Meta:
        registry = models


for profile in await Profile.query.select_related("profile").reference_select(
    {"user": {"profile_name": "name"}}
):
    assert profile.user.profile_name == profile.name

# up to one level you can leave out the select_related()
# you can also reference columns in case you use them of the main table or explicitly provided via extra_select

for profile in await Profile.query.reference_select(
    {"user": {"profile_name": Profile.table.c.name}}
):
    assert profile.user.profile_name == profile.name

Explanation:

• The reference_select function allows you to include columns from related tables (parents) in your query results.
• In this example, it's used to bring parent columns into the child's result set, making parent data accessible alongside child data.

import sqlalchemy

import edgy

models = edgy.Registry(database=...)


class User(edgy.StrictModel):
    name = edgy.CharField(max_length=100)
    profile_name = edgy.fields.PlaceholderField(null=True)

    class Meta:
        registry = models


class Profile(edgy.Model):
    user = edgy.fields.OneToOne(User, related_name="profile")
    name = edgy.CharField(max_length=100)

    class Meta:
        registry = models


for profile in await Profile.query.select_related("profile").reference_select(
    {"user": {"profile_name": "name"}}
):
    assert profile.user.profile_name == profile.name

# up to one level you can leave out the select_related()

for profile in await Profile.query.reference_select({"user": {"profile_name": "name"}}):
    assert profile.user.profile_name == profile.name

Explanation:

• When using StrictModel, every attribute in the query result must correspond to a defined field.
• PlaceHolderField(null=True) is used to reserve attribute names for assignment, ensuring that the query results can be correctly mapped to the model's fields.
• This is crucial for maintaining type safety and preventing unexpected errors when working with strictly defined models.

Warning

With StrictModel, ensure that all query result names match defined fields. Use PlaceHolderField(null=True) to reserve attribute names for assignment.

Annotating and embed_parent

reference_select executes before embedding, enabling the addition of parent attributes to grandchild elements.

import sqlalchemy

import edgy

models = edgy.Registry(database=...)


class User(edgy.Model):
    name = edgy.CharField(max_length=100)

    class Meta:
        registry = models


class Profile(edgy.Model):
    user = edgy.fields.OneToOne(User, related_name="profile")
    name = edgy.CharField(max_length=100)
    profile = edgy.fields.OneToOne(
        "SuperProfile", related_name="profile", embed_parent=("user", "normal_profile")
    )

    class Meta:
        registry = models


class SuperProfile(edgy.Model):
    name = edgy.CharField(max_length=100)

    class Meta:
        registry = models


for profile in await SuperProfile.query.all():
    user = (
        await profile.profile.select_related("user")
        .reference_select({"user": {"profile_name": "name"}})
        .get()
    )
    assert isinstance(user, User)
    assert user.normal_profile.name == user.profile_name

Explanation:

• The embed_parent feature allows you to include parent data when fetching related children.
• By using reference_select before embedding, you can augment the data available to the grandchild with additional attributes from the parent.
• This is particularly useful for deeply nested relationships where you need to access data from multiple levels.

Annotating Child Attributes to the Parent

reference_select operates from the perspective of the main query, allowing child attributes to be set via reference_select({"user": {"user_name", "user__name"}}) on the child.

Alternatively, you can manually use the hash_tablekey helper function (though this is not recommended).

import sqlalchemy

import edgy
from edgy.core.utils.db import hash_tablekey

models = edgy.Registry(database=...)


class User(edgy.Model):
    name = edgy.CharField(max_length=100)

    class Meta:
        registry = models


class Profile(edgy.Model):
    user = edgy.fields.OneToOne(User, related_name="profile")
    name = edgy.CharField(max_length=100)

    class Meta:
        registry = models


for profile in await Profile.query.select_related("user").reference_select(
    {"user_name": sqlalchemy.text(f"user__name")}
):
    assert profile.user_name == profile.user.name


# manual way
join_table_key = hash_tablekey(tablekey=User.table.key, prefix="user")
for profile in await Profile.query.select_related("user").reference_select(
    {"user_name": sqlalchemy.text(f"{join_table_key}_name")}
):
    assert profile.user_name == profile.user.name

Explanation:

• reference_select allows you to pull data not only from parent tables to children, but also from children to parents.
• The dictionary passed to reference_select specifies the relationship and the fields to be included.
• In this example, child attributes (e.g., user_name, user__name) are being added to the parent's result set.
• Using hash_tablekey manually is possible but generally discouraged due to its complexity and potential for errors.

Annotating and Subqueries

Beyond referencing child-parent structures, you can reference arbitrary select statements using extra_select.

import sqlalchemy
from sqlalchemy import func

import edgy

models = edgy.Registry(database=...)


class User(edgy.Model):
    name = edgy.CharField(max_length=100)

    class Meta:
        registry = models


class Profile(edgy.Model):
    user = edgy.fields.OneToOne(User, related_name="profile")
    name = edgy.CharField(max_length=100)

    class Meta:
        registry = models


for profile in await Profile.query.extra_select(
    func.count()
    .select()
    .select_from((await User.query.as_select()).subquery())
    .label("total_number")
).reference_select({"total_number": "total_number"}):
    assert profile.total_number == 10


# or manually
for profile in await Profile.query.extra_select(
    sqlalchemy.select(func.count(User.table.c.id).label("total_number")).subquery()
).reference_select({"total_number": "total_number"}):
    assert profile.total_number >= 0

Explanation:

• extra_select allows you to include custom SQL expressions or subqueries in your query results.
• You can add multiple extra_select entries, each with a unique label to prevent collisions.
• This provides flexibility to include calculated values or data from other tables that are not directly related through model relationships.
• The example shows how to add a subquery that counts the number of posts associated with each user.

Furthermore, you can reference Common Table Expressions (CTEs) by knowing their name or providing the column.

Explanation:

• CTEs, like subqueries, can be used to add complex data to your queries.
• By referencing a CTE's name or providing the necessary column information, you can include the results of the CTE in your query.
• This allows for more advanced data manipulation and aggregation within your queries.
• This is very useful for optimizing complex queries by breaking them down into simpler, reusable parts.