Relationship Aggregates
One common type of subselect field is the count of related entites. For instance, you may want to load a Post or a list of Posts with the count of Comments on each Post. You can reuse your existing relationship definitions and add this count using the
withCount method.Adds a count of related entities as a subselect property. Relationships can be constrained at runtime by passing a struct where the key is the relationship name and the value is a function to constrain the query.
Name | Type | Required | Default | Description |
relation | any | true | | A single relation name or array of relation names to load counts. |
By default, you will access the returned count using the relationship name appended with
Count, i.e. comments will be available under commentsCount.var post = getInstance( "Post" )
.withCount( "comments" )
.findOrFail( 1 );
post.getCommentsCount();
You can alias the count attribute using the
AS syntax as follows:var post = getInstance( "Post" )
.withCount( "comments AS myCommentsCount" )
.findOrFail( 1 );
post.getMyCommentsCount();
This is especially useful as you can dynamically constrain counts at runtime using the same struct syntax as eager loading with the
with function.var post = getInstance( "Post" )
.withCount( [
"comments AS allCommentsCount",
{ "comments AS pendingCommentsCount": function( q ) {
q.where( "approved", 0 );
} },
{ "comments AS approvedCommentsCount": function( q ) {
q.where( "approved", 1 );
} }
] )
.findOrFail( 1 );
post.getAllCommentsCount();
post.getPendingCommentsCount();
post.getApprovedCommentsCount();
Note that where possible it is cleaner and more readable to create a dedicated relationship instead of using dynamic constraints. In the above example, the
Post entity could have pendingComments and approvedComments relationships. Dynamic constraints are more useful when applying user-provided data to the constraints, like searching.Adds a sum of an attribute of related entities as a subselect property. Relationships can be constrained at runtime by passing a struct where the key is the relationship name and the value is a function to constrain the query.
Name | Type | Required | Default | Description |
relationMapping | any | true | | A single relation mapping string ( relationName.attributeName) or array of relation mappings to load as summed subselects. |
By default, you will access the returned sum using the relationship name prepended with
total, i.e. purchases.amount will be available under totalPurchases.var user = getInstance( "User" )
.withSum( "purchases.amount" )
.findOrFail( 1 );
user.getTotalPurchases();
You can alias the count attribute using the
AS syntax as follows:var user = getInstance( "User" )
.withSum( "purchases.amount AS totalPurchaseAmount" )
.findOrFail( 1 );
user.getTotalPurchaseAmount();
This is especially useful as you can dynamically constrain counts at runtime using the same struct syntax as eager loading with the
with function.var user = getInstance( "User" )
.withCount( [
"purchases.amount",
{ "purchases.amount AS totalSingleQuantityPurchases": function( q ) {
q.where( "quantity", 1 );
} },
{ "purchases.amount AS totalMultipleQuantityPurchases": function( q ) {
q.where( "quantity", ">", 1 );
} }
] )
.findOrFail( 1 );
post.getTotalPurchases();
post.getTotalSingleQuantityPurchases();
post.getTotalMultipleQuantityPurchases();
Last modified 2mo ago