Version: current

# Class SQLQuery

Object representing a SQL query. The various parts of the SQL query can be manipulated individually.

Caution: Only supports SELECT (default) and DELETE at the moment.

### Direct known subclasses

`SS_Report_FakeQuery`

Package: sapphire\model
Located at sapphire/core/model/SQLQuery.php

## Methods summary

`public` | # `__construct( array $select = "*", array $from = array(), array $where = "", string $orderby = "", array $groupby = "", array $having = "", string $limit = "" )` Construct a new SQLQuery. Construct a new SQLQuery. #### Parameters $select array $select An array of fields to select. $from array $from An array of join clauses. The first one should be just the table name. $where array $where An array of filters, to be inserted into the WHERE clause. $orderby string $orderby An ORDER BY clause. $groupby array $groupby An array of fields to group by. $having array $having An array of having clauses. $limit string $limit A LIMIT clause. TODO: perhaps we can quote things here instead of requiring all the parameters to be quoted by this stage.
`public `SQLQuery`` | # `select( mixed $fields )` Specify the list of columns to be selected by the query. Specify the list of columns to be selected by the query.
    // pass fields to select as single parameter array
    $query->select(array("Col1","Col2"))->from("MyTable");

    // pass fields to select as multiple parameters
    $query->select("Col1", "Col2")->from("MyTable");
#### Parameters $fields mixed $fields #### Returns ``SQLQuery`` SQLQuery
`public `SQLQuery`` | # `from( string $table )` Specify the target table to select from. Specify the target table to select from.
    $query->from("MyTable"); // SELECT * FROM MyTable
#### Parameters $table string $table #### Returns ``SQLQuery`` This instance
`public `SQLQuery`` | # `leftJoin( mixed $table, mixed $onPredicate )` Add a LEFT JOIN criteria to the FROM clause. Add a LEFT JOIN criteria to the FROM clause. #### Returns ``SQLQuery`` This instance
`public `SQLQuery`` | # `innerJoin( mixed $table, mixed $onPredicate )` Add an INNER JOIN criteria to the FROM clause. Add an INNER JOIN criteria to the FROM clause. #### Returns ``SQLQuery`` This instance
`public` | # `isJoinedTo( mixed $tableAlias )` Returns true if we are already joining to the given table alias Returns true if we are already joining to the given table alias
`public `SQLQuery`` | # `limit( string|array $limit )` Pass LIMIT clause either as SQL snippet or in array format. Pass LIMIT clause either as SQL snippet or in array format. #### Parameters $limit string|array $limit #### Returns ``SQLQuery`` This instance
`public `SQLQuery`` | # `orderby( string|array $orderby )` Pass ORDER BY clause either as SQL snippet or in array format. Pass ORDER BY clause either as SQL snippet or in array format. #### Parameters $orderby string|array $orderby #### Returns ``SQLQuery`` This instance
`public `SQLQuery`` | # `groupby( string|array $groupby )` Add a GROUP BY clause. Add a GROUP BY clause. #### Parameters $groupby string|array $groupby #### Returns ``SQLQuery`` SQLQuery
`public `SQLQuery`` | # `having( string|array $having )` Add a HAVING clause. Add a HAVING clause. #### Parameters $having string|array $having #### Returns ``SQLQuery`` SQLQuery
`public` | # `where( )` Apply a predicate filter to the where clause. Apply a predicate filter to the where clause. Accepts a variable length of arguments, which represent different ways of formatting a predicate in a where clause:
    // the entire predicate as a single string
    $query->where("Column = 'Value'");

    // an exact match predicate with a key value pair
    $query->where("Column", "Value");

    // a predicate with user defined operator
    $query->where("Column", "!=", "Value");
`public` | # `useDisjunction( )` Use the disjunctive operator 'OR' to join filter expressions in the WHERE clause. Use the disjunctive operator 'OR' to join filter expressions in the WHERE clause.
`public` | # `useConjunction( )` Use the conjunctive operator 'AND' to join filter expressions in the WHERE clause. Use the conjunctive operator 'AND' to join filter expressions in the WHERE clause.
`public` | # `renameTable( string $old, string $new )` Swap the use of one table with another. Swap the use of one table with another. #### Parameters $old string $old Name of the old table. $new string $new Name of the new table.
`public` | # `replaceText( string $old, string $new )` Swap some text in the SQL query with another. Swap some text in the SQL query with another. #### Parameters $old string $old The old text. $new string $new The new text.
`public string` | # `getFilter( )` Return an SQL WHERE clause to filter a SELECT query. Return an SQL WHERE clause to filter a SELECT query. #### Returns `string` string
`public string` | # `sql( )` Generate the SQL statement for this query. Generate the SQL statement for this query. #### Returns `string` string
`public string` | # `__toString( )` Return the generated SQL string for this query Return the generated SQL string for this query #### Returns `string` string
`public `SS_Query`` | # `execute( )` Execute this query. Execute this query. #### Returns ``SS_Query`` SS_Query
`public boolean` | # `filtersOnID( )` Checks whether this query is for a specific ID in a table Checks whether this query is for a specific ID in a table #### Returns `boolean` boolean
`public boolean` | # `filtersOnFK( )` Checks whether this query is filtering on a foreign key, ie finding a has_many relationship Checks whether this query is filtering on a foreign key, ie finding a has_many relationship #### Returns `boolean` boolean
`public integer` | # `unlimitedRowCount( mixed $column = null )` Return the number of rows in this query if the limit were removed. Useful in paged data sets. Return the number of rows in this query if the limit were removed. Useful in paged data sets. #### Returns `integer` int
`public` | # `canSortBy( mixed $fieldName )` Returns true if this query can be sorted by the given field. Note that the implementation of this method is a little crude at the moment, it wil return "false" more often that is strictly necessary. Returns true if this query can be sorted by the given field. Note that the implementation of this method is a little crude at the moment, it wil return "false" more often that is strictly necessary.

### Magic methods summary

## Properties summary

`public array` | $select | # An array of fields to select. An array of fields to select.
`public array` | $from | # An array of join clauses. The first one is just the table name. An array of join clauses. The first one is just the table name.
`public array` | $where | # An array of filters. An array of filters.
`public string` | $orderby | # An ORDER BY clause. An ORDER BY clause.
`public array` | $groupby | # An array of fields to group by. An array of fields to group by.
`public array` | $having | # An array of having clauses. An array of having clauses.
`public string` | $limit | # A limit clause. A limit clause.
`public boolean` | $distinct | # If this is true DISTINCT will be added to the SQL. If this is true DISTINCT will be added to the SQL.
`public boolean` | $delete | # If this is true, this statement will delete rather than select. If this is true, this statement will delete rather than select.
`public string` | $connective | # The logical connective used to join WHERE clauses. Defaults to AND. The logical connective used to join WHERE clauses. Defaults to AND.

[Raise a SilverStripe Framework issue/bug](https://github.com/silverstripe/silverstripe-framework/issues/new)
- [Raise a SilverStripe CMS issue/bug](https://github.com/silverstripe/silverstripe-cms/issues/new)
- Please use the Silverstripe Forums to ask development related questions. -