CollectionFind Class Reference

Handler for document selection on a Collection. More...

Methods
CollectionFind	find (str searchCondition)
	Sets the search condition to identify the Documents to be retrieved from the owner Collection. More...
CollectionFind	fields (str fieldDefinition[, str fieldDefinition,...])
	Sets the fields to be retrieved from each document matching the criteria on this find operation. More...
CollectionFind	group_by (list fields)
	Sets a grouping criteria for the resultset. More...
CollectionFind	having (str condition)
	Sets a condition for records to be considered in aggregate function operations. More...
CollectionFind	sort (list sortCriteria)
	Sets the sorting criteria to be used on the DocResult. More...
CollectionFind	limit (int numberOfDocs)
	Sets the maximum number of documents to be returned by the operation. More...
CollectionFind	skip (int numberOfDocs)
	Sets number of documents to skip on the resultset when a limit has been defined. More...
CollectionFind	offset (int numberOfDocs)
	Sets number of documents to skip on the resultset when a limit has been defined. More...
CollectionFind	lock_shared (str lockContention)
	Instructs the server to acquire shared row locks in documents matched by this find operation. More...
CollectionFind	lock_exclusive (str lockContention)
	Instructs the server to acquire an exclusive lock on documents matched by this find operation. More...
CollectionFind	bind (str name, Value value)
	Binds a value to a specific placeholder used on this CollectionFind object. More...
DocResult	execute ()
	Executes the find operation with all the configured options. More...

Detailed Description

Handler for document selection on a Collection.

This object provides the necessary functions to allow selecting document data from a collection.

This object should only be created by calling the find function on the collection object from which the documents will be retrieved.

See also

Collection

Member Function Documentation

find()

CollectionFind find

(

str

searchCondition

)

Sets the search condition to identify the Documents to be retrieved from the owner Collection.

Parameters

searchCondition

Optional String expression defining the condition to be used on the selection.

Returns

This CollectionFind object.

Sets the search condition to identify the Documents to be retrieved from the owner Collection. If the search condition is not specified the find operation will be executed over all the documents in the collection.

The search condition supports parameter binding.

Method Chaining

After this function invocation, the following functions can be invoked:

• fields(str fieldDefinition[, str fieldDefinition, ...]), fields(list fieldDefinition), fields(DocExpression fieldDefinition)
• group_by(list fields), group_by(str field[, str field, ...])
• sort(list sortCriteria), sort(str sortCriterion[, str sortCriterion, ...])
• limit(int numberOfDocs)
• lock_shared(str lockContention)
• lock_exclusive(str lockContention)
• bind(str name, Value value)
• execute()

See also

Usage examples at execute().

fields()

CollectionFind fields

(

str

fieldDefinition[, str fieldDefinition,...]

)

Sets the fields to be retrieved from each document matching the criteria on this find operation.

Parameters

fieldDefinition

Definition of the fields to be retrieved.

Returns

This CollectionFind object.

This function sets the fields to be retrieved from each document matching the criteria on this find operation.

A field is defined as a string value containing an expression defining the field to be retrieved.

The fields to be retrieved can be set using any of the next methods:

• Passing each field definition as an individual string parameter.
• Passing a list of strings containing the field definitions.
• Passing a JSON expression representing a document projection to be generated.

Method Chaining

This function can be invoked only once after:

• find(str searchCondition) After this function invocation, the following functions can be invoked:
• group_by(list fields), group_by(str field[, str field, ...])
• sort(list sortCriteria), sort(str sortCriterion[, str sortCriterion, ...])
• limit(int numberOfDocs)
• lock_shared(str lockContention)
• lock_exclusive(str lockContention)
• bind(str name, Value value)
• execute()

See also

Usage examples at execute().

group_by()

CollectionFind group_by

(

list

fields

)

Sets a grouping criteria for the resultset.

Returns

This CollectionFind object.

Sets a grouping criteria for the resultset.

Method Chaining

This function can be invoked only once after:

• find(str searchCondition)
• fields(str fieldDefinition[, str fieldDefinition, ...]), fields(list fieldDefinition), fields(DocExpression fieldDefinition) After this function invocation the following functions can be invoked:
• having(str condition)
• sort(list sortCriteria), sort(str sortCriterion[, str sortCriterion, ...])
• limit(int numberOfDocs)
• lock_shared(str lockContention)
• lock_exclusive(str lockContention)
• bind(str name, Value value)
• execute()

See also

Usage examples at execute().

having()

CollectionFind having

(

str

condition

)

Sets a condition for records to be considered in aggregate function operations.

Parameters

condition

A condition on the aggregate functions used on the grouping criteria.

Returns

This CollectionFind object.

Sets a condition for records to be considered in aggregate function operations.

Method Chaining

This function can be invoked only once after:

• group_by(list fields), group_by(str field[, str field, ...]) After this function invocation, the following functions can be invoked:
• sort(list sortCriteria), sort(str sortCriterion[, str sortCriterion, ...])
• limit(int numberOfDocs)
• lock_shared(str lockContention)
• lock_exclusive(str lockContention)
• bind(str name, Value value)
• execute()

See also

Usage examples at execute().

sort()

CollectionFind sort

(

list

sortCriteria

)

Sets the sorting criteria to be used on the DocResult.

Returns

This CollectionFind object.

If used, the CollectionFind operation will return the records sorted with the defined criteria.

Every defined sort criterion follows the format:

name [ ASC | DESC ]

ASC is used by default if the sort order is not specified.

Method Chaining

This function can be invoked only once after:

• find(str searchCondition)
• fields(str fieldDefinition[, str fieldDefinition, ...]), fields(list fieldDefinition), fields(DocExpression fieldDefinition)
• group_by(list fields), group_by(str field[, str field, ...])
• having(str condition) After this function invocation, the following functions can be invoked:
• limit(int numberOfDocs)
• lock_shared(str lockContention)
• lock_exclusive(str lockContention)
• bind(str name, Value value)
• execute()

See also

Usage examples at execute().

limit()

CollectionFind limit

(

int

numberOfDocs

)

Sets the maximum number of documents to be returned by the operation.

Parameters

numberOfDocs

The maximum number of documents to be retrieved.

Returns

This CollectionFind object.

If used, the operation will return at most numberOfDocs documents.

Method Chaining

This function can be invoked only once after:

• find(str searchCondition)
• fields(str fieldDefinition[, str fieldDefinition, ...]), fields(list fieldDefinition), fields(DocExpression fieldDefinition)
• group_by(list fields), group_by(str field[, str field, ...])
• having(str condition)
• sort(list sortCriteria), sort(str sortCriterion[, str sortCriterion, ...]) This function can be called every time the statement is executed.

After this function invocation, the following functions can be invoked:

• offset(int numberOfDocs)
• lock_shared(str lockContention)
• lock_exclusive(str lockContention)
• bind(str name, Value value)
• execute()

See also

Usage examples at execute().

skip()

CollectionFind skip

(

int

numberOfDocs

)

Sets number of documents to skip on the resultset when a limit has been defined.

Parameters

numberOfDocs

The number of documents to skip before start including them on the DocResult.

Returns

This CollectionFind object.

If used, the first numberOfDocs records will not be included on the result.

Attention

This function will be removed in a future release, use the offset() function instead.

Method Chaining

This function can be invoked only once after:

• limit(int numberOfDocs) After this function invocation, the following functions can be invoked:
• lock_shared(str lockContention)
• lock_exclusive(str lockContention)
• bind(str name, Value value)
• execute()

See also

Usage examples at execute().

offset()

CollectionFind offset

(

int

quantity

)

Sets number of documents to skip on the resultset when a limit has been defined.

Parameters

quantity

The number of documents to skip before start including them on the DocResult.

Returns

This CollectionFind object.

If used, the first quantity records will not be included on the result.

Method Chaining

This function can be invoked only once after:

• limit(int numberOfDocs) After this function invocation, the following functions can be invoked:
• lock_shared(str lockContention)
• lock_exclusive(str lockContention)
• bind(str name, Value value)
• execute()

See also

Usage examples at execute().

lock_shared()

CollectionFind lock_shared

(

str

lockContention

)

Instructs the server to acquire shared row locks in documents matched by this find operation.

Parameters

lockContention

optional parameter to indicate how to handle documents that are already locked.

Returns

This CollectionFind object.

When this function is called, the selected documents will be locked for write operations, they may be retrieved on a different session, but no updates will be allowed.

The acquired locks will be released when the current transaction is committed or rolled back.

The lockContention parameter defines the behavior of the operation if another session contains an exclusive lock to matching documents.

The lockContention can be specified using the following constants:

• mysqlx.LockContention.DEFAULT
• mysqlx.LockContention.NOWAIT
• mysqlx.LockContention.SKIP_LOCKED

The lockContention can also be specified using the following string literals (no case sensitive):

• 'DEFAULT'
• 'NOWAIT'
• 'SKIP_LOCKED'

If no lockContention or the default is specified, the operation will block if another session already holds an exclusive lock on matching documents until the lock is released.

If lockContention is set to NOWAIT and another session already holds an exclusive lock on matching documents, the operation will not block and an error will be generated.

If lockContention is set to SKIP_LOCKED and another session already holds an exclusive lock on matching documents, the operation will not block and will return only those documents not having an exclusive lock.

This operation only makes sense within a transaction.

Method Chaining

This function can be invoked at any time before bind() or execute() are called.

After this function invocation, the following functions can be invoked:

• lock_exclusive(str lockContention)
• bind(str name, Value value)
• execute()

If lock_exclusive() is called, it will override the lock type to be used on the selected documents.

See also

Usage examples at execute().

lock_exclusive()

CollectionFind lock_exclusive

(

str

lockContention

)

Instructs the server to acquire an exclusive lock on documents matched by this find operation.

Parameters

lockContention

optional parameter to indicate how to handle documents that are already locked.

Returns

This CollectionFind object.

When this function is called, the selected documents will be locked for read operations, they will not be retrievable by other session.

The acquired locks will be released when the current transaction is committed or rolled back.

The lockContention parameter defines the behavior of the operation if another session contains a lock to matching documents.

The lockContention can be specified using the following constants:

• mysqlx.LockContention.DEFAULT
• mysqlx.LockContention.NOWAIT
• mysqlx.LockContention.SKIP_LOCKED

The lockContention can also be specified using the following string literals (no case sensitive):

• 'DEFAULT'
• 'NOWAIT'
• 'SKIP_LOCKED'

If no lockContention or the default is specified, the operation will block if another session already holds a lock on matching documents.

If lockContention is set to NOWAIT and another session already holds a lock on matching documents, the operation will not block and an error will be generated.

If lockContention is set to SKIP_LOCKED and another session already holds a lock on matching documents, the operation will not block and will return only those documents not having a lock.

This operation only makes sense within a transaction.

Method Chaining

This function can be invoked at any time before bind() or execute() are called.

After this function invocation, the following functions can be invoked:

• lock_shared(str lockContention)
• bind(str name, Value value)
• execute()

If lock_shared() is called, it will override the lock type to be used on the selected documents.

See also

Usage examples at execute().

bind()

CollectionFind bind	(	str	name,
		Value	value
	)

Binds a value to a specific placeholder used on this CollectionFind object.

Parameters

name	The name of the placeholder to which the value will be bound.
value	The value to be bound on the placeholder.

Returns

This CollectionFind object.

Binds the given value to the placeholder with the specified name.

An error will be raised if the placeholder indicated by name does not exist.

This function must be called once for each used placeholder or an error will be raised when the execute() method is called.

Method Chaining

This function can be invoked multiple times right before calling execute().

After this function invocation, the following functions can be invoked:

• bind(str name, Value value)
• execute()

See also

Usage examples at execute().

execute()

DocResult execute

(

)

Executes the find operation with all the configured options.

Returns

A DocResult object that can be used to traverse the documents returned by this operation.

Method Chaining

This function can be invoked after any other function on this class.

Examples

Retrieving All Documents

records = collection.find().execute().fetch_all()
print("All:", len(records), "\n")

Filtering

records = collection.find('gender = "male"').execute().fetch_all()
print("Males:", len(records), "\n")
 
records = collection.find('gender = "female"').execute().fetch_all()
print("Females:", len(records), "\n")

Field Selection

Using a field selection list

result = collection.find('name = "jack"').fields(['ucase(name) as FirstName', 'age as Age']).execute()
record = result.fetch_one()
print("First Name: %s\n" % record.FirstName)
print("Age: %s\n" % record.Age)

Using separate field selection parameters

result = collection.find('name = "jack"').fields('ucase(name) as FirstName', 'age as Age').execute()
record = result.fetch_one()
print("First Name: %s\n" % record.FirstName)
print("Age: %s\n" % record.Age)

Using a projection expression

result = collection.find('name = "jack"').fields(mysqlx.expr('{"FirstName":ucase(name), "InThreeYears":age + 3}')).execute()
record = result.fetch_one()
print("First Name: %s\n" % record.FirstName)
print("In Three Years: %s\n" % record.InThreeYears)

Sorting

records = collection.find().sort(['name']).execute().fetch_all()
for index in range(7):
  print('Find Asc', index, ':', records[index].name, '\n')
 
records = collection.find().sort(['name desc']).execute().fetch_all()
for index in range(7):
  print('Find Desc', index, ':', records[index].name, '\n')

Using Limit and Offset

records = collection.find().limit(4).execute().fetch_all()
print('Limit-Offset 0 :', len(records), '\n')
 
for index in range(8):
  records = collection.find().limit(4).offset(index + 1).execute().fetch_all()
  print('Limit-Offset', index + 1, ':', len(records), '\n')

Parameter Binding

records = collection.find('age = :years and gender = :heorshe').bind('years', 13).bind('heorshe', 'female').execute().fetch_all()
print('Find Binding Length:', len(records), '\n')
print('Find Binding Name:', records[0].name, '\n')