A WPDB wrapper and query builder library.

Related tags

Laravel db
Overview

DB

Tests Static Analysis

A WPDB wrapper and query builder library.

Installation

It's recommended that you install DB as a project dependency via Composer:

composer require stellarwp/db

We actually recommend that this library gets included in your project using Strauss.

Luckily, adding Strauss to your composer.json is only slightly more complicated than adding a typical dependency, so checkout our strauss docs.

Table of contents

DB

DB class is a static decorator for the $wpdb class, but it has a few methods that are exceptions to that. Methods DB::table() and DB::raw().

DB::table() is a static facade for the QueryBuilder class, and it accepts two string arguments, $tableName and $tableAlias.

Under the hood, DB::table() will create a new QueryBuilder instance, and it will use QueryBuilder::from method to set the table name. Calling QueryBuilder::from when using DB::table method will return an unexpected result. Basically, we are telling the QueryBuilder that we want to select data from two tables.

Important

When using DB::table(tableName) method, the tableName is prefixed with $wpdb->prefix. To bypass that, you can use DB::raw method which will tell QueryBuilder not to prefix the table name.

DB::table(DB::raw('posts'));

Select statements

Available methods - select / selectRaw / distinct

By using the QueryBuilder::select method, you can specify a custom SELECT statement for the query.

DB::table('posts')->select('ID', 'post_title', 'post_date');

Generated SQL

SELECT ID, post_title, post_date FROM wp_posts

You can also specify the column alias by providing an array [column, alias] to the QueryBuilder::select method.

DB::table('posts')->select(
    ['ID', 'post_id'],
    ['post_status', 'status'],
    ['post_date', 'createdAt']
);

Generated SQL:

SELECT ID AS post_id, post_status AS status, post_date AS createdAt FROM wp_posts

The distinct method allows you to force the query to return distinct results:

DB::table('posts')->select('post_status')->distinct();

You can also specify a custom SELECT statement with QueryBuilder::selectRaw method. This method accepts an optional array of bindings as its second argument.

DB::table('posts')
    ->select('ID')
    ->selectRaw('(SELECT ID from wp_posts WHERE post_status = %s) AS subscriptionId', 'give_subscription');

Generated SQL

SELECT ID, (SELECT ID from wp_posts WHERE post_status = 'give_subscription') AS subscriptionId FROM wp_posts

By default, all columns will be selected from a database table.

DB::table('posts');

Generated SQL

SELECT * FROM wp_posts

From clause

By using the QueryBuilder::from() method, you can specify a custom FROM clause for the query.

$builder = new QueryBuilder();
$builder->from('posts');

Set multiple FROM clauses

$builder = new QueryBuilder();
$builder->from('posts');
$builder->from('postmeta');

Generated SQL

SELECT * FROM wp_posts, wp_postmeta

Important

Table name is prefixed with $wpdb->prefix. To bypass that, you can use DB::raw method which will tell QueryBuilder not to prefix the table name.

$builder = new QueryBuilder();
$builder->from(DB::raw('posts'));

Joins

The Query Builder may also be used to add JOIN clauses to your queries.

Available methods - leftJoin / rightJoin / innerJoin / joinRaw / join

LEFT Join

LEFT JOIN clause.

DB::table('posts', 'donationsTable')
    ->select('donationsTable.*', 'metaTable.*')
    ->leftJoin('give_donationmeta', 'donationsTable.ID', 'metaTable.donation_id', 'metaTable');

Generated SQL

SELECT donationsTable.*, metaTable.* FROM wp_posts AS donationsTable LEFT JOIN wp_give_donationmeta metaTable ON donationsTable.ID = metaTable.donation_id

RIGHT Join

RIGHT JOIN clause.

DB::table('posts', 'donationsTable')
    ->select('donationsTable.*', 'metaTable.*')
    ->rightJoin('give_donationmeta', 'donationsTable.ID', 'metaTable.donation_id', 'metaTable');

Generated SQL

SELECT donationsTable.*, metaTable.* FROM wp_posts AS donationsTable RIGHT JOIN wp_give_donationmeta metaTable ON donationsTable.ID = metaTable.donation_id

INNER Join

INNER JOIN clause.

DB::table('posts', 'donationsTable')
    ->select('donationsTable.*', 'metaTable.*')
    ->innerJoin('give_donationmeta', 'donationsTable.ID', 'metaTable.donation_id', 'metaTable');

Generated SQL

SELECT donationsTable.*, metaTable.* FROM wp_posts AS donationsTable INNER JOIN wp_give_donationmeta metaTable ON donationsTable.ID = metaTable.donation_id

Join Raw

Insert a raw expression into query.

DB::table('posts', 'donationsTable')
    ->select('donationsTable.*', 'metaTable.*')
    ->joinRaw('LEFT JOIN give_donationmeta metaTable ON donationsTable.ID = metaTable.donation_id');

Generated SQL

SELECT donationsTable.*, metaTable.* FROM wp_posts AS donationsTable LEFT JOIN give_donationmeta metaTable ON donationsTable.ID = metaTable.donation_id

Advanced Join Clauses

The closure will receive a Give\Framework\QueryBuilder\JoinQueryBuilder instance

DB::table('posts')
    ->select('donationsTable.*', 'metaTable.*')
    ->join(function (JoinQueryBuilder $builder) {
        $builder
            ->leftJoin('give_donationmeta', 'metaTable')
            ->on('donationsTable.ID', 'metaTable.donation_id')
            ->andOn('metaTable.meta_key', 'some_key', $qoute = true);
    });

Generated SQL

SELECT donationsTable.*, metaTable.* FROM wp_posts LEFT JOIN wp_give_donationmeta metaTable ON donationsTable.ID = metaTable.donation_id AND metaTable.meta_key = 'some_key'

Unions

The Query Builder also provides a convenient method to "union" two or more queries together.

Available methods - union / unionAll

Union

$donations = DB::table('give_donations')->where('author_id', 10);

DB::table('give_subscriptions')
    ->select('ID')
    ->where('ID', 100, '>')
    ->union($donations);

Generated SQL:

SELECT ID FROM wp_give_subscriptions WHERE ID > '100' UNION SELECT * FROM wp_give_donations WHERE author_id = '10'

Where Clauses

You may use the Query Builder's where method to add WHERE clauses to the query.

Where

Available methods - where / orWhere

DB::table('posts')->where('ID', 5);

Generated SQL

SELECT * FROM wp_posts WHERE ID = '5'

Using where multiple times.

DB::table('posts')
    ->where('ID', 5)
    ->where('post_author', 10);

Generated SQL

SELECT * FROM wp_posts WHERE ID = '5' AND post_author = '10'

Where IN Clauses

Available methods - whereIn / orWhereIn / whereNotIn / orWhereNotIn

The QueryBuilder::whereIn method verifies that a given column's value is contained within the given array:

DB::table('posts')->whereIn('ID', [1, 2, 3]);

Generated SQL

SELECT * FROM wp_posts WHERE ID IN ('1','2','3')

You can also pass a closure as the second argument which will generate a subquery.

The closure will receive a Give\Framework\QueryBuilder\QueryBuilder instance

DB::table('posts')
    ->whereIn('ID', function (QueryBuilder $builder) {
        $builder
            ->select(['meta_value', 'donation_id'])
            ->from('give_donationmeta')
            ->where('meta_key', 'donation_id');
    });

Generated SQL

SELECT * FROM wp_posts WHERE ID IN (SELECT meta_value AS donation_id FROM wp_give_donationmeta WHERE meta_key = 'donation_id')

Where BETWEEN Clauses

The QueryBuilder::whereBetween method verifies that a column's value is between two values:

Available methods - whereBetween / orWhereBetween / whereNotBetween / orWhereNotBetween

DB::table('posts')->whereBetween('ID', 0, 100);

Generated SQL

SELECT * FROM wp_posts WHERE ID BETWEEN '0' AND '100'

Where LIKE Clauses

The QueryBuilder::whereLike method searches for a specified pattern in a column.

Available methods - whereLike / orWhereLike / whereNotLike / orWhereNotLike

DB::table('posts')->whereLike('post_title', 'Donation');

Generated SQL

SELECT * FROM wp_posts WHERE post_title LIKE '%Donation%'

Where IS NULL Clauses

The QueryBuilder::whereIsNull method verifies that a column's value is NULL

Available methods - whereIsNull / orWhereIsNull / whereIsNotNull / orWhereIsNotNull

DB::table('posts')->whereIsNull('post_author');

Generated SQL

SELECT * FROM wp_posts WHERE post_author IS NULL

Where EXISTS Clauses

The QueryBuilder::whereExists method allows you to write WHERE EXISTS SQL clauses. The QueryBuilder::whereExists method accepts a closure which will receive a QueryBuilder instance.

Available methods - whereExists / whereNotExists

DB::table('give_donationmeta')
    ->whereExists(function (QueryBuilder $builder) {
        $builder
            ->select(['meta_value', 'donation_id'])
            ->where('meta_key', 'donation_id');
    });

Generated SQL

SELECT * FROM wp_give_donationmeta WHERE EXISTS (SELECT meta_value AS donation_id WHERE meta_key = 'donation_id')

Subquery Where Clauses

Sometimes you may need to construct a WHERE clause that compares the results of a subquery to a given value.

DB::table('posts')
    ->where('post_author', function (QueryBuilder $builder) {
        $builder
            ->select(['meta_value', 'author_id'])
            ->from('postmeta')
            ->where('meta_key', 'donation_id')
            ->where('meta_value', 10);
    });

Generated SQL

SELECT * FROM wp_posts WHERE post_author = (SELECT meta_value AS author_id FROM wp_postmeta WHERE meta_key = 'donation_id' AND meta_value = '10')

Nested Where Clauses

Sometimes you may need to construct a WHERE clause that has nested WHERE clauses.

The closure will receive a Give\Framework\QueryBuilder\WhereQueryBuilder instance

DB::table('posts')
    ->where('post_author', 10)
    ->where(function (WhereQueryBuilder $builder) {
        $builder
            ->where('post_status', 'published')
            ->orWhere('post_status', 'donation')
            ->whereIn('ID', [1, 2, 3]);
    });

Generated SQL

SELECT * FROM wp_posts WHERE post_author = '10' AND ( post_status = 'published' OR post_status = 'donation' AND ID IN ('1','2','3'))

Ordering, Grouping, Limit & Offset

Ordering

The QueryBuilder::orderBy method allows you to sort the results of the query by a given column.

DB::table('posts')->orderBy('ID');

Generated SQL

SELECT * FROM wp_posts ORDER BY ID ASC

Sorting result by multiple columns

DB::table('posts')
    ->orderBy('ID')
    ->orderBy('post_date', 'DESC');

Generated SQL

SELECT * FROM wp_posts ORDER BY ID ASC, post_date DESC

Grouping

The QueryBuilder::groupBy and QueryBuilder::having* methods are used to group the query results.

Available methods - groupBy / having / orHaving / havingCount / orHavingCount / havingMin / orHavingMin / havingMax / orHavingMax / havingAvg / orHavingAvg / havingSum / orHavingSum / havingRaw

DB::table('posts')
    ->groupBy('id')
    ->having('id', '>', 10);

Generated SQL

SELECT * FROM wp_posts WHERE GROUP BY id HAVING 'id' > '10'

Limit & Offset

Limit the number of results returned from the query.

Available methods - limit / offset

DB::table('posts')
    ->limit(10)
    ->offset(20);

Generated SQL

SELECT * FROM wp_posts LIMIT 10 OFFSET 20

Special methods for working with meta tables

Query Builder has a few special methods for abstracting the work with meta tables.

attachMeta

attachMeta is used to include meta table meta_key column values as columns in the SELECT statement.

Under the hood QueryBuilder::attachMeta will add join clause for each defined meta_key column. And each column will be added in select statement as well, which means the meta columns will be returned in query result. Aliasing meta columns is recommended when using QueryBuilder::attachMeta method.

DB::table('posts')
    ->select(
        ['ID', 'id'],
        ['post_date', 'createdAt'],
        ['post_modified', 'updatedAt'],
        ['post_status', 'status'],
        ['post_parent', 'parentId']
    )
    ->attachMeta('give_donationmeta', 'ID', 'donation_id',
        ['_give_payment_total', 'amount'],
        ['_give_payment_currency', 'paymentCurrency'],
        ['_give_payment_gateway', 'paymentGateway'],
        ['_give_payment_donor_id', 'donorId'],
        ['_give_donor_billing_first_name', 'firstName'],
        ['_give_donor_billing_last_name', 'lastName'],
        ['_give_payment_donor_email', 'donorEmail'],
        ['subscription_id', 'subscriptionId']
    )
    ->leftJoin('give_donationmeta', 'ID', 'donationMeta.donation_id', 'donationMeta')
    ->where('post_type', 'give_payment')
    ->where('post_status', 'give_subscription')
    ->where('donationMeta.meta_key', 'subscription_id')
    ->where('donationMeta.meta_value', 1)
    ->orderBy('post_date', 'DESC');

Generated SQL:

SELECT ID                                         AS id,
       post_date                                  AS createdAt,
       post_modified                              AS updatedAt,
       post_status                                AS status,
       post_parent                                AS parentId,
       give_donationmeta_attach_meta_0.meta_value AS amount,
       give_donationmeta_attach_meta_1.meta_value AS paymentCurrency,
       give_donationmeta_attach_meta_2.meta_value AS paymentGateway,
       give_donationmeta_attach_meta_3.meta_value AS donorId,
       give_donationmeta_attach_meta_4.meta_value AS firstName,
       give_donationmeta_attach_meta_5.meta_value AS lastName,
       give_donationmeta_attach_meta_6.meta_value AS donorEmail,
       give_donationmeta_attach_meta_7.meta_value AS subscriptionId
FROM wp_posts
         LEFT JOIN wp_give_donationmeta give_donationmeta_attach_meta_0
                   ON ID = give_donationmeta_attach_meta_0.donation_id AND
                      give_donationmeta_attach_meta_0.meta_key = '_give_payment_total'
         LEFT JOIN wp_give_donationmeta give_donationmeta_attach_meta_1
                   ON ID = give_donationmeta_attach_meta_1.donation_id AND
                      give_donationmeta_attach_meta_1.meta_key = '_give_payment_currency'
         LEFT JOIN wp_give_donationmeta give_donationmeta_attach_meta_2
                   ON ID = give_donationmeta_attach_meta_2.donation_id AND
                      give_donationmeta_attach_meta_2.meta_key = '_give_payment_gateway'
         LEFT JOIN wp_give_donationmeta give_donationmeta_attach_meta_3
                   ON ID = give_donationmeta_attach_meta_3.donation_id AND
                      give_donationmeta_attach_meta_3.meta_key = '_give_payment_donor_id'
         LEFT JOIN wp_give_donationmeta give_donationmeta_attach_meta_4
                   ON ID = give_donationmeta_attach_meta_4.donation_id AND
                      give_donationmeta_attach_meta_4.meta_key = '_give_donor_billing_first_name'
         LEFT JOIN wp_give_donationmeta give_donationmeta_attach_meta_5
                   ON ID = give_donationmeta_attach_meta_5.donation_id AND
                      give_donationmeta_attach_meta_5.meta_key = '_give_donor_billing_last_name'
         LEFT JOIN wp_give_donationmeta give_donationmeta_attach_meta_6
                   ON ID = give_donationmeta_attach_meta_6.donation_id AND
                      give_donationmeta_attach_meta_6.meta_key = '_give_payment_donor_email'
         LEFT JOIN wp_give_donationmeta give_donationmeta_attach_meta_7
                   ON ID = give_donationmeta_attach_meta_7.donation_id AND
                      give_donationmeta_attach_meta_7.meta_key = 'subscription_id'
         LEFT JOIN wp_give_donationmeta donationMeta ON ID = donationMeta.donation_id
WHERE post_type = 'give_payment'
  AND post_status = 'give_subscription'
  AND donationMeta.meta_key = 'subscription_id'
  AND donationMeta.meta_value = '1'
ORDER BY post_date DESC

Returned result:

stdClass Object
(
    [id] => 93
    [createdAt] => 2022-02-21 00:00:00
    [updatedAt] => 2022-01-21 11:08:09
    [status] => give_subscription
    [parentId] => 92
    [amount] => 100.000000
    [paymentCurrency] => USD
    [paymentGateway] => manual
    [donorId] => 1
    [firstName] => Ante
    [lastName] => Laca
    [donorEmail] => [email protected]
    [subscriptionId] => 1
)

Fetch multiple instances of the same meta key

Sometimes we need to fetch multiple instances of the same meta key. This is possible by setting the third parameter to true, example ['additional_email', 'additionalEmails', true]

DB::table('give_donors')
  ->select(
      'id',
      'email',
      'name'
  )
  ->attachMeta(
      'give_donormeta',
      'id',
      'donor_id',
  	  ['additional_email', 'additionalEmails', true]
  );

Generated SQL:

SELECT id, email, name, GROUP_CONCAT(DISTINCT give_donormeta_attach_meta_0.meta_value) AS additionalEmails
FROM wp_give_donors
    LEFT JOIN wp_give_donormeta give_donormeta_attach_meta_0 ON id = give_donormeta_attach_meta_0.donor_id AND give_donormeta_attach_meta_0.meta_key = 'additional_email'
GROUP BY id

Returned result:

Instances with the same key, in this case additional_email, will be concatenated into JSON array string.

Array
(
    [0] => stdClass Object
        (
            [id] => 1
            [email] => [email protected]
            [name] => Bill Murray
            [additionalEmails] => ["[email protected]","[email protected]"]
        )

    [1] => stdClass Object
        (
            [id] => 2
            [email] => [email protected]
            [name] => Jon Waldstein
            [additionalEmails] => ["[email protected]","[email protected]","[email protected]"]
        )

    [2] => stdClass Object
        (
            [id] => 3
            [email] => [email protected]
            [name] => Ante laca
            [additionalEmails] =>
        )

)

configureMetaTable

By default, QueryBuilder::attachMeta will use meta_key, and meta_value as meta table column names, but that sometimes might not be the case.

With QueryBuilder::configureMetaTable you can define a custom meta_key and meta_value column names.

DB::table('posts')
    ->select(
        ['ID', 'id'],
        ['post_date', 'createdAt']
    )
    ->configureMetaTable(
        'give_donationmeta',
        'custom_meta_key',
        'custom_meta_value'
    )
    ->attachMeta(
        'give_donationmeta',
        'ID',
        'donation_id',
        ['_give_payment_total', 'amount']
    )
    ->leftJoin('give_donationmeta', 'ID', 'donationMeta.donation_id', 'donationMeta')
    ->where('post_type', 'give_payment')
    ->where('post_status', 'give_subscription')
    ->where('donationMeta.custom_meta_key', 'subscription_id')
    ->where('donationMeta.custom_meta_value', 1);

Generated SQL

SELECT ID AS id, post_date AS createdAt, give_donationmeta_attach_meta_0.custom_meta_value AS amount
FROM wp_posts
         LEFT JOIN wp_give_donationmeta give_donationmeta_attach_meta_0
                   ON ID = give_donationmeta_attach_meta_0.donation_id AND
                      give_donationmeta_attach_meta_0.custom_meta_key = '_give_payment_total'
         LEFT JOIN wp_give_donationmeta donationMeta ON ID = donationMeta.donation_id
WHERE post_type = 'give_payment'
  AND post_status = 'give_subscription'
  AND donationMeta.custom_meta_key = 'subscription_id'
  AND donationMeta.custom_meta_value = '1'

CRUD

Insert

The QueryBuilder also provides QueryBuilder::insert method that may be used to insert records into the database table.

DB::table('posts')
    ->insert([
        'post_title'   => 'Post Title',
        'post_author'  => 1,
        'post_content' => 'Post Content'
    ]);

Update

In addition to inserting records into the database, the QueryBuilder can also update existing records using the QueryBuilder::update method.

DB::table('posts')
    ->where('post_author', 1)
    ->update([
        'post_title'   => 'Post Title 2',
        'post_content' => 'Post Content 2'
    ]);

Delete

The QueryBuilder::delete method may be used to delete records from the table.

DB::table('posts')
    ->where('post_author', 1)
    ->delete();

Get

Available methods - get / getAll

Get single row

$post = DB::table('posts')->where('post_author', 1)->get();

Get all rows

$posts = DB::table('posts')->where('post_status', 'published')->getAll();

Aggregate Functions

The Query Builder also provides a variety of methods for retrieving aggregate values like count, sum, avg, min and max.

Count

$count = DB::table('posts')
    ->where('post_type', 'published')
    ->count();

Count rows where provided column is not null.

$count = DB::table('donations')->count('not_null_value_column');

Sum

$sum = DB::table('give_donationmeta')
    ->where('meta_key', 'donation_amount')
    ->sum('meta_value');

Avg

$avg = DB::table('give_donationmeta')
    ->where('meta_key', 'donation_amount')
    ->avg('meta_value');

Min

$min = DB::table('give_donationmeta')
    ->where('meta_key', 'donation_amount')
    ->min('meta_value');

Max

$max = DB::table('give_donationmeta')
    ->where('meta_key', 'donation_amount')
    ->max('meta_value');

Acknowledgements

Props to the GiveWP team for creating this library!

Comments
  • Add a config class to provide some customizability

    Add a config class to provide some customizability

    This PR adds a StellarWP\DB\Config class that is merely a string store, so it is all a pile of static shenanigans. Added docs on initialization and configuration.

    Additionally, I tacked on the work to convert tric to slic.

    opened by borkweb 4
  • Adjust signatures of __callStatic() methods to match wpdb

    Adjust signatures of __callStatic() methods to match wpdb

    While the docs for wpdb::insert and wpdb::replace claim array|string for the $format parameter, the default is null. This PR adds null as a valid argument value.

    opened by borkweb 0
  • Feature: Add an Upsert method

    Feature: Add an Upsert method

    Often times it's useful to update existing rows if present and insert if missing. An example of this in WordPress is the update_post_meta function. It would be useful to be able to do something like this:

    DB::table('flights')->upsert(
        [
            ['departure' => 'Oakland', 'destination' => 'San Diego', 'price' => 99],
            ['departure' => 'Chicago', 'destination' => 'New York', 'price' => 150]
        ],
        ['departure', 'destination'],
        ['price']
    );
    

    Arguments:

    1. The records to insert or update
    2. The column(s) that uniquely identify whether the record already exists
    3. The column(s) to update if the record does exist

    Under the hood this will probably use the ON DUPLICATE KEY UPDATE SQL syntax, as such it will be important that the identifier column(s) are either "primary" or "unique".

    enhancement 
    opened by JasonTheAdams 0
  • Feature: use plain SQL to run the Query Builder update method

    Feature: use plain SQL to run the Query Builder update method

    Right now the QueryBuilder::update() method uses wpdb::update() under the hood. This limits us to what that method is capable of. For example, want a WHERE X IN (SELECT...) clause in your update method? Too bad. 😭

    By switching to running simple SQL under the hood we make more complex conditions possible without much effort. The WHERE clause is simply converted to SQL and tacked onto the end, getting the full power of the Query Builder behind complex update queries. Furthermore, update can also receive:

    • A Query
    • ORDER BY clause
    • LIMIT clause
    enhancement 
    opened by JasonTheAdams 0
  • Feature: use plain SQL to run the Query Builder delete method

    Feature: use plain SQL to run the Query Builder delete method

    Right now the QueryBuilder::delete() method uses wpdb::delete() under the hood. This limits us to what that method is capable of. For example, want a WHERE X IN (SELECT...) clause in your delete method? Too bad. 😭

    By switching to running simple SQL under the hood we make more complex conditions possible without much effort. The WHERE clause is simply converted to SQL and tacked onto the end, getting the full power of the Query Builder behind complex delete queries.

    enhancement 
    opened by JasonTheAdams 0
  • Feature: Switch Query Builder insert method to use raw SQL and support multi-row inserts

    Feature: Switch Query Builder insert method to use raw SQL and support multi-row inserts

    Currently the Query Builder user wpdb::insert() under the hood to run the insert. This is lame for a couple reasons:

    1. You can't really do anything particularly complex as we're limited to what that function supports
    2. For example, you can only insert a single row at a time... Have 20 rows to insert? Too bad. Do it 20 times. 😭

    Let's just ditch wpdb and build the SQL ourselves. We don't need them! I think as a good first step the following should be possible:

    DB::table('post_meta')
    	->insert([
    		['post_id' => 1, 'meta_key' => 'foo', 'meta_value' => 'bar'],
    		['post_id' => 1, 'meta_key' => 'foo2', 'meta_value' => 'bar2'],
    		['post_id' => 1, 'meta_key' => 'foo3', 'meta_value' => 'bar3'],
    	]);
    

    Currently the insert signature contains a $format parameter. I'm inclined to just drop that and infer from the value type what it should be: strings should be strings, numbers should be numbers, and so forth. Crazy stuff! Dropping the last parameter shouldn't break anything since passing additional parameters in PHP doesn't throw an error.

    enhancement 
    opened by JasonTheAdams 0
  • Feature: Add an insertInto method to the Query Builder

    Feature: Add an insertInto method to the Query Builder

    SQL provides the ability to Insert Into a table using the results of another query. This is incredibly useful, especially as a means of migration or conditionally inserting rows.

    Take the following example:

    global $wpdb;
    
    $donationMetaTable = "{$wpdb->prefix}give_donationmeta";
    $subscriptionTable = "{$wpdb->prefix}give_subscriptions";
    
    $wpdb->query(
        "
            INSERT INTO $donationMetaTable (donation_id, meta_key, meta_value)
            SELECT
                p.ID,
                'subscription_id',
                s.id
            FROM
                $wpdb->posts AS p
                LEFT JOIN $donationMetaTable AS dm1 ON dm1.donation_id = p.ID
                    AND dm1.meta_key = 'subscription_id'
                LEFT JOIN $donationMetaTable AS dm2 ON dm2.donation_id = p.ID
                    AND dm2.meta_key = '_give_subscription_payment'
                LEFT JOIN $subscriptionTable AS s ON s.parent_payment_id = p.ID
            WHERE
                dm2.meta_value = '1'
                AND dm1.meta_value IS NULL
        "
    );
    

    This inserts subscription_id meta into the donation meta table, but only for cases where it's missing. So if this query were to run twice the second time it runs it would do nothing! ✨

    It would be awesome to have something like the following:

    DB::table('donationmeta')
      ->insertInto(
        DB::table('posts')->select()->where(); // set up query
      );
    
    enhancement 
    opened by JasonTheAdams 0
Releases(1.0.3)
  • 1.0.3(Nov 22, 2022)

  • 1.0.2(Nov 22, 2022)

    Changes

    • Fix - Adjust DB::insert(), DB::delete(), DB::update(), and DB::replace() signature to match wpdb's supported method signatures.
    • Fix - Adjust DB::get_var(), DB::get_col(), and DB::get_results() signature of first arg to match wpdb's signature.
    Source code(tar.gz)
    Source code(zip)
  • 1.0.1(Sep 29, 2022)

    Changes

    • Added a Config class to handle overrides of the DatabaseQueryException and addition of a hook prefix.
    • Added tests for Config
    • More documentation
    Source code(tar.gz)
    Source code(zip)
  • 1.0.0(Aug 28, 2022)

Owner
StellarWP
StellarWP stands behind leading WordPress plugins.
StellarWP
A MongoDB based Eloquent model and Query builder for Laravel (Moloquent)

Laravel MongoDB This package adds functionalities to the Eloquent model and Query builder for MongoDB, using the original Laravel API. This library ex

Jens Segers 6.3k Jan 7, 2023
A DynamoDB based Eloquent model and Query builder for Laravel.

Laravel DynamoDB A DynamoDB based Eloquent model and Query builder for Laravel. You can find an example implementation in kitar/simplechat. Motivation

Satoshi Kita 146 Jan 2, 2023
Provides a Eloquent query builder for Laravel or Lumen

This package provides an advanced filter for Laravel or Lumen model based on incoming requets.

M.Fouladgar 484 Jan 4, 2023
A simple to use query builder for the jQuery QueryBuilder plugin for use with Laravel.

QueryBuilderParser Status Label Status Value Build Insights Code Climate Test Coverage QueryBuilderParser is designed mainly to be used inside Laravel

Tim Groeneveld 149 Nov 11, 2022
A Laravel wrapper for spatie/dns. Allows to query and validate DNS records.

A Laravel wrapper for spatie/dns. Allows to query and validate DNS records.

Astrotomic 22 Nov 17, 2022
Generate and autoload custom Helpers, Builder Scope, Service class, Trait

laravel-make-extender Generate and autoload custom helpers, It can generate multilevel helpers in the context of the directory. Generate Service class

Limewell 30 Dec 24, 2022
this is cv-builder project in php and ajax

download the project make a database has name cv_builder extract the project in xampp/htdocs/ open the project localhost/cv_builder/index.php or use t

Hilal ahmad 6 Jun 21, 2022
A powerful form builder, for Laravel and other frameworks (stand-alone too)

Former A Laravelish way to create and format forms Former outputs form elements in HTML compatible with your favorite CSS framework (Bootstrap and Fou

null 1.3k Dec 22, 2022
A Laravel Admin Starter project with Page Builder, Roles, Impersonation, Analytics, Blog, News, Banners, FAQ, Testimonials and more

Laravel CMS Starter Project A Laravel CMS Starter project with AdminLTE theme and core features. Preview project here User: [email protected]

Ben-Piet O'Callaghan 306 Nov 28, 2022
HydePHP - Elegant and Powerful Static App Builder

HydePHP - Elegant and Powerful Static App Builder Make static websites, blogs, and documentation pages with the tools you already know and love. About

HydePHP 31 Dec 29, 2022
The query filter bundle allows you to filter data from QueryBuilder and the Database.

The query filter bundle allows you to filter data from QueryBuilder and the Database. you can filter multiple columns at the same time and also you can filter relation fields with two-level deep and without any join in your query builder.

Bugloos 15 Dec 29, 2022
Laravel Form builder for version 5+!

Laravel 5 form builder Form builder for Laravel 5 inspired by Symfony's form builder. With help of Laravels FormBuilder class creates forms that can b

Kristijan Husak 1.7k Dec 31, 2022
Reactive Form Builder for Vue.js with Laravel Support

Dynamic Form Builder for Laravel with Vue.js Create even the most complex forms with ease, using two-sided validation, eloquent, nested elements, cond

Laraform 340 Dec 31, 2022
Laravel Mysql Spatial Builder Extension

magutti-spatial V2 Laravel Builder Mysql Spatial Extension Laravel Builder extensions to calculate distances between two Spatial points using Mysql na

Marco Asperti 4 Oct 2, 2022
A TWBS menu builder for Laravel

Laravel Menu Builder A menu builder for Laravel 4-5 using Bootstrap's markup. Документация на Русском Note that this package is shipped with no styles

Alexander Kalnoy 24 Nov 29, 2022
Laravel API architecture builder based on artisan commands.

??‍?? API-Formula Laravel API architecture builder based on artisan commands. This package provides a nice and fluent way to generate combined control

Krševan Lisica 1 Jan 16, 2022
Builder - A handful of tools for Rapid Laravel Development

Grafite Builder Grafite has archived this project and no longer supports or develops the code. We recommend using only as a source of ideas for your o

Grafite Inc 997 Dec 22, 2022
🎨 Free custom elements for the WordPress plugin Oxygen Builder.

?? Custom Elements for Oxygen Builder Free custom elements for the WordPress plugin Oxygen Builder. If you find the elements useful, click on the star

Simon Vidman 62 Dec 29, 2022
A demo of how to use filament/forms to build a user-facing Form Builder which stores fields in JSON.

About Laravel Laravel is a web application framework with expressive, elegant syntax. We believe development must be an enjoyable and creative experie

Dan Harrin 41 Dec 24, 2022