This development plan covers the creation of a new data mapper, or query generator, for SilverStripe?.

General idea

Replace this:

DataObject::get("Order" "Name = 'Sam'", "Created DESC");

With something like one of these:

DataObject::get("Order")->whereEquals("Name","Sam")->sort("Created", "desc");
data()->Order->where("Name", "=", "Sam")->orderBy("Created", "desc");
$this->data->Order->...
data('Order')->...

The way we will archive this is as follows:

• The data requester method, instead of returning a DataObjectSet? that contains an array of items, will return a DataObjectSet? that simply contains a reference to the query that should be executed.
• Those objects will have a bunch of methods available for manipulating the query before it gets executed.
• Optionally, the base objects will be returned by an object that is responsible for dishing out database results, rather than static methods.

Query Survey

In this section, we will collate a big list of all the different kinds of queries that are executed in the SilverStripe? core, and maybe representative projects and modules too...

• Selections
  • Record list (DO::get)
  • Single record (DO::get_one / DO::get_by_id)
• Get by ID
  • get one by id
  • Get multiple by an id list
• Combinations of filters
  • Get a number of records with a simple conjunctive filter "A = B AND C = D AND E > F"
  • Simple disjunctive filter A = B OR C = D OR E = F; of course, the ClassName? IN (A,B,C) filter is added conjunctively to this.
• Filter types
  • A =,!=,<,> B
  • A BETWEEN C AND D
  • A IN,NOT IN (B,C,D,E)
  • MONTH(A) = B, YEAR(C) = D
  • A LIKE '%B%', C LIKE '%C', D LIKE 'E%'
  • A >,< NOW
  • A >,< NOW +,- INTERVAL C D
  • A IS,IS NOT NULL
  • A REGEXP B
• Joins
  • Inner join on a simple A = B expression
• Limits
  • Pagination
• Sorting
  • Single field, ASC/DESC
  • Sort by an aggregate function, MAX(PostList?.Created) DESC
• Misc
  • DataObject::get_one("BlogHolder?") - return a "singleton" database record

DB::query queries

• Selection
  • Limited the columns, with a DISTINCT filter (eg, get the unique months and years of BlogEntries?)
  • Select calculated values, eg, DATE_FORMAT
  • Get a count
  • Get a grouped count - SELECT COUNT(DISTINCT AuthorID)
  • Get aggregate values SELECT max(ID), max(Created)
• Delete
  • All matching criteria
• Update
  • Field = other field, single record
  • Data migration - get ForumRole? having data loaded from ForumMember?
• Misc
  • Check for table existence
  • Field information
• Querying data not covered by the object model (_obsolete_File)
• Avoiding onWrite and onDelete handlers (File)
• Grouped select with some aggregate values and some non-aggregate values
• "Having" filters, on aggregate values

Tentative Spec / Examples

This is really just a place for sketching ideas.

Get a record by ID

get('SiteTree')->byId(3);

Multiple conjunctive (AND) filters

get('Post')->equals('ParentID', 5)->afterDateInPast('Created', '15 mins')->orderBy('Created','DESC');

Or, a Django-like syntax for building filters with an array

get('Post')->filter(array('ParentID' => 5, 'Created.Ago.LessThan' => '15 mins'))->orderBy('Created','DESC');

We could embed SearchContext?'s famous dot-syntax in here, too:

get('Post')->filter(array('Parent.ID' => 5, 'Author.FirstName' => 'Sam'))->orderBy('Created','DESC');

Disjunctive (OR) filters - note that these are rare and so clumsiness is less of an issue.

get('Member')->or( filter()->equals('Name','Sam')->equals('Name', 'Ingo') )

Deleting

get('Member')->byId(3)->delete();
get('Post')->equals('TopicID',5)->deleteAll();

Counting records

get('BlogEntry')->equals('AuthorID', 5)->count();

Selecting limited columns

get('BlogEntry')->columns('ID', 'Title')
get('BlogEntry')->distinctColumns('AuthorID')->asArray()

Output formats

get('BlogEntry')->columns('ID', 'Title')->asMaps() # Returns an array of maps
get('BlogEntry')->columns('ID', 'Title')->asArray() # Returns the first column as an array
get('BlogEntry')->distinctColumns('AuthorID')->First()->asMap() # Returns the first record as a map
get('BlogEntry')->distinctColumns('AuthorID')->asValue() # Returns

More complex queries - you have to peer behind the curtain

get('BlogEntry')->where("ID = 3 AND Title > str_repeat('@',MIN(ID, ParentID))");

References

The Django query engine seems to be the closest to achieving what we are trying to achieve:  http://docs.djangoproject.com/en/dev/topics/db/queries/