LiveWhale CMS

Welcome, Guest Login

Support Center

Our documentation is moving: docs.livewhale.com

Please check docs.livewhale.com for the most up-to-date LiveWhale CMS and LiveWhale Calendar documentation. The below legacy documentation will remain available as a reference until the documentation migration is complete.

Using LiveWhale’s LWQuery

Last Updated: Jul 06, 2016 01:41PM PDT

LWQuery is LiveWhale’s wrapper for database interaction. As a developer, you have full ability to interact with your data— all of it— and the framework offers LWQuery as an easy means to do that, without having to make or manage connections or write explicit SQL. And while you might typically use LWQuery to access your LiveWhale database, you can also use it to access external MySQL databases too.

Single Query Usage

Basic Usage

So long as you use this in handlers with a leading global $_LW; or in a script from which you’ve previously instantiated LiveWhale, you already have access to the database via LWQuery. A simple select example is:
 

class LiveWhaleApplicationMyApp {
  public function onLoad () { // runs when the module is loaded
    global $_LW;
    $result = $_LW->db->query('select','livewhale_news.headline','livewhale_news',
'livewhale_news.id=1')->firstRow()->run();
    // selects the first news story and assigns it to $result
  }
}

You may specify additional values such as a where condition, a field to sort on and a limit with the same method:
 

$_LW->dbo->query('select','table.field1,table.field2','table','table.id=1','table.sort_field',30);
// selecting multiple fields, adds where clause, sort field, limit

Execution

LWQuery supports chaining of multiple conditions, joins, etc. in a single string or in subsequent additions. To indicate that all query parameters have been appended, you execute the query by appending a final ->run() as shown in the earliest example above.

Results and Iteration

If you only need a single matching row, then you can append ->firstRow() immediately before ->run() to have the row returned as an associative array:
 

if ($results=$_LW->dbo->query(...)->firstRow()->run()) {
  // do something with $results
}

 

If you want all matching rows, then you can append ->allRows() immediately before ->run() to have all the rows returned as an associative array:
 

if ($results=$_LW->dbo->query(...)->allRows()->run()) {
  // do something with $results
}


You can also iterate through multiple returned $results quite easily:
 

if ($results=$_LW->dbo->query(...)->run()) {
  if ($results->hasResults()) {
    echo $results->num_rows;
    while ($result=$results->next()) {
      print_r($result);
    }
  }
}

Do note that the $results->next(); method automatically excludes shared content which it has already encountered. Thus, you may not reach a requested ->limit() value in some cases where there is content overlap.

Procedural Style

As noted above, you may chain any number of query modifications until you execute the query with ->run(), but you also need not start with ->query() as a base. You can start with either ->query() and append additional query parameters, or simply begin by specifying your needed parameters, many of which can be specified multiple times in any order.

 

$_LW->dbo->select('table');
$_LW->dbo->fields('table.field1,table.field2');
$_LW->dbo->where('table.id=1');
$_LW->dbo->where('table.pid=2');
$_LW->dbo->innerJoin('table2','table.x=table2.y');
$_LW->dbo->sortBy('table.field1');
$_LW->dbo->limit(10); // or limit(10,30) to include an offset of 10
$_LW->dbo->​orderBy('table.field1 ASC, table.field2 DESC');
$_LW->dbo->groupBy('table.field2');
$_LW->dbo->having('table.field1=value'); // must be used after groupBy()
...
$results=$_LW->dbo->run();

You may also mix styles by beginning with ->query() and modifying it to your need with the procedural style.

Joins

LWQuery supports joins (->innerJoin(), ->outerJoin(), ->leftJoin(),->leftOuterJoin,  and ->rightJoin()) to return associated content in a single request. An example is:
 

$results=$_LW->dbo->query('select',...)->leftJoin('table2','table1.field=table2.field')->run();
// left joins table2 on table1.field=table2.field

Alternative Syntax for Multiple Similar Parameters

When your request requires multiple fields, tables, LWQuery can handle them automatically if provided in an array.

 

$_LW->dbo->query('select','table.x,table.y','table');
$_LW->dbo->query('select',array('table.x','table.y'),'table');
// these are treated the same (i.e. you can supply comma separated values as a string, or an array)

$_LW->dbo->query('select','table.x,table.y','table','table.x=1 AND table.y=2');
$_LW->dbo->query('select','table.x,table.y','table',array('table.x=1','table.y=2'));
// these are treated the same as a WHERE, "AND" implied

$_LW->dbo->fields('table.field1,table.field2');
$_LW->dbo->fields(array('table.field1','table.field2'));
// the alternative syntax supported in procedural style also

Automatic Escaping

LWQuery does not automatically escape data to guard against SQL injection attacks. Users must perform their own filtering, or use the framework function $_LW->escape() which will sanitize all data going into SQL statements.

However, when using procedural style calls to fields() on an INSERT or UPDATE, or a where() method, values to be escaped can be specified with the placeholder character "?". An additional argument must be supplied, which is an array of values corresponding to the placeholders. In this case, all values in that array will be automatically escaped before SQL execution.
 

$_LW->dbo->query('select','*','table')->where('field=?',array($field_value));
// $field_value will be automatically escaped

Duplicate Keys, Flags and Hinting

You can apply SQL flags and override duplicate key errors when they occur with LWQuery as follows:
 

$_LW->dbo->query('insert',array('table.field'=>'value'),'table',true)->run();
// extra "true" arg results in ON DUPLICATE KEY UPDATE for supplied field/value pairs

Other flags can be specified explicitly:
 

$_LW->dbo->query('select','table.field','table')->flags('sql_no_cache')->run();
// this supports one or an array of:
// SQL_NO_CACHE
// DELAYED
// LOW_PRIORITY
// HIGH_PRIORITY
// SQL_CALC_FOUND_ROWS
// QUICK
// IGNORE
// DISTINCT

When using SQL_CALC_FOUND_ROWS you do not need to SELECT FOUND_ROWS() in a subsequent query. LWQuery automatically sets $result->found_rows to the # of found rows after executing the query.

To facilitate index hinting, you may append one of two chainable methods:
 

$_LW->dbo->useIndex('index_name'); // Applies a USE INDEX hint to the query
$_LW->dbo->forceIndex('index_name'); // Applies a FORCE INDEX hint to the query

Last Insert ID

You can easily obtain a LAST_INSERT_ID() by using:
 

$id=$_LW->dbo->lastInsertID();

Multiple Query Usage

Chaining Queries

You may chain executed queries together to return the results together:

 

$results=$_LW->dbo->query('select',...)->query('select',...)->run();
// returns array of 2 result sets, rather than a single result set

$results=$_LW->dbo->query('delete',...)->$_LW->dbo->query('select',...)->query('select',...)->run();
// you can chain different query types, this still returns 2 result sets

Unions

Given a chain of pending queries with more than one SELECT statement, adding isUnion() to the end of the query chain will merge all previous SELECTs into a single union query.
 

$_LW->dbo->query('select','id','tableA','id=1');
$_LW->dbo->query('select','id','tableB','id=2');
$_LW->dbo->isUnion()->run();
// (SELECT id FROM tableA WHERE id=1) UNION (SELECT id FROM tableB WHERE id=2);

Transactions and Multi-queries

You can execute transactions and multi-queries for all but SELECT queries, throttling either to ensure that commits occur at regular intervals.

 

$_LW->dbo->startTransaction(30);
// automatically commits after every 30 queries, supply no value for no throttling
$_LW->dbo->query('delete',...)->run();
$_LW->dbo->query('delete',...)->run();
...
$_LW->dbo->endTransaction(); // automatically commits pending queries

$_LW->dbo->startMultiQuery(30);
​// automatically commits after every 30 queries, supply no value for no throttling
$_LW->dbo->query('delete',...)->run();
$_LW->dbo->query('delete',...)->run();
...
$_LW->dbo->endMultiQuery(); // automatically commits pending queries



Table Upgrades and Migrations

The methods createUpgradeTable() and enableUpgradeTable() can be used to safely perform ALTER statements on open tables without adversely affecting end users.

This is accomplished by duplicating the table with the suffix “_new” appended to its current name. You can then perform all the upgrades/migrations necessary on the duplicate and swap the table in once changes are complete. An example follows:

 

if ($_LW->dbo->createUpgradeTable('table')) { // 'table' is cloned as table_new
  $_LW->dbo->sql('ALTER TABLE table_new ...'); // ALTER statements performed on table_new
  $_LW->dbo->enableUpgradeTable('table'); // 'table' is replaced with table_new
}
eb8f96c071020d8b0923da726d6cab65@livewhale.desk-mail.com
https://cdn.desk.com/
false
desk
Loading
seconds ago
a minute ago
minutes ago
an hour ago
hours ago
a day ago
days ago
about
false
Invalid characters found
/customer/en/portal/articles/autocomplete?b_id=4256