Persistence Methods

While this library is mostly aimed at writing complex queries more cleanly, you it also has a series of methods for adding/editing and removing rows from a table.

insert()

/**
 * @param array<int|string, mixed|mixed[]> $data either key=>value array for single or array of arrays for bulk
 *
 * @return int|int[]|mixed|null can return a single row id, array of row ids, null (for failed) or any other value short circuited from event
 */
public function insert($data)

You can add either single rows or groups of rows.

Single Row

// Using un prepared values.
$id = QB::table('foo')->insert(['col1' => 'val1', 'col2' => 'val2']);
var_dump($id); // This would return the primary key for the row entered.

// Using bindings to ensure values are passed through wpdb::prepare()
$id = QB::table('foo')->insert([
    'col1' => Binding::asString($val1), 
    'col2' => new Binding($val2, Binding::STRING)
]);
// INSERT INTO foo (col1,col2) VALUES ('$val1','$val2')


// You can also pass in RAW values if you need to call MYSQL functions or constants.
$id = QB::table('foo')->insert([
    'col1' => Raw::val('NOW()'), 
    'col2' => new Raw('CURRENT_TIMESTAMP')
]);
// INSERT INTO foo (col1,col2) VALUES (NOW(),CURRENT_TIMESTAMP)

Multiple Rows

insertOnDuplicateKey()

It is possible to make of ON DUPLICATE KEY UPDATE to handle attempting to create a value in which any KEY(PRIMARY or UNIQUE) already exists.

/**
 * @param array<string, mixed> $data
 * @return QueryBuilderHandler
 */
public function onDuplicateKeyUpdate(array $data): QueryBuilderHandler

example

id	email	counter
1	[email protected]	10
2	[email protected]	15

id = PRIMARY KEY
email = UNIQUE KEY

// Add to counter
$count = 5;

QB::table('mock_unique')
    ->onDuplicateKeyUpdate([
        'email' => '[email protected]',
        'counter' => (QB::table('mock_unique')->find('[email protected]', 'email')->counter + $count)
    ])
    ->insert([
        'email' => '[email protected]',
        'counter' => $count
    ]);

// RESULT :: {id: 1, email: '[email protected]', counter: 15}

In the above example, as [email protected] is already in the DB (with email as unique column), we add 5 to current count (10), rather insert as 5.

---

update()

You can update any number of columns, based on the conditions defined in the query.

/**
 * @param array<string, mixed> $data
 *
 * @return int|null Number of row effected, null for none.
 */
public function update(array $data): ?int

example

id	email	campaign	sent
1	[email protected]	silver	false
2	[email protected]	silver	false
3	[email protected]	gold	false
4	[email protected]	bronze	false

// All none bronze campaigns.
$updated = QB::table('subscribers')
    ->where('campaign', '!=', 'bronze')
    ->update(['sent' => true]);

// Returns the row updated count = 3 (1,2,3);
var_dump($updated); 

updateOrInsert()

This allows for the passing a set of criteria which if matched will update the passed values, or create.

/**
 * @param array<string, mixed> $attributes Conditions to check
 * @param array<string, mixed> $values     Values to add/update
 *
 * @return int|int[]|null will return row id for insert and null for success/fail on update
 */
public function updateOrInsert(array $attributes, array $values = [])

Example

id	email	score
1	[email protected]	10
2	[email protected]	15

// Update with matching
QB::table('scores')
    ->updateOrInsert(['email' => '[email protected]'], ['score' => 20, 'email' => '[email protected]']);
// Score for [email protected] would now be 20

// Insert as no matching
QB::table('scores')
    ->updateOrInsert(['email' => '[email protected]'], ['score' => 30, 'email' => '[email protected]']);
// Would insert ['email' => '[email protected]', 'score' => 30]

The attributes passed are treated as where($key => $value), so this could update multiple rows. Works best where the attributes are KEYs

---

delete()

Like update, it is possible to create a query with a set of conditions and have resulting rows deleted.

/**
 * @return mixed number of rows effected or shortcircuited response
 */
public function delete()

Example

id	email	campaign	sent
1	[email protected]	silver	true
2	[email protected]	silver	false
3	[email protected]	gold	true
4	[email protected]	bronze	true

$deleted = QB::table('subscribers')
    ->where('campaign', '!=', 'gold')
    ->where('sent', true) // AND WHERE
    ->delete();

// Returns the row deleted count = 2 (1,4);
var_dump($updated);