Skip to content

class Athena::ORM::NativeQuery
inherits Reference #

Executes a native SQL query, hydrating the rows it returns into entities as described by a Query::ResultSetMapping.

Native queries cover what the AORM::EntityRepository finders can't express, such as joins, subqueries, or vendor-specific SQL. The SQL is executed as written, so it is only as portable between database platforms as the SQL itself. They are created via AORM::EntityManager#create_native_query:

rsm = AORM::Query::ResultSetMapping.new
rsm.add_entity_result User, "u"
rsm.add_field_result "u", "id", "id"
rsm.add_field_result "u", "username", "username"

query = em.create_native_query <<-SQL, rsm
  SELECT u.id, u.username
  FROM users u
  JOIN avatars a ON a.id = u.avatar_id
  WHERE a.url = ?
SQL

query.set_parameter 1, "https://example.com/bob.png"

users = query.get_result.map &.as(User)

See Query::ResultSetMapping for how columns are mapped to entities and their associations.

Parameters#

Parameters are written as ? placeholders, and each is bound with #set_parameter, keyed by its 1-based position. The same placeholders work on every platform; they're rewritten to $1, $2, etc. on Postgres. Parameters are bound in position order, whatever order they were set in.

Like entity fields, each value is converted to its database representation through a AORM::Types::Type. The type's name may be passed explicitly, otherwise it is inferred from the value:

  • Int32 - integer
  • Int64 - bigint
  • Bool - boolean
  • Time - datetime, which converts the time to UTC

Values of any other type are bound without conversion.

query.set_parameter 1, "george"            # Bound as is
query.set_parameter 2, Time.local          # Converted through the `datetime` type
query.set_parameter 3, "secret", "my_type" # Converted through the custom type registered as `my_type`

Todo

Named parameters aren't supported yet. String keys aren't matched to :name placeholders; their values are bound in the order they were set.

Results#

Each executes the query when called. Results are typed as AORM::Entity, so cast them to the mapped entity class.

Hydrated entities are managed by the entity manager, so changes made to them are written on the next flush. Rows go through the identity map: if an entity with the same identifier is already managed, that instance is returned unchanged rather than being updated with the row's values.

Constructors#

.new(em : EntityManagerInterface, sql : String, rsm : Query::ResultSetMapping)#

Creates a query that executes sql against em's connection, hydrating its results according to rsm.

Prefer AORM::EntityManager#create_native_query.

View source

Methods#

#get_one_or_nil_result : Entity | ::Nil#

Executes the query and returns its only result, or nil if there are none.

Raises AORM::Exceptions::NonUniqueResult if there is more than one result.

View source

#get_result : Array(Entity)#

Executes the query and returns all of its results.

View source

#get_single_result : Entity#

Executes the query and returns its only result.

Raises AORM::Exceptions::NoResult if there are no results, or AORM::Exceptions::NonUniqueResult if there is more than one.

View source

#result_set_mapping : Query::ResultSetMapping#

Returns the mapping this query hydrates its results with.

View source

#set_parameter(key : String | Int32, value : DB::Any, type : String | Nil = nil) : self#

Sets the value of the parameter at the 1-based position key, converted through the Types::Type named type when bound.

Without a type, one is inferred from value. See Parameters.

View source

#sql : String#

Returns the SQL this query executes.

View source