Annotating queries

Annotating queries is a bit difficult in edgy because we use a hash function with a prefix (select_related) path to generate stable names for joins. But given you mostly use just a subquery you won't run often in this case.

Annotating a child with the parent columns

We have embed target for drilling down to a child element but sometimes lack parameters of the parent. Here comes the function reference_select to help. This works analogue to django's F() function but is more generic.

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

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

Warning

In case you use the StrictModel every name where the query result is attached must be a field. You can use PlaceHolderField(null=True) for reserving an attribute name for assignment.

Annotating and embed_parent

reference_select is executed before embedding. This can be helpful to add some parent attributes to the grand child.

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

Annotating child attributes to the parent

reference_select point of view is always the main query. This means you can set child attributes via

reference_select({"user": {"user_name", "user__name"}}) to the child itself.

Or you can do all manually by using the helper function hash_tablekey (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

Annotating and subqueries

Until now we referenced only child parent structures but we can also reference arbitary select statements. How to insert some? Using extra_select. You can add as many as you want and label them as you want but are responsible for collisions.

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

The cool thing is you are not limited to subqueries. CTEs can be referenced too, you just need to know the name or to provide the column.