Db.Query.Mysql Class
Class implements MySQL query
Constructor
Db.Query.Mysql
-
mysql -
type -
query -
parameters -
table
Parameters:
-
mysqlObjectDb.Mysql
-
typeNumberOne of the TYPE_* constants in Db.Query
-
queryString | ObjectA sql query (for raw queries) or an associative array of clauses
-
parametersObject | ArrayThe parameters to add to the query right away (to be bound when executing). Values corresponding to numeric keys replace question marks, while values corresponding to string keys replace ":key" placeholders, in the SQL.
-
tableStringThe table operated with query
Item Index
Methods
Events
Methods
after
-
after -
clause
Inserts a custom clause after a particular clause
Parameters:
Returns:
The resulting Db.Query object
andWhere
-
criteria -
or_criteria
Adds to the WHERE clause, like this: " ... AND (x OR y OR z)", where x, y and z are the arguments to this function.
Parameters:
-
criteriaDb.Expression | Object | StringAn associative array of expression: value pairs. The values are automatically turned into placeholders to be escaped later. They can also be arrays, in which case they are placed into an expression of the form "key IN ('val1', 'val2')" Or, this could be a Db.Expression object.
-
or_criteriaDb.Expression | Object | StringYou can have any number of these, including zero.
Returns:
The resulting Db.Query object
begin
-
[lockType='FOR
Begins a transaction right before executing this query. The reason this method is part of the query class is because you often need the "where" clauses to figure out which database to send it to, if sharding is being used.
Parameters:
-
[lockType='FORStringUPDATE'] Defaults to 'FOR UPDATE', but can also be 'LOCK IN SHARE MODE', or set it to null to avoid adding a "LOCK" clause
build
-
[options={}]
Builds the query from the clauses
Parameters:
-
[options={}]Object optional
Returns:
commit
()
Db.Query.Mysql
chainable
Commits transaction when query is executed Use only with MySQL.
Returns:
The resulting Db.Query object
execute
-
callback -
[options={}]
Executes the query against a database connection Connects to one or more shard(s) as necessary.
Parameters:
-
callbackFunctionThis function is called when the queries have all completed. It is passed the following arguments:
- errors: an Object. If there were any errors, it will contain shardName: error pairs
- results: an array of results merged from all the shards (for SELECT queries) for INSERT queries results contains the value of LAST_INSERT_ID()
- fields: an array of fields merged from all the shards (for SELECT queries) It is passed an object whose keys are the shard names and the values are arrays of [err, results, fields] as returned from the mysql module.
-
[options={}]Object optionalYou can override the following options:
-
[plain=false]Boolean optionalIf true, returns array of plain object instead of Db.Row instances
-
[raw=false]Boolean optionalIf true, or if the query type is Db.Query.TYPE_RAW, the callback will be passed an object of pairs representing the results returned from the mysql query on each shard. Note that the results array will contain raw objects of the form "{fieldName: fieldValue};", and not objects which have Db.Row mixed in.
-
[shards]String | Array | Object optionalThis option will bypass the usual sharding calculations. You can pass a string here, which will be used to run the query on this shard. Or pass an array of shard names. Or you can specify custom query objects as {shardName: query}.
-
getSQL
-
callback -
[shardName='']
Gets the SQL that would be executed with the execute() method.
Parameters:
groupBy
-
expression
Adds a GROUP BY clause to a query
Parameters:
-
expressionDb.Expression | StringA string or Db.Expression with the expression to group the results by.
Returns:
The resulting Db.Query object
having
-
criteria
Adds a HAVING clause to a query
Parameters:
-
criteriaDb.Expression | Object | StringAn associative array of expression => value pairs. The values are automatically escaped using PDO placeholders. Or, this could be a Db.Expression object.
Returns:
The resulting Db.Query object
join
-
table -
condition -
[join_type='INNER']
Joins another table to use in the query
Parameters:
-
tableStringThe name of the table. May also be "name AS alias".
-
conditionDb.Expression | Object | StringThe condition to join on. Thus, JOIN table ON (condition)
-
[join_type='INNER']String optionalThe string to prepend to JOIN, such as 'INNER', 'LEFT OUTER', etc.
Returns:
The resulting Db.Query object
limit
-
limit -
[offset=0]
Adds optional LIMIT and OFFSET clauses to the query
Parameters:
Returns:
The resulting Db.Query object
lock
-
[type='FOR
Works with SELECT queries to lock the selected rows. Use only with MySQL.
Parameters:
-
[type='FORStringUPDATE'] Defaults to 'FOR UPDATE', but can also be 'LOCK IN SHARE MODE'
Returns:
The resulting Db.Query object
onDuplicateKeyUpdate
-
updates
Adds an ON DUPLICATE KEY UPDATE clause to an INSERT statement. Use only with MySQL.
Parameters:
-
updatesObjectAn associative array of {column: value} pairs. The values are automatically escaped using PDO placeholders.
Returns:
The resulting Db.Query object
options
-
options
This function provides an easy way to provide additional clauses to the query.
Parameters:
-
optionsObjectAn associative array of {key: value} pairs, where the key is the name of the method to call, and the value is the array of arguments. If the value is not an array, it is wrapped in one.
Returns:
The resulting Db.Query object
orderBy
-
expression -
[ascending=false]
Adds a ORDER BY clause to a query
Parameters:
-
expressionDb.Expression | StringA string or Db.Expression with the expression to order the results by.
-
[ascending=false]Object optionalBoolean If false, sorts results as ascending, otherwise descending.
Returns:
The resulting Db.Query object
orWhere
-
criteria -
and_criteria
Adds to the WHERE clause, like this: " ... OR (x AND y AND z)", where x, y and z are the arguments to this function.
Parameters:
-
criteriaDb.Expression | Object | StringAn associative array of expression: value pairs. The values are automatically turned into placeholders to be escaped later. They can also be arrays, in which case they are placed into an expression of the form "key IN ('val1', 'val2')" Or, this could be a Db.Expression object.
-
and_criteriaDb.Expression | Object | String
Returns:
The resulting Db.Query object
reallyConnect
-
callback -
[shardName=''] -
modifications={}
Create mysql.Connection and connects to the database table
rollback
-
[criteria=null]
Rolls back transaction when query is executed Use only with MySQL.
Parameters:
-
[criteria=null]String optionalPass this to target the rollback to the right shard.
Returns:
The resulting Db.Query object
SELECT
-
fields -
tables -
[repeat=false]
Creates a query to select fields from one or more tables.
Parameters:
-
fieldsString | ObjectThe fields as strings, or associative array of {alias: field};
-
tablesString | ObjectThe tables as strings, or associative array of {alias: table};
-
[repeat=false]Boolean optionalIf tables is an array, and select() has already been called with the exact table name and alias as one of the tables in that array, then this table is not appended to the tables list if repeat is false. Otherwise it is. This is really just for using in your hooks.
Returns:
The resulting query object.
set
-
updates
Adds a SET clause to an UPDATE statement
Parameters:
-
updatesObjectAn associative array of column: value pairs. The values are automatically escaped using PDO placeholders.
Returns:
The resulting Db.Query object
where
-
criteria
Adds a WHERE clause to a query
Parameters:
-
criteriaDb.Expression | Object | StringAn associative array of expression: value pairs. The values are automatically turned into placeholders to be escaped later. They can also be arrays, in which case they are placed into an expression of the form "key IN ('val1', 'val2')" Or, this could be a Db.Expression object.
Returns:
The resulting Db.Query
Properties
Events
error
Database error
Event Payload:
-
errorErrorThe error object
-
mqDb.Query.MysqlDb.Query.Mysql object which caused an error