Query Builder

Introduction

WP Social Ninja uses an Eloquent-based ORM, compatible with Laravel's query builder. You can interact with the database through Model classes or through the query builder directly via the global wpsrDb() helper.

TIP

If you know Laravel's query builder, you already know this one. wpsrDb()->table('...') returns a fluent builder over any table (including WordPress core tables), while models give you the same builder scoped to a wpsr_ table.

Using a model

use WPSocialReviews\App\Models\Review;

$reviews = Review::query()
    ->where('review_approved', 1)
    ->whereBetween('rating', [4, 5])
    ->when($platform, function ($query) use ($platform) {
        return $query->where('platform_name', $platform);
    })
    ->orderBy('review_time', 'DESC')
    ->get();

Using the query builder directly

$rows = wpsrDb()
    ->table('wpsr_reviews')
    ->select(['reviewer_name', 'rating', 'platform_name'])
    ->where('review_approved', 1)
    ->orderBy('id', 'DESC')
    ->get();

Retrieving Results

All rows

$reviews = wpsrDb()->table('wpsr_reviews')->get();

foreach ($reviews as $review) {
    echo $review->reviewer_name;
}

A single row / column

// A single row
$review = wpsrDb()->table('wpsr_reviews')->where('platform_name', 'google')->first();

// A single value
$rating = wpsrDb()->table('wpsr_reviews')->where('id', 5)->value('rating');

A list of column values

$ids = wpsrDb()->table('wpsr_reviews')->pluck('id');

// keyed by another column
$namesById = wpsrDb()->table('wpsr_reviews')->pluck('reviewer_name', 'id');

Chunking large result sets

Process thousands of rows without loading them all into memory:

wpsrDb()->table('wpsr_reviews')->orderBy('id')->chunk(100, function ($reviews) {
    foreach ($reviews as $review) {
        // process each review
    }
    // return false; // stop further chunks
});

Aggregates

$total   = wpsrDb()->table('wpsr_reviews')->count();
$maxRate = wpsrDb()->table('wpsr_reviews')->max('rating');
$avgRate = wpsrDb()->table('wpsr_reviews')->where('platform_name', 'google')->avg('rating');

Determining if records exist

$hasApproved = wpsrDb()->table('wpsr_reviews')->where('review_approved', 1)->exists();

Selects

// Specific columns (with alias)
$rows = wpsrDb()->table('wpsr_reviews')
    ->select('reviewer_name', 'rating as stars')
    ->get();

// Distinct
$platforms = wpsrDb()->table('wpsr_reviews')->select('platform_name')->distinct()->get();

// Add to an existing select
$query = wpsrDb()->table('wpsr_reviews')->select('reviewer_name');
$rows  = $query->addSelect('rating')->get();

Where Clauses

Simple where

$rows = wpsrDb()->table('wpsr_reviews')->where('rating', 5)->get();

// With an operator
$rows = wpsrDb()->table('wpsr_reviews')->where('rating', '>=', 4)->get();

// Multiple (AND)
$rows = wpsrDb()->table('wpsr_reviews')
    ->where('platform_name', 'google')
    ->where('review_approved', 1)
    ->get();

Or statements

$rows = wpsrDb()->table('wpsr_reviews')
    ->where('platform_name', 'google')
    ->orWhere('platform_name', 'facebook')
    ->get();

Additional where clauses

// IN / NOT IN
wpsrDb()->table('wpsr_reviews')->whereIn('platform_name', ['google', 'yelp'])->get();
wpsrDb()->table('wpsr_reviews')->whereNotIn('rating', [1, 2])->get();

// BETWEEN
wpsrDb()->table('wpsr_reviews')->whereBetween('rating', [4, 5])->get();

// NULL checks
wpsrDb()->table('wpsr_reviews')->whereNull('reviewer_email')->get();
wpsrDb()->table('wpsr_reviews')->whereNotNull('review_time')->get();

// LIKE
wpsrDb()->table('wpsr_reviews')->where('reviewer_text', 'LIKE', '%great%')->get();

Parameter grouping

Wrap complex OR logic in a closure to keep it correctly parenthesized:

$rows = wpsrDb()->table('wpsr_reviews')
    ->where('review_approved', 1)
    ->where(function ($query) {
        $query->where('platform_name', 'google')
              ->orWhere('platform_name', 'facebook');
    })
    ->get();

Ordering, Grouping, Limit & Offset

$rows = wpsrDb()->table('wpsr_reviews')
    ->where('review_approved', 1)
    ->orderBy('review_time', 'DESC')
    ->groupBy('platform_name')
    ->limit(20)
    ->offset(40) // or ->skip(40)->take(20)
    ->get();

Conditional Clauses

when() applies constraints only when a value is truthy — no manual if needed:

$rows = wpsrDb()->table('wpsr_reviews')
    ->when($minRating, function ($query) use ($minRating) {
        return $query->where('rating', '>=', $minRating);
    })
    ->when($search, function ($query) use ($search) {
        return $query->where('reviewer_text', 'LIKE', "%{$search}%");
    })
    ->get();

Inserts

// Single row → returns the inserted ID
$id = wpsrDb()->table('wpsr_reviews')->insertGetId([
    'platform_name'   => 'custom',
    'reviewer_name'   => 'Jane Doe',
    'rating'          => 5,
    'review_approved' => 1,
]);

// Multiple rows
wpsrDb()->table('wpsr_reviews')->insert([
    ['platform_name' => 'custom', 'reviewer_name' => 'A', 'rating' => 5],
    ['platform_name' => 'custom', 'reviewer_name' => 'B', 'rating' => 4],
]);

Updates

$affected = wpsrDb()->table('wpsr_reviews')
    ->where('id', 10)
    ->update(['review_approved' => 1]);

Increment & Decrement

Useful for the cache failure counter:

wpsrDb()->table('wpsr_caches')->where('id', $cacheId)->increment('failed_count');
wpsrDb()->table('wpsr_caches')->where('id', $cacheId)->decrement('failed_count', 1);

Deletes

// Delete matching rows
wpsrDb()->table('wpsr_reviews')->where('review_approved', 0)->delete();

// Empty a table
wpsrDb()->table('wpsr_caches')->truncate();

WP Social Ninja Query Examples

Approved reviews for a template's platforms

$reviews = wpsrDb()->table('wpsr_reviews')
    ->whereIn('platform_name', ['google', 'facebook'])
    ->where('review_approved', 1)
    ->where('rating', '>=', 4)
    ->orderBy('review_time', 'DESC')
    ->limit(20)
    ->get();

Expired cache entries

$expired = wpsrDb()->table('wpsr_caches')
    ->where('platform', 'instagram')
    ->where('expiration', '<', current_time('mysql'))
    ->get();

Duplicate-email check (respecting the one-review-per-email rule)

$exists = wpsrDb()->table('wpsr_reviews')
    ->where('source_id', $sourceId)
    ->where('reviewer_email', $email)
    ->exists();

Rating distribution for a source

$distribution = wpsrDb()->table('wpsr_reviews')
    ->select('rating')
    ->selectRaw('COUNT(*) as total')
    ->where('source_id', $sourceId)
    ->where('review_approved', 1)
    ->groupBy('rating')
    ->orderBy('rating', 'DESC')
    ->get();

Always prefix and validate

Query the wpsr_ tables by their unprefixed name (wpsrDb()->table('wpsr_reviews')) — the builder applies the WordPress table prefix for you. And validate every ID/value before it reaches a query, per the coding conventions.