HumHub Documentation (unofficial)

SqlDataProvider extends BaseDataProvider
in package

SqlDataProvider implements a data provider based on a plain SQL statement.

SqlDataProvider provides data in terms of arrays, each representing a row of query result.

Like other data providers, SqlDataProvider also supports sorting and pagination. It does so by modifying the given [[sql]] statement with "ORDER BY" and "LIMIT" clauses. You may configure the [[sort]] and [[pagination]] properties to customize sorting and pagination behaviors.

SqlDataProvider may be used in the following way:

$count = Yii::$app->db->createCommand('
    SELECT COUNT(*) FROM user WHERE status=:status
', [':status' => 1])->queryScalar();

$dataProvider = new SqlDataProvider([
    'sql' => 'SELECT * FROM user WHERE status=:status',
    'params' => [':status' => 1],
    'totalCount' => $count,
    'sort' => [
        'attributes' => [
            'age',
            'name' => [
                'asc' => ['first_name' => SORT_ASC, 'last_name' => SORT_ASC],
                'desc' => ['first_name' => SORT_DESC, 'last_name' => SORT_DESC],
                'default' => SORT_DESC,
                'label' => 'Name',
            ],
        ],
    ],
    'pagination' => [
        'pageSize' => 20,
    ],
]);

// get the user records in the current page
$models = $dataProvider->getModels();

Note: if you want to use the pagination feature, you must configure the [[totalCount]] property to be the total number of rows (without pagination). And if you want to use the sorting feature, you must configure the [[sort]] property so that the provider knows which columns can be sorted.

For more details and usage information on SqlDataProvider, see the guide article on data providers.

Tags
author

Qiang Xue qiang.xue@gmail.com

since
2.0

Table of Contents

Properties

$behaviors  : array<string|int, Behavior>
$count  : int
$db  : Connection|array<string|int, mixed>|string
$id  : string|null
$key  : string|callable|null
$keys  : array<string|int, mixed>
$models  : array<string|int, mixed>
$pagination  : Pagination|false
$params  : array<string|int, mixed>
$sort  : Sort|bool
$sql  : string
$totalCount  : int
$_behaviors  : array<string|int, Behavior>|null
$_events  : array<string|int, mixed>
$_eventWildcards  : array<string|int, mixed>
$_keys  : mixed
$_models  : mixed
$_pagination  : mixed
$_sort  : mixed
$_totalCount  : mixed
$counter  : int

Methods

__call()  : mixed
Calls the named method which is not a class method.
__clone()  : mixed
This method is called after the object is created by cloning an existing one.
__construct()  : mixed
Constructor.
__get()  : mixed
Returns the value of a component property.
__isset()  : bool
Checks if a property is set, i.e. defined and not null.
__set()  : mixed
Sets the value of a component property.
__unset()  : mixed
Sets a component property to be null.
attachBehavior()  : Behavior
Attaches a behavior to this component.
attachBehaviors()  : mixed
Attaches a list of behaviors to the component.
behaviors()  : array<string|int, mixed>
Returns a list of behaviors that this component should behave as.
canGetProperty()  : bool
Returns a value indicating whether a property can be read.
canSetProperty()  : bool
Returns a value indicating whether a property can be set.
className()  : string
Returns the fully qualified name of this class.
detachBehavior()  : Behavior|null
Detaches a behavior from the component.
detachBehaviors()  : mixed
Detaches all behaviors from the component.
ensureBehaviors()  : mixed
Makes sure that the behaviors declared in [[behaviors()]] are attached to this component.
getBehavior()  : Behavior|null
Returns the named behavior object.
getBehaviors()  : array<string|int, Behavior>
Returns all behaviors attached to this component.
getCount()  : int
Returns the number of data models in the current page.
getKeys()  : array<string|int, mixed>
Returns the key values associated with the data models.
getModels()  : array<string|int, mixed>
Returns the data models in the current page.
getPagination()  : Pagination|false
Returns the pagination object used by this data provider.
getSort()  : Sort|bool
Returns the sorting object used by this data provider.
getTotalCount()  : int
Returns the total number of data models.
hasEventHandlers()  : bool
Returns a value indicating whether there is any handler attached to the named event.
hasMethod()  : bool
Returns a value indicating whether a method is defined.
hasProperty()  : bool
Returns a value indicating whether a property is defined for this component.
init()  : mixed
Initializes the DB connection component.
off()  : bool
Detaches an existing event handler from this component.
on()  : mixed
Attaches an event handler to an event.
prepare()  : mixed
Prepares the data models and keys.
refresh()  : mixed
Refreshes the data provider.
setKeys()  : mixed
Sets the key values associated with the data models.
setModels()  : mixed
Sets the data models in the current page.
setPagination()  : mixed
Sets the pagination for this data provider.
setSort()  : mixed
Sets the sort definition for this data provider.
setTotalCount()  : mixed
Sets the total number of data models.
trigger()  : mixed
Triggers an event.
prepareKeys()  : array<string|int, mixed>
Prepares the keys associated with the currently available data models.
prepareModels()  : array<string|int, mixed>
Prepares the data models that will be made available in the current page.
prepareTotalCount()  : int
Returns a value indicating the total number of data models in this data provider.
attachBehaviorInternal()  : Behavior
Attaches a behavior to this component.

Properties

$behaviors read-only

public array<string|int, Behavior> $behaviors

List of behaviors attached to this component.

$count read-only

public int $count

The number of data models in the current page.

$db

public Connection|array<string|int, mixed>|string $db = 'db'

the DB connection object or the application component ID of the DB connection. Starting from version 2.0.2, this can also be a configuration array for creating the object.

$id

public string|null $id

an ID that uniquely identifies the data provider among all data providers. Generated automatically the following way in case it is not set:

  • First data provider ID is empty.
  • Second and all subsequent data provider IDs are: "dp-1", "dp-2", etc.

$key

public string|callable|null $key

the column that is used as the key of the data models. This can be either a column name, or a callable that returns the key value of a given data model.

If this is not set, the keys of the [[models]] array will be used.

$keys

public array<string|int, mixed> $keys

The list of key values corresponding to [[models]]. Each data model in [[models]] is uniquely identified by the corresponding key value in this array.

$models

public array<string|int, mixed> $models

The list of data models in the current page.

$pagination

public Pagination|false $pagination

The pagination object. If this is false, it means the pagination is disabled. Note that the type of this property differs in getter and setter. See [[getPagination()]] and [[setPagination()]] for details.

$params

public array<string|int, mixed> $params = []

parameters (name=>value) to be bound to the SQL statement.

$sort

public Sort|bool $sort

The sorting object. If this is false, it means the sorting is disabled. Note that the type of this property differs in getter and setter. See [[getSort()]] and [[setSort()]] for details.

$sql

public string $sql

the SQL statement to be used for fetching data rows.

$_behaviors

private array<string|int, Behavior>|null $_behaviors

the attached behaviors (behavior name => behavior). This is null when not initialized.

$_events

private array<string|int, mixed> $_events = []

the attached event handlers (event name => handlers)

$_eventWildcards

private array<string|int, mixed> $_eventWildcards = []

the event handlers attached for wildcard patterns (event name wildcard => handlers)

Tags
since
2.0.14

$counter

private static int $counter = 0

Number of data providers on the current page. Used to generate unique IDs.

Methods

__call()

Calls the named method which is not a class method.

public __call(string $name, array<string|int, mixed> $params) : mixed

This method will check if any attached behavior has the named method and will execute it if available.

Do not call this method directly as it is a PHP magic method that will be implicitly called when an unknown method is being invoked.

Parameters
$name : string

the method name

$params : array<string|int, mixed>

method parameters

Tags
throws
UnknownMethodException

when calling unknown method

Return values
mixed —

the method return value

__clone()

This method is called after the object is created by cloning an existing one.

public __clone() : mixed

It removes all behaviors because they are attached to the old object.

__construct()

Constructor.

public __construct([array<string|int, mixed> $config = [] ]) : mixed

The default implementation does two things:

  • Initializes the object with the given configuration $config.
  • Call [[init()]].

If this method is overridden in a child class, it is recommended that

  • the last parameter of the constructor is a configuration array, like $config here.
  • call the parent implementation at the end of the constructor.
Parameters
$config : array<string|int, mixed> = []

name-value pairs that will be used to initialize the object properties

__get()

Returns the value of a component property.

public __get(string $name) : mixed

This method will check in the following order and act accordingly:

  • a property defined by a getter: return the getter result
  • a property of a behavior: return the behavior property value

Do not call this method directly as it is a PHP magic method that will be implicitly called when executing $value = $component->property;.

Parameters
$name : string

the property name

Tags
throws
UnknownPropertyException

if the property is not defined

throws
InvalidCallException

if the property is write-only.

see
__set()
Return values
mixed —

the property value or the value of a behavior's property

__isset()

Checks if a property is set, i.e. defined and not null.

public __isset(string $name) : bool

This method will check in the following order and act accordingly:

  • a property defined by a setter: return whether the property is set
  • a property of a behavior: return whether the property is set
  • return false for non existing properties

Do not call this method directly as it is a PHP magic method that will be implicitly called when executing isset($component->property).

Parameters
$name : string

the property name or the event name

Tags
see
https://www.php.net/manual/en/function.isset.php
Return values
bool —

whether the named property is set

__set()

Sets the value of a component property.

public __set(string $name, mixed $value) : mixed

This method will check in the following order and act accordingly:

  • a property defined by a setter: set the property value
  • an event in the format of "on xyz": attach the handler to the event "xyz"
  • a behavior in the format of "as xyz": attach the behavior named as "xyz"
  • a property of a behavior: set the behavior property value

Do not call this method directly as it is a PHP magic method that will be implicitly called when executing $component->property = $value;.

Parameters
$name : string

the property name or the event name

$value : mixed

the property value

Tags
throws
UnknownPropertyException

if the property is not defined

throws
InvalidCallException

if the property is read-only.

see
__get()

__unset()

Sets a component property to be null.

public __unset(string $name) : mixed

This method will check in the following order and act accordingly:

  • a property defined by a setter: set the property value to be null
  • a property of a behavior: set the property value to be null

Do not call this method directly as it is a PHP magic method that will be implicitly called when executing unset($component->property).

Parameters
$name : string

the property name

Tags
throws
InvalidCallException

if the property is read only.

see
https://www.php.net/manual/en/function.unset.php

attachBehavior()

Attaches a behavior to this component.

public attachBehavior(string $name, string|array<string|int, mixed>|Behavior $behavior) : Behavior

This method will create the behavior object based on the given configuration. After that, the behavior object will be attached to this component by calling the [[Behavior::attach()]] method.

Parameters
$name : string

the name of the behavior.

$behavior : string|array<string|int, mixed>|Behavior

the behavior configuration. This can be one of the following:

  • a [[Behavior]] object
  • a string specifying the behavior class
  • an object configuration array that will be passed to [[Yii::createObject()]] to create the behavior object.
Tags
see
detachBehavior()
Return values
Behavior —

the behavior object

attachBehaviors()

Attaches a list of behaviors to the component.

public attachBehaviors(array<string|int, mixed> $behaviors) : mixed

Each behavior is indexed by its name and should be a [[Behavior]] object, a string specifying the behavior class, or an configuration array for creating the behavior.

Parameters
$behaviors : array<string|int, mixed>

list of behaviors to be attached to the component

Tags
see
attachBehavior()

behaviors()

Returns a list of behaviors that this component should behave as.

public behaviors() : array<string|int, mixed>

Child classes may override this method to specify the behaviors they want to behave as.

The return value of this method should be an array of behavior objects or configurations indexed by behavior names. A behavior configuration can be either a string specifying the behavior class or an array of the following structure:

'behaviorName' => [
    'class' => 'BehaviorClass',
    'property1' => 'value1',
    'property2' => 'value2',
]

Note that a behavior class must extend from [[Behavior]]. Behaviors can be attached using a name or anonymously. When a name is used as the array key, using this name, the behavior can later be retrieved using [[getBehavior()]] or be detached using [[detachBehavior()]]. Anonymous behaviors can not be retrieved or detached.

Behaviors declared in this method will be attached to the component automatically (on demand).

Return values
array<string|int, mixed> —

the behavior configurations.

canGetProperty()

Returns a value indicating whether a property can be read.

public canGetProperty(string $name[, bool $checkVars = true ][, bool $checkBehaviors = true ]) : bool

A property can be read if:

  • the class has a getter method associated with the specified name (in this case, property name is case-insensitive);
  • the class has a member variable with the specified name (when $checkVars is true);
  • an attached behavior has a readable property of the given name (when $checkBehaviors is true).
Parameters
$name : string

the property name

$checkVars : bool = true

whether to treat member variables as properties

$checkBehaviors : bool = true

whether to treat behaviors' properties as properties of this component

Tags
see
canSetProperty()
Return values
bool —

whether the property can be read

canSetProperty()

Returns a value indicating whether a property can be set.

public canSetProperty(string $name[, bool $checkVars = true ][, bool $checkBehaviors = true ]) : bool

A property can be written if:

  • the class has a setter method associated with the specified name (in this case, property name is case-insensitive);
  • the class has a member variable with the specified name (when $checkVars is true);
  • an attached behavior has a writable property of the given name (when $checkBehaviors is true).
Parameters
$name : string

the property name

$checkVars : bool = true

whether to treat member variables as properties

$checkBehaviors : bool = true

whether to treat behaviors' properties as properties of this component

Tags
see
canGetProperty()
Return values
bool —

whether the property can be written

className()

Returns the fully qualified name of this class.

public static className() : string
Tags
deprecated

since 2.0.14. On PHP >=5.5, use ::class instead.

Return values
string —

the fully qualified name of this class.

detachBehavior()

Detaches a behavior from the component.

public detachBehavior(string $name) : Behavior|null

The behavior's [[Behavior::detach()]] method will be invoked.

Parameters
$name : string

the behavior's name.

Return values
Behavior|null —

the detached behavior. Null if the behavior does not exist.

detachBehaviors()

Detaches all behaviors from the component.

public detachBehaviors() : mixed

ensureBehaviors()

Makes sure that the behaviors declared in [[behaviors()]] are attached to this component.

public ensureBehaviors() : mixed

getBehavior()

Returns the named behavior object.

public getBehavior(string $name) : Behavior|null
Parameters
$name : string

the behavior name

Return values
Behavior|null —

the behavior object, or null if the behavior does not exist

getBehaviors()

Returns all behaviors attached to this component.

public getBehaviors() : array<string|int, Behavior>
Return values
array<string|int, Behavior> —

list of behaviors attached to this component

getCount()

Returns the number of data models in the current page.

public getCount() : int
Return values
int —

the number of data models in the current page.

getKeys()

Returns the key values associated with the data models.

public getKeys() : array<string|int, mixed>
Return values
array<string|int, mixed> —

the list of key values corresponding to [[models]]. Each data model in [[models]] is uniquely identified by the corresponding key value in this array.

getModels()

Returns the data models in the current page.

public getModels() : array<string|int, mixed>
Return values
array<string|int, mixed> —

the list of data models in the current page.

getPagination()

Returns the pagination object used by this data provider.

public getPagination() : Pagination|false

Note that you should call [[prepare()]] or [[getModels()]] first to get correct values of [[Pagination::totalCount]] and [[Pagination::pageCount]].

Return values
Pagination|false —

the pagination object. If this is false, it means the pagination is disabled.

getSort()

Returns the sorting object used by this data provider.

public getSort() : Sort|bool
Return values
Sort|bool —

the sorting object. If this is false, it means the sorting is disabled.

getTotalCount()

Returns the total number of data models.

public getTotalCount() : int

When [[pagination]] is false, this returns the same value as [[count]]. Otherwise, it will call [[prepareTotalCount()]] to get the count.

Return values
int —

total number of possible data models.

hasEventHandlers()

Returns a value indicating whether there is any handler attached to the named event.

public hasEventHandlers(string $name) : bool
Parameters
$name : string

the event name

Return values
bool —

whether there is any handler attached to the event.

hasMethod()

Returns a value indicating whether a method is defined.

public hasMethod(string $name[, bool $checkBehaviors = true ]) : bool

A method is defined if:

  • the class has a method with the specified name
  • an attached behavior has a method with the given name (when $checkBehaviors is true).
Parameters
$name : string

the property name

$checkBehaviors : bool = true

whether to treat behaviors' methods as methods of this component

Return values
bool —

whether the method is defined

hasProperty()

Returns a value indicating whether a property is defined for this component.

public hasProperty(string $name[, bool $checkVars = true ][, bool $checkBehaviors = true ]) : bool

A property is defined if:

  • the class has a getter or setter method associated with the specified name (in this case, property name is case-insensitive);
  • the class has a member variable with the specified name (when $checkVars is true);
  • an attached behavior has a property of the given name (when $checkBehaviors is true).
Parameters
$name : string

the property name

$checkVars : bool = true

whether to treat member variables as properties

$checkBehaviors : bool = true

whether to treat behaviors' properties as properties of this component

Tags
see
canGetProperty()
see
canSetProperty()
Return values
bool —

whether the property is defined

init()

Initializes the DB connection component.

public init() : mixed

This method will initialize the [[db]] property to make sure it refers to a valid DB connection.

Tags
throws
InvalidConfigException

if [[db]] is invalid.

off()

Detaches an existing event handler from this component.

public off(string $name[, callable|null $handler = null ]) : bool

This method is the opposite of [[on()]].

Note: in case wildcard pattern is passed for event name, only the handlers registered with this wildcard will be removed, while handlers registered with plain names matching this wildcard will remain.

Parameters
$name : string

event name

$handler : callable|null = null

the event handler to be removed. If it is null, all handlers attached to the named event will be removed.

Tags
see
on()
Return values
bool —

if a handler is found and detached

on()

Attaches an event handler to an event.

public on(string $name, callable $handler[, mixed $data = null ][, bool $append = true ]) : mixed

The event handler must be a valid PHP callback. The following are some examples:

function ($event) { ... }         // anonymous function
[$object, 'handleClick']          // $object->handleClick()
['Page', 'handleClick']           // Page::handleClick()
'handleClick'                     // global function handleClick()

The event handler must be defined with the following signature,

function ($event)

where $event is an [[Event]] object which includes parameters associated with the event.

Since 2.0.14 you can specify event name as a wildcard pattern:

$component->on('event.group.*', function ($event) {
    Yii::trace($event->name . ' is triggered.');
});
Parameters
$name : string

the event name

$handler : callable

the event handler

$data : mixed = null

the data to be passed to the event handler when the event is triggered. When the event handler is invoked, this data can be accessed via [[Event::data]].

$append : bool = true

whether to append new event handler to the end of the existing handler list. If false, the new handler will be inserted at the beginning of the existing handler list.

Tags
see
off()

prepare()

Prepares the data models and keys.

public prepare([bool $forcePrepare = false ]) : mixed

This method will prepare the data models and keys that can be retrieved via [[getModels()]] and [[getKeys()]].

This method will be implicitly called by [[getModels()]] and [[getKeys()]] if it has not been called before.

Parameters
$forcePrepare : bool = false

whether to force data preparation even if it has been done before.

refresh()

Refreshes the data provider.

public refresh() : mixed

After calling this method, if [[getModels()]], [[getKeys()]] or [[getTotalCount()]] is called again, they will re-execute the query and return the latest data available.

setKeys()

Sets the key values associated with the data models.

public setKeys(array<string|int, mixed> $keys) : mixed
Parameters
$keys : array<string|int, mixed>

the list of key values corresponding to [[models]].

setModels()

Sets the data models in the current page.

public setModels(array<string|int, mixed> $models) : mixed
Parameters
$models : array<string|int, mixed>

the models in the current page

setPagination()

Sets the pagination for this data provider.

public setPagination(array<string|int, mixed>|Pagination|bool $value) : mixed
Parameters
$value : array<string|int, mixed>|Pagination|bool

the pagination to be used by this data provider. This can be one of the following:

  • a configuration array for creating the pagination object. The "class" element defaults to 'yii\data\Pagination'
  • an instance of [[Pagination]] or its subclass
  • false, if pagination needs to be disabled.
Tags
throws
InvalidArgumentException

setSort()

Sets the sort definition for this data provider.

public setSort(array<string|int, mixed>|Sort|bool $value) : mixed
Parameters
$value : array<string|int, mixed>|Sort|bool

the sort definition to be used by this data provider. This can be one of the following:

  • a configuration array for creating the sort definition object. The "class" element defaults to 'yii\data\Sort'
  • an instance of [[Sort]] or its subclass
  • false, if sorting needs to be disabled.
Tags
throws
InvalidArgumentException

setTotalCount()

Sets the total number of data models.

public setTotalCount(int $value) : mixed
Parameters
$value : int

the total number of data models.

trigger()

Triggers an event.

public trigger(string $name[, Event|null $event = null ]) : mixed

This method represents the happening of an event. It invokes all attached handlers for the event including class-level handlers.

Parameters
$name : string

the event name

$event : Event|null = null

the event instance. If not set, a default [[Event]] object will be created.

prepareKeys()

Prepares the keys associated with the currently available data models.

protected prepareKeys(mixed $models) : array<string|int, mixed>
Parameters
$models : mixed

the available data models

Return values
array<string|int, mixed> —

the keys

prepareModels()

Prepares the data models that will be made available in the current page.

protected prepareModels() : array<string|int, mixed>
Return values
array<string|int, mixed> —

the available data models

prepareTotalCount()

Returns a value indicating the total number of data models in this data provider.

protected prepareTotalCount() : int
Return values
int —

total number of data models in this data provider.

attachBehaviorInternal()

Attaches a behavior to this component.

private attachBehaviorInternal(string|int $name, string|array<string|int, mixed>|Behavior $behavior) : Behavior
Parameters
$name : string|int

the name of the behavior. If this is an integer, it means the behavior is an anonymous one. Otherwise, the behavior is a named one and any existing behavior with the same name will be detached first.

$behavior : string|array<string|int, mixed>|Behavior

the behavior to be attached

Return values
Behavior —

the attached behavior.


        

        
    




    

    

                                

                

                                
    

        On this page

        
    


                

                            

            

    

        

            
Search results