Feature: Add an insertInto method to the Query Builder

[JasonTheAdams]

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

[kjohnson]

Discussion Summary

We discussed inverting the relationship between the INSERT INTO query and the related sub-query.

Leveraging the Query Builder we can define the SELECT as the main logic of the query which can then be inserted into another table, which more closely matches the application logic.

DB::table('posts')

    // Subquery
    ->select(['ID', 'post_id'])
    ->selectRaw('(SELECT "postTitle") as meta_key')
    ->select(['post_title', 'meta_value'])

    // Insert Into
    ->insertInto('postmeta', ['post_id', 'meta_key', 'meta_value']);

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
    SELECT
        ID AS post_id,
        (SELECT "postTitle") as meta_key,
        post_title AS meta_value
    FROM wp_posts;