Query Constructor

For a quick extraction of model data in MV special methods are used, that are related to SQL query constructor.

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);

Model methods, that are using queries constructor, as a parameter using an array with SQL request parameters and connect to a model table. If no parameter passed (or an empty array was passed), then all records will be extracted (counted).

  • 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 colulm, "fields->" parameter is mandatory
  • countRecords() - returns and integer, which is number of fields that meet search criteria
  • findRecord() - the same as "selectOne()", only if a record is found, then it’s id will be placed into “id” property of the current model and the 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 to 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
$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 time 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" - order of possible values for the parameter

//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" - order of 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
$conditions = array("active" => 1, "group->by" => "producer", "parent" => $catalog -> id, "fields->" => "id");
$current_producers = $mv -> products -> selectCoulumn($conditions);

"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 on the most popular scenario. It doesn’t cover all the varieties of SQL constructions. To make any SQL request use Direct Queries.