Query builder

For fast data extraction from models, MV uses special methods that access the SQL query builder.

Examples of using the query builder

//Queries inside the model class
$rows = $this -> select(['order->desc' => 'date', 'limit->' => 21,7]);
$rows = $this -> select(['parent' => 7, 'active' => 1, 'in_menu' => 1, 'order->asc' => 'order']);

//Queries from the template
$page = $mv -> pages -> find(34);
$block_content = $mv -> blocks -> find(['id' => 1, 'active' => 1]);
$last_article = $mv -> articles -> selectOne(['order->desc' => 'active' => 1]);
$total_active = $mv -> events -> countRecords(['active' => 1]);

Model methods that use the query builder all use an array with parameters for the SQL query as a parameter and access the model table. If no parameters were passed, or an empty array was passed, all records from the model table will be retrieved.

• select() - returns an associated array of records from the database that meet the search conditions, the array indices are the record ids
• selectOne() - returns an associated array with the fields of one record
• selectColumn() - returns a one-dimensional index array with the values ​​of the desired column, the 'fields->' parameter is required
• countRecords() - returns an integer that is the number of fields that meet the specified conditions, without conditions it will count all records
• find() - the same as selectOne(), but if the desired record was found, then the Record object will be returned, if the record is not found, then will be returned null

//Find active page by URL
$content = $mv -> pages -> find(['url' => 'contact', 'active' => 1]);
echo $content -> name;
echo $content -> content;

//Building a menu list
public function displayMenu($parent)
{
    $rows = $this -> select(['parent' => $parent, 
                             'active' => 1,
                             'in_menu' => 1,
                             'order->asc' => 'order']);
    
    foreach($rows as $row)
    {
        ...
    }
}

//Building a news feed with pagination
public function display()
{
    $rows = $this -> select(['order->desc' => 'date', 
                             'limit->' => $this ->  paginator -> getParamsForSQL()]);
    
    foreach($rows as $row)
    {
        ...
    }
}

Parameters for the SQL query constructor

'field' => 'value' - parameters of the ‘key-value’ type, equality check

//All entries in the parent section id=32
$rows = $this -> select(['parent' => 32]);

//Entries with specific ids as an array
$rows = $this -> select(['id' => [1,5,12]]);

//All news from the Sports section that are approved for publication
$rows = $mv -> news -> select(['type' => 'sport', 'active' => 1]);

//All news of specific categories
$rows = $mv -> news -> select(['type' => ['sport', 'life', 'relax'], 'active' => 1]);

//Corporate clients with confirmed registration for a specific date
$rows = $mv -> clients -> select(['group' => 'copropate', 'confirm' => 1, 'date' => '2023-07-24']);

'field(!=, >, <, >=, <=)' => 'value' - parameters of the ‘key-value’ type, inequality check and other conditions

//All records except the type with id=7
$rows = $this -> select(['type!=' => 7]);

//Products with a price greater than 450
$rows = $this -> select(['price>' => 450]);

//Books with more than 300 pages, received within a certain time interval
$rows = $mv -> books -> select(['pages>=' => 300, 'date>=' => '2022-02-21', 'date<=' => '2024-05-07']);

'fields->' => 'aaa, bbb, ccc' - limitation of fields for selection, by default all fields of the table are taken

//Take names and contents of all active blocks
$rows = $mv -> blocks -> select(['active' => 1, 'fields->' => 'name,content']);

'field->in' => '1,2,3' - list of possible values ​​of the parameter

//Select products with id equal to 3, 64, and 9, which are displayed in the menu
$rows = $mv -> products -> select(['id->in' => '3,64,9', 'in_menu' => 1]);

//Select news from sections 1,3,4,7 and 12
$rows = $mv -> news -> select(['theme->in' => '1,3,4,7,12']);

//Option with an array
$rows = $mv -> news -> select(['theme' => [1,3,4,7,12]]);

'field->not-in' => '1,2,3' - list of values ​​that the parameter should not satisfy

//Select all clients except those listed
$rows = $mv -> clients -> select(['id->not-in' => '3,77,91']);

'field->like' => 'abc' - compare with a string value

//All clients whose last name contains 'ivan'
$rows = $mv -> clients -> select(['last_name->like' => 'ivan']);

'field->not-like' => 'abc' - comparison with the absence of a string value

//All pages that do not have 'shop' in their titles

$rows = $mv -> pages -> select(['title->not-like' => 'shop']);

'order->asc' => 'field', 'order->desc' => 'field' - sorting of the selection results, more details in the section Sorting

//Articles by publication date, the most recent first
$rows = $mv -> articles -> select(['order->desc' => 'date']);

//Section products by price, cheap first
$rows = $mv -> products -> select(['catalog' => 49, 'order->asc' => 'price']);

'order->double' => 'field->direction' - additional double sorting of the selection results, used together with 'order->asc' => 'field' or 'order->desc' => 'field'

//Articles by publication date and additionally by rating, the most popular first
$rows = $mv -> articles -> select(['order->desc' => 'date', 'order->double' => 'rating->desc']);

'order->in' => '23,5,76,9' - sorting the results of the selection in the order of the id parameter values, only applies to MySQL, and the 'ORDER BY FIELD (`id`, 23,5,76,9)' construct is run

//Products in the order of the transferred id
$rows = $mv -> products -> select(['id->in' => '7,32,56', 'order->in' => '7,32,56']);

'order->' => 'random' - sorting the results of the selection in random order

//3 articles in random order
$rows = $mv -> articles -> select(['order->' => 'random', 'limit->' => 3]);

'limit->' => 'value' - limitation of the number of results in the selection, more details in the section Pagination

//3 photos that are available for placement on the main page
$rows = $mv -> photos -> select(['index_page' => 1, 'limit->' => '3']);

//Latest events, page 3 when displaying 10 events per page
$rows = $mv -> events -> select(['order->desc' => 'date', 'limit->' => '20,10']);

'table->' => 'value' - specifies the table to which the query is directed, by default the model table is taken from which the method is called

//All active comments to the article
$rows = $mv -> articles -> select(['active' => 1, 'parent' => 45 'table->' => 'comments']);

//Often used inside the model method when you need to access another table
$this -> select(['table->' => 'people', 'event_id' => 3]);

'group->by' - grouping results by a specified field

//All manufacturers of goods in this catalog
$conditions = ['active' => 1, 'group->by' => 'producer', 'parent' => $catalog -> id, 'fields->' => 'id'];
$current_producers = $mv -> products -> selectColumn($conditions);

'extra->' => 'sql query' - additional query conditions in regular SQL format. Added after all conditions of the WHERE operator in the SQL query. Used to pass complex conditions with brackets and/or the OR condition to the query.

$mv -> photos -> select(['active' => 1, 'extra->' => "((`type`='ceiling' AND `square`<='38' AND `square`>='7') OR `type` IN('wall', 'table', 'floor'))"]);

'field->m2m' => 'value' - data extraction by many-to-many field, the value is the id of the record of the opposite model

//All news by tag with id=24, tags and news are related by a many-to-many relationship
$mv -> news -> select(['active' => 1, 'tag->m2m' => 24]);