Ent: Question: Join Aggregate

[sno6]

Hello,

I am wondering if it is possible to get the count of the edges of an entity.

I have the following setup:

Entities:

• search
• listing

Search edges:
edge.To("listing", Listing.Type)

Listing edges:
edge.From("search", Search.Type).Ref("listing").Unique()

Essentially I want to replicate the following query (PSQL):

SELECT "search"."id", count("listing"."id")
FROM "search"
LEFT JOIN "listing" ON "listing"."search_listing" = "search"."id"
GROUP BY "search"."id";

[a8m]

Hey @sno6!

ent doesn't expose the JOIN operation in the generated code, but you can always add your customization to the generated code using external-templates.

However, in this case, I would use a different way for querying the edges of O2M relation - I'll use the User->Pet example in the doc. It's the same, you can just replaces names.

A query for getting a list of user-ids and their number of pets:

var v []struct {
    OwnerID int `json:"user_pets"` // A struct-tag for the FK column.
    Count   int
}

client.Pet.Query().
    Where(pet.HasOwner()).  // Filter all pets without owner (a NULL value).
    GroupBy(pet.OwnerColumn).
    Aggregate(ent.Count()).
    ScanX(ctx, &v)

// Output: [{OwnerID: 4, Count: 2} {OwnerID: 6, Count: 1, ....}]

Closing, but feel free to continue the discussion if you still need help with it.

Update: Re-thinking about my suggestion - if you want to get also users with 0 edges, it won't work, so you need to fallback to the template option. Thanks.

[sno6]

Thanks for the help, external templates look interesting. This package has been super helpful for a project I'm working on, and it's refreshing to see a maintainer so active and competent.

[a8m]

Thanks for the kind words @sno6 😄

It'll be great if you can update the issue with the final solution. When I developed the aggregation option for the codegen, I didn't give it too much attention (as I wished to), so there's definitely a room for improvements here.

[dilipkk-foyernet]

@a8m is aggregation supported for m2m tables?