Query constructor

For a quick extraction of model data in MV framework there are special methods related to SQL query constructor. These methods can be called from models objects and by default work with initial model's table.

Examples of using a query constructor

$rows = $this -> select(array("order->desc" => "date", "limit->" => "21,7"));

$rows = $this -> select(array("parent" => 7, "active" => 1, "in_menu" => 1, "order->asc" => "order"));

$block_content = $mv -> blocks -> findRecord(array("id" => 1, "active" => 1));

$last_article = $mv -> articles -> selectOne(array("order->desc" => "date", "active" => 1));

$total_active = $mv -> events -> countRecords(array("active" => 1));

$client = $mv -> clients -> findRecordById(487);

Query constructor methods, use as a parameter an array with SQL request options related to a model table. If no parameter passed (or an empty array was passed), then all records will be extracted from table (counted if it's a count request).

select() - returns an associative array of records (rows, arrays) from a database, that meet search criteria

selectOne() - returns an associative array with the fields of one record

selectColumn() - returns a standard array with the values of required column, "fields->" parameter is mandatory

countRecords() - returns and integer number, which is quantity of records in table that meet search criteria

findRecord() - the same as "selectOne()", only if a record is found, then an object of Record class will return for easy operating with data. If no record found, then "false" returns.

findRecordById($id) - the same as "findRecord()", but with only parameter "id" of required record. The only argument is an integer - "id" of record in search. An object of Record class returns, or false if no record.

Examples of use

//Search the page in table by URL
$content = $mv -> pages -> findRecord(array("url" => "contact", "active" => 1));
<h1><? echo $content -> name; ?></h1>
<div><? echo $content -> content; ?></div>

//Create menu list  
public function displayMenu($parent)
{
    $rows = $this -> select(array("parent" => $parent, 
                                  "active" => 1, 
                                  "in_menu" => 1, 
                                  "order->asc" => "order"));
    foreach($rows as $row)
    {
        ...
    }
}

//Create news feed
public function display()
{
    $rows = $this -> select(array("order->desc" => "date", 
                                  "limit->" => $this -> pager -> getParamsForSQL()));
    foreach($rows as $row)
    {
        ...
    }
}

Parameters for SQL query constructor

"field" => "value" - parameters of "key-value" format (validation for match equal)

//All records in parent section id=32
$rows = $this -> select(array("parent" => 32));

//All news from Sports section, that are approved for publishing
$rows = $mv -> news -> select(array("type" => "sport", "active" => 1));

//Corporate clients with confirmed registration on a specific date
$rows = $mv -> clients -> select(array("group" => "copropate", "confirm" => 1, "date" => "2012-07-24"));

"field(!=, >, <, >=, <=)" => "value" - parameters of "key-value" format (validation for non-equal or other conditions)

//All records except type with id=7, suppose the type has foreign key
$rows = $this -> select(array("type!=" => 7));

//Products with a price > 450
$rows = $this -> select(array("price>" => 450));

//Books with volume more that 300 pages, received within a specific date interval
$rows = $mv -> books -> select(array("pages>=" => 300, "date>=" => "2011-02-21", "date<=" => "2012-11-07"));

"fields->" => "`aaa`,`bbb`,`ccc`" - limitation of selection fields (by default all fields of table are taken)

//Take names and content of active blocks
$rows = $mv -> blocks -> select(array("active" => 1, "fields->" => "`name`,`content`"));

"field->in" => "1,2,3" - group of parameter's values, glued with comma

//Select products with id equal to  3, 64, and 9, that are shown in a menu
$rows = $mv -> products -> select(array("id->in" => "3,64,9", "in_menu" => 1));

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

"field->not-in" => "1,2,3" - group of parameter's values, the parameter should not match to

//Select all clients, except listed ones
$rows = $mv -> clients -> select(array("id->not-in" => "3,77,91"));

"field->like" => "abc" - comparison with a string value

//All clients with a last name containing "john"
$rows = $mv -> clients -> select(array("last_name->like" => "john"));

"field->not-like" => "abc" - comparison with absence of string value

//All pages that don't have "shop" in titles
$rows = $mv -> pages -> select(array("title->not-like" => "shop"));

"order->asc" => "field", "order->desc" => "field" - sorting of query result (read more in Sorting section)

//Articles by publication date, the latest come the first
$rows = $mv -> articles -> select(array("order->desc" => "date"));

//Catalog products by price, the cheaper are the first
$rows = $mv -> products -> select(array("catalog" => "49", "order->asc" => "price"));

"order->double" => "field->direction" - extra double sorting of query result, usually used with "order->asc" or "order->desc"

//Articles sorted by date and rating
$rows = $mv -> articles -> select(array("order->desc" => "date", "order->double" => "rating->desc"));

"order->in" => "23,5,76,9" - sorting of query result in order of sequence of id parameter values. Applies only for MySQL, with using a construction of "ORDER BY FIELD (`id`, 23,5,76,9)"

//Products by order of passed id
$rows = $mv -> products -> select(array("id->in" => "7,32,56", "order->in" => "7,32,56"));

"order->" => "random" - "order->" => "random" – sorting of query result in random order

//3 articles in random order
$rows = $mv -> articles -> select(array("order->" => "random", "limit->" => 3));

"limit->" => "value" - limitation of query result number (read more Pagination section)

//3 photos that are available to be published on main page
$rows = $mv -> photos -> select(array("index_page" => 1, "limit->" => "3"));

//Latest events, 3 pages with 10 events per a page 
$rows = $mv -> events -> select(array("order->desc" => "date", "limit->" => "20,10"));

"table->" => "value" - indication of table, the query is connected to (by default, a table of model is taken where the method is called from)

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

//Often used inside a model method when you need to connect to another table
$this -> select(array("table->" => "people", "event_id" => 3));

"group->by" - group result on specified field (SQL operator "GROUP BY")

//All producers of goods in this catalog, ids are being taken
$conditions = array("active" => 1, "group->by" => "producer", "parent" => $catalog -> id, "fields->" => "id");
$current_producers = $mv -> products -> selectCoulumn($conditions);

"field->m2m" => "value" - extraction of data by many-to-many field, the value is id of row in opposite model

//All news by tag with id=24, tags and news are linked with many-to-many ration
$mv -> news -> select(array("active" => 1, "tag->m2m" => 24));

"extra->" => "sql query" - additional terms of query in standard SQL format. They are added after all conditions of WHERE operator in SQL query. Will be used to pass a request with complex conditions with brackets and/or “OR”.

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

Attention ! Query constructor is used for a quick extraction of data by the most popular scenarios. It doesn’t cover all the varieties of SQL constructions. To make any SQL request use Direct queries.

Previous section

Form methods

Next section

Direct queries