This bugtracker is archived (announcement). New tickets are created on github.com. See all framework issues, cms issues, and search the module listings for more specific bugtrackers.
wiki:development/NewDataMapper
Last modified 6 years ago Last modified on 18/09/08 23:05:33

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
  • 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/