Complex queries and DQL

Although the EntityRepository provides some simple methods for querying objects remotely and on already loaded objects (loadOneBy, loadBy, findOneBy, findBy) there will often be situations where you want to select objects through more complex criteria.

###DQL Doctrine 2, the technology that Flextrine is based on, provides a powerful query language called DQL (Doctrine Query Language). DQL is similar to SQL, but is based around objects and attributes instead of rows and columns. The documentation for DQL can be found here.

When refering to entity classes DQL expects the entities to be specified using PHP5.3 namespace notation - for example, vo.User needs to be specified as vo\User. Flextrine provides a utility function to perform this conversion for you called QueryUtil.getDQLClass which you should use whenever you need to specify a fully qualified class name in a DQL query (e.g. in the FROM field).

var dqlString:String = "SELECT d FROM " + QueryUtil.getDQLClass(Doctor) + " d";

Selecting entities

You can run DQL select queries using the select or selectOne methods in EntityManager. For example, the following query (taken from the DQL documentation) will select all users with an age greater than 20.

em.select(new Query("SELECT u FROM " + QueryUtil.getDQLClass(User) + " u WHERE u.age > :age", { age: 20 } )).addResponder(new AsyncResponder(onResult, onFault));

private function onResult(result:Object, token:Object):void {
  // 'result' contains an array of User entities
  for each (var user:User in result)
    trace(user);
}

private function onFault(fault:Object, token:Object):void {
  throw new Error("An error occurred during select: " + fault);
}

As well as returning the result through the AsyncToken responder, the selected entities are also added to the appropriate repositories.

selectOne only returns a single entity result. If the query returns more than one entity only the first is returned and the rest are discarded before being sent to Flextrine.

em.selectOne(new Query("SELECT u FROM " + QueryUtil.getDQLClass(User) + " u WHERE u.username=:username AND u.password=:password", { username: "dave", password: "password" } )).addResponder(new AsyncResponder(onResult, onFault));

private function onResult(result:Object, token:Object):void {
  if (result) {
    trace("User authenticated!");
  } else {
    trace("Username/password not found");
  }
}

private function onFault(fault:Object, token:Object):void {
  throw new Error("An error occurred during selectOne: " + fault);
}

As with all operations that load entities from the server, Flextrine takes care to ensure that there is only a single instance of each entity in its repositories. This means that if you perform a selectOne that returns a User object, and the User object already exists in the User EntityRepository the User that is returned in the result paramater of onResult(result:Object, token:Object) is the same instance that was already in the repository. If any of its attributes have changed since it was last loaded these will be merged into the existing object, possibly triggering databinding on any components that are viewing this entity.

Updating and deleting entities

DQL updates and deletes are not currently supported by Flextrine as changes they make are unable to be reflected in the client side repositories. This restriction may be relaxed in a future version of Flextrine. If you absolutely need to do DQL updates/deletes then you should use a custom PHP function to run the query directly in PHP, and be aware that the changes will not be reflected in the client unless you manually refresh from the server.

Scalar queries

As of v0.9 Flextrine support the selection of alternative hydration modes when creating a Query. The most common use case for these hydration modes is to execute a query that returns a scalar value instead of entities. Hydration modes are selected using the setHydrationMode method of the Query class.

var query:Query = new Query("SELECT COUNT(d) FROM " + QueryUtil.getDQLClass(Doctor) + " d");
query.setHydrationMode(Query.HYDRATE_SINGLE_SCALAR);
em.select(query).addResponder(new AsyncResponder(function(e:ResultEvent, token:Object):void {
    trace("There are " + e.result + " doctors");
}, null));

See the DQL documentation for more details.

Server side queries

As of v1.0 RC1 Flextrine also supports server side queries using em.select. This is useful for times when you don't want to pass DQL from the client, but instead want to generate a query directly on the server. This is more secure, allows you to use information from the session in your query and also allows you to execute other queries in order to construct the main query.

In order to run a server-side query, rather than a client-side query merely create the Query object in AS3 with the name of a server-side method. For example:

em.select(new Query("getAllPatients", { minimumAge: 25 } ));

This will look for a method in FlextrineService.php called query_getAllDoctors (note that query_ is prefixed to the method name to prevent against the possibility of arbitrary methods being invoked). Also note that this method should be protected, not public. Any parameters passed from AS3 in the Query object will be passed into this method for you to use as you like. The return value of the query_ method should be a constructed Doctrine Query which Flextrine will then execute and return.

protected function query_getAllPatients($params) {
  $dql = 'SELECT p FROM vo\Patient p WHERE p.age > :minimumAge';
  $query = $this->em->createQuery($dql);
  $query->setParameter("minimumAge", $params->minimumAge);
  return $query;
}

Note that this also gives you the ability to use the Doctrine Query Builder to construct queries that can then be run from AS3.