Joins

Tables¶

Create table objects with advanced query features including immutable and mutable joins.

Joining¶

The join() starts the chain of join methods, followed by the type of join. If no type is given, then default is innerjoin(). The on() method tells the join what table is being joined. All subsequent queries will reference this joined table by the tablename or alias. The table is linked by the map() array.

join()->type->on->map

types:
    innerjoin()
    leftjoin()
    rightjoin()    

on:
    on('tablename')    
    on('tablename','alias')

map:    
    map(['field' => 'value'])
    map(['field1' => 'value1', 'field2 => 'value2'])

Code Sample

use function vnix\std_key;
use vnix\std\track\opo\table\gentable;
$check = (new gentable(std_key()->dbApp('translate')))->select;
$check->inputColumn('id');
$check->join()->on('nsindex')->map([
    'nsindex_id' => 'id',
]);
$sql = $check->sqlRender();
$row = $this->dbo->recordQuery($sql);

Need documentation

Immutable Joins¶

Need documentation

Joining columns from other database tables requires the join() function.

Mutable Joins¶

Columns capable of or subject to change or alteration.

mutable()

Enables the creating and updating of joined records.

Syntax Example

$this->form1q->join()->on(’joined table’)->mutable();

The available mutable chained methods are shown below:

autoUpdate¶

Defines the date column to automatically update when the record changes.

insert¶

Define a list of options controlling the insert of new record.

autoUpdate(string)¶

Defines the date column to automatically update when the record changes.

Syntax Example

$this->form1q->join()->on(’joined table’)->mutable()->autoUpdate(‘updated’);

The following coding format for mutable methods is recommended over the previous example. It allows all mutable methods to be aligned for easy code reading.

$this->form1q->join()->on(’joined table’)->mutable()->autoUpdate(‘updated’);

autoInsertPrebind(array)¶

Upon editing, creates a new record in the joined table, then binds it to the form. Viewing voids the join and hides the joined fields on the form.

Typically used to create timeline records while editing other fields on the form. The timeline fields will display only when editing. In one scenario, using a form followed by a table listing items from the timeline, when the user clicks the Edit button, new fields appear to enter a date and comment, then when the record is saved, the fields disappear from the form while the results of data entered into the timeline show up in the table below.

!!! important There is currently no way to use autoInsertPrebind(array) with branch level navigation, which allows moving through a record list based on a table by clicking the prev/next buttons. The reason for this is the branch cannot reset correctly when changing records.

autoInsertPrebindOnclick(array)¶

This joins a table and creates binding from click event. This is similar to autoInsertPrebind(array) except a button has to be clicked.

autoInsertRight(array)¶

This creates a join if a link does not exist with the primary key of the main table in a secondary table. The record is automatically created in edit mode if it does not already exist.

It doesn’t always make sense to add columns to a table if most of the records in the table will not contain any data in those columns. The autoInsertRight(array) will only create records when there is data linked to the main table by the primary key.

autoInsertRightOnclick(array)¶

This is similar to autoInsertRight(array) except a button has to be clicked.

events(array)¶

Occur after the join query to evaluate to column values in the map. Each event is named by the column reference. Each column event has the following properties and actions:

Mutable Join Scenarios¶