Download Plugin (zip)

Stats

Version

0.1.2

Wheels Compatibility

1.1.5, 1.1.4, 1.1.3, 1.1.2, 1.1.1, 1.1

Downloads

38

Last Updated

September 30, 2011

About Plugins

Plugins allow you to extend or modify default Wheels application behavior. To use, you just drop the zip file into your plugins directory and reload your application.

Read Using and Creating Plugins for more information.

CFRel

Relational algebra query engine for CFWheels.

Author

Don Humphreys

Project Home

https://github.com/dhumphreys/cfrel

Description

CFRel is a query engine for writing SELECT statements using relational algebra. This means that you can build and execute queries against your models using a chained, object-oriented syntax.

It is recommended to change all of your querying over to the new syntax, but all CFWheels findOne(), findAll(), aggregates, and dynamic finders will automatically use CFRel as the query writing engine.

Note: This does not replace any queries executed as creates, updates, or deletes. Also, calculated properties are not included by default by CFRel to avoid issues with aggregate calculated properties. Options may be available in the future to switch this on and off.

Usage/Examples

Below is an example using CFRel to query a model:

// grab records 21-40 of items with sales added up items = model("item") .select("id,name") .select("SUM(itemSales) AS salesTotal") .include("itemSales", [], "outer") .where("name LIKE ? AND createdAt > ?", ["D%", DateAdd(Now(), "m", 6)]) .group("id,name") .paginate(2, 20);

The "items" variable now contains a Relation object, which has many methods available to improve your querying experience. One important note is that even though I have done a lot to this object, a query has not been executed yet. Queries are lazily executed a variety of ways right when you request data from the Relation:

// execute, but don't return anything items.exec(); // get a recordset back from the object recordset = items.query(); // get coldfusion result data from the object result = items.result(); // loop over the model objects represented by the relation while (items.next()) writeDump(items.curr()); // grab entire array of result structures or model objects itemStructs = items.structs(); itemModels = items.objects(); // perform an in memory sub-query (or query-of-query) itemRecordset = items.qoq().where(id=546).query(); // duplicate the relation, modify it, and execute against db again
// NOTE: we are dealing with a new relation after calling .qoq() or .clone() items2 = items.clone().having("salesTotal > ?", [3000]).query();

There are many ways to use CFRel to improve your query logic. Please refer to the GitHub page wiki for more information, or email me if you have any trouble.

Change Log

Version 0.1.2—September 30, 2011 Download

  • Fixed column mappings for sql BETWEEN operations
  • Fixed broken afterFind logic for relations with no model
  • Wheels compatibility: 1.1, 1.1.1, 1.1.2, 1.1.3, 1.1.4, 1.1.5.

Version 0.1.1—August 22, 2011 Download

  • Allow model instances to be created from QoQ relations
  • Allow findAll() to use returnAs="relation" with any query
  • Wheels compatibility: 1.1, 1.1.1, 1.1.2, 1.1.3, 1.1.4, 1.1.5.

Version 0.1—August 16, 2011 Download

  • Initial release.
  • Wheels compatibility: 1.1, 1.1.1, 1.1.2, 1.1.3, 1.1.4, 1.1.5.