Connect the Silverstripe Datalist to the left with the where clause
An object representing a SQL select query, which can be serialized into a SQL statement. It is easier to deal with object wrappers than string parsing a raw SQL query. This object is used internally by the SilverStripe ORM.
Dealing with low-level SQL is not encouraged, since the ORM provides powerful abstraction APIs (see datamodel). Starting with SilverStripe 3, records in collections are lazy loaded, and these collections have the ability to run efficient SQL such as counts or returning a single column.
For example, if you want to run a simple SQL statement, the following three statements are functionally equivalent:
If you do use raw SQL, you'll run the risk of breaking various assumptions the ORM and code based on it have:
- Custom getters / setters (object property can differ from database column)
- DataObject hooks like and
- Automatic casting
- Default values set through objects
- Database abstraction
We'll explain some ways to use SELECT with the full power of SQL, but still maintain a connection to the ORM where possible.
Selection can be done by creating an instance of, which allows management of all elements of a SQL SELECT query, including columns, joined tables, conditional filters, grouping, limiting, and sorting.
The result of is an array lightly wrapped in a database-specific subclass of query. This class implements the Iterator-interface, and provides convenience methods for accessing the data.
Deletion can be done either by calling / directly, by creating an object, or by transforming a into an object instead.
For example, creating an object:
Alternatively, turning an existing into a delete:
Directly querying the database:
INSERT / UPDATE #
INSERT and UPDATE can be performed using the and classes. These both have similar aspects in that they can modify content in the database, but each are different in the way in which they behave.
Previously, similar operations could be performed by using the function which would build the INSERT and UPDATE queries on the fly. This method still exists, but internally uses /, although the actual query construction is now done by the object.
Each of these classes implement the interface, noting that each accepts write key / value pairs in a number of similar ways. These include the following API methods:
- - Takes a list of assignments as an associative array of key -> value pairs, but also supports SQL expressions as values if necessary
- - Replaces all existing assignments with the specified list
- - Returns all currently given assignments, as an associative array in the format
- - Singular form of addAssignments, but only assigns a single column value
- - Assigns a column the value of a specified SQL expression without parameters is shorthand for
SQLUpdate also includes the following API methods:
- - Clears all assignments
- - Gets the table to update
- - Sets the table to update (this should be ANSI-quoted) e.g.
SQLInsert also includes the following API methods:
- - Clears all rows
- - Clears all assignments on the current row
- - Adds another row of assignments, and sets the current row to the new row
- - Adds a number of arrays, each representing a list of assignment rows, and sets the current row to the last one
- - Gets the names of all distinct columns assigned
- - Gets the table to insert into
- - Sets the table to insert into (this should be ANSI-quoted), e.g.
In addition to assigning values, the SQLInsert object also supports multi-row inserts. For database connectors and API that don't have multi-row insert support these are translated internally as multiple single row inserts.
Value checks #
Raw SQL is handy for performance-optimized calls, e.g. when you want a single column rather than a full-blown object representation.
Example: Get the count from a relationship.
Note that in the ORM, this call would be executed in an efficient manner as well:
Creates a map based on the first two columns of the query result. This can be useful for creating dropdowns.
Example: Show player names with their birth year, but set their birth dates as values.
Note that going through SQLSelect is just necessary here because of the custom SQL value transformation (). An alternative approach would be a custom getter in the object definition:
Data types #
As of SilverStripe 4.4, the following PHP types will be used to return database content:
- booleans will be an integer 1 or 0, to ensure consistency with MySQL that doesn't have native booleans
- integer types returned as integers
- floating point / decimal types returned as floats
- strings returned as strings
- dates / datetimes returned as strings
Up until SilverStripe 4.3, bugs meant that strings were used for every column type.
Related Lessons #
Related Documentation #
API Documentation #
- Wholesale caretaker supply voucher
- How to register online for free with illuminati
- How to open mp4 part files player
- WhatsApp op iPhone 5 Zonder credit card
- Robin Roberts bracelet how to get one
- How to hatch eggs in Minecraft
- What is Movistar on my Samsung
- What does non-bookable article summary mean
- What is adolf dassler able to do
- Greg Howe turning the pedal
- T-shirt printing machine wholesale under $ 200
- Ford Transit does not start at the hotel
- How to play stratego junior
- How to transfer an MSDN subscription option
- Highlands Howies Suamico Wisconsin Shooting
- What is Endorsement 3083a
- Who owns the dairy goat farm
- Minecraft how to find dungeons with f3
- Questionnaire on the entire healing system and the modality
- How to hunt pigs in nc
- Kayla Marie Howran protests against the hero
- Horchow Outlet locations in Texas
- Wholesale craft supplies South Africa
- Haproxy configuration in Ubuntu, how to delete