> For the complete documentation index, see [llms.txt](https://coldbox-orm.ortusbooks.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://coldbox-orm.ortusbooks.com/v1.x/coldbox-criteria-builder/result-transformers.md). # Result Transformers Our criteria builder also supports the notion of projections (). A projection is used to change the nature of the results, much how a result transformer does. However, there are several projection types you can use which are great for doing counts, distinct counts, max values, sums, averages and much more. This is great when you do paging as obviously you do not want to execute two queries: one for the pagination and another for the total reuslts count. Below are the available projections you can use: | Transform | Description | Example | | --------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | avg | The name of the property to avg or a list or array of property names | *withProjections(avg="salary")* | | count | The name of the property to count or a list or array of property names | *withProjections(count="comments")* | | countDistinct | The name of the property to count distinct or a list or array of property names | *withProjections(countDistinct="email")* | | distinct | The name of the property to do a distinct on, this can be a single property name a list or an array of property names | *withProjections(distinct="email")* | | groupProperty | The name of the property to group by or a list or array of property names | *withProjections(groupproperty="lastName")* | | max | The name of the property to max or a list or array of property names | *withProjections(max="lastLogin")* | | min | The name of the property to min or a list or array of property names | *withProjections(min="cid")* | | property | The name of the property to do a projected value on or a list or array of property names | *withProjections(property="firstname")* | | sum | The name of the property to sum or a list or array of property names | *withProjections(sum="balance")* | | rowCount | Do a row count on the criteria | *withProjections(rowcount=1)* | | id | Return the projected identifier value | *withProjections(id=1)* | | sqlProjection | Return projected value for sql fragment. Can accept a single config {sql,alias,property}, or an array of multiple configs. |

withProjections(sqlProjection={sql="SELECT count( ) from blog where Year < 2006 and Author={alias}.Author",
alias="BlogPosts",
property="Author" })\*

| | sqlGroupProjection | Return projected value for sql fragment with grouping. Can accept a single config( sql,alias,property,group}, or an array of multiple configs. |

withProjections(sqlGroupProjection={sql="SELECT count( ) from blog where Year < 2006 and Author={alias}.Author",
alias="BlogPosts",
property="Author",
group="Author" })\*

| | detachedSQLProjection | Creates a sqlProjection() based on Detached Criteria Builder | See [Detached Criteria Builder](https://github.com/ColdBox/cbox-cborm/wiki/ORM-Detached-Criteria-Builder) | You will use them by passing them to the *withProjections()* method as arguments that match the projection type. The value of the argument is one, a list or an array of property names to run the projection on, with the exception of id and rowcount which take a boolean true. Also, you can pass in a string separated with a : to denote an alias on the property when doing the SQL. The alias can then be used with any restriction the criteria builder can use. ```javascript Ex: avg="balance", avg="balance:myBalance", avg="balance, total", avg=["balance","total"] ``` > **INFO** If the :alias is not used, then the alias becomes the property name. ```javascript // Using native approach for one projection only var results = c.like("firstName","Lui%") .and( c.restrictions.between( "balance", 200, 300), c.restrictions.eq("department", "development") ) .setProjection( c.projections.rowCount() ) .get(); // Using the withProjections() method, which enables you to do more than 1 projection var results = c.like("firstName","Lui%") .and( c.restrictions.between( "balance", 200, 300), c.restrictions.eq("department", "development") ) .withProjections(rowCount=1) .get(); var results = c.like("firstName","Lui%") .and( c.restrictions.between( "balance", 200, 5000), c.restrictions.eq("department", "development") ) .withProjections(avg="balance,total",max="total:maxTotal") .gt("maxTotal",500) .list(); ``` --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://coldbox-orm.ortusbooks.com/v1.x/coldbox-criteria-builder/result-transformers.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.