This development plan covers the creation of a new data mapper, or query generator, for SilverStripe?.
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.
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...
- 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
- Inner join on a simple A = B expression
- Single field, ASC/DESC
- Sort by an aggregate function, MAX(PostList?.Created) DESC
- DataObject::get_one("BlogHolder?") - return a "singleton" database record
- 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)
- All matching criteria
- 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
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') )
Selecting limited columns
get('BlogEntry')->columns('ID', 'Title') get('BlogEntry')->distinctColumns('AuthorID')->asArray()
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))");
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/