PHPackages                             visi/buddy-plugin-subquery-resolver - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Search & Filtering](/categories/search)
4. /
5. visi/buddy-plugin-subquery-resolver

ActiveLibrary[Search & Filtering](/categories/search)

visi/buddy-plugin-subquery-resolver
===================================

Manticore Search Buddy plugin to resolve subqueries by executing them separately and injecting results

v1.1(2mo ago)028GPL-2.0-or-laterPHPPHP >=8.1

Since Mar 31Pushed 2mo agoCompare

[ Source](https://github.com/V-i-S-i/manticore-buddy-plugin-subquery-resolver)[ Packagist](https://packagist.org/packages/visi/buddy-plugin-subquery-resolver)[ RSS](/packages/visi-buddy-plugin-subquery-resolver/feed)WikiDiscussions main Synced today

READMEChangelogDependencies (1)Versions (5)Used By (0)

Manticore Buddy Subquery Resolver Plugin
========================================

[](#manticore-buddy-subquery-resolver-plugin)

A Manticore Buddy plugin that enables SQL subquery support for IN/NOT IN clauses in Manticore Search.

Overview
--------

[](#overview)

Manticore Search does not natively support subqueries in WHERE clauses. This plugin intercepts such queries, executes the subqueries separately, and injects the results into the main query - completely transparently to the client.

**Supported subquery types:**

- **IN/NOT IN clauses**: `WHERE id IN (SELECT ...)`
- **Comparison operators**: `WHERE date > (SELECT ...)` using =, !=, <>, <, >, <=, >=

**Note:** Manticore already supports subqueries in FROM clauses (derived tables). This plugin only handles WHERE clause subqueries.

Features
--------

[](#features)

- Automatic detection and handling of IN/NOT IN clause subqueries
- Comparison operator subqueries (=, !=, <>, <, >, <=, >=)
- Multiple subqueries in a single query
- Nested subqueries (subqueries within subqueries, up to 10 levels)
- Transparent subquery execution and result injection
- Handles empty result sets gracefully (replaces with NULL)
- Supports Manticore MVA (multi-value attributes)
- **Subquery deduplication**: identical subqueries appearing multiple times are executed only once
- **Same-table merge**: subqueries from the same table with the same filters but different columns are merged into a single multi-column query
- No client-side changes required
- Default result limit of 20,000 rows per subquery with error if limit is reached
- Override limit per subquery with an explicit LIMIT clause

Quick Example
-------------

[](#quick-example)

**Before (fails in Manticore):**

```
-- IN clause subquery
SELECT id FROM rt_today_lt
WHERE ANY(keyword_id) IN (
  SELECT id FROM rt_keywords_customers WHERE customers = 3408
);
-- ERROR: P01: syntax error, unexpected SELECT...

-- Comparison operator subquery
SELECT * FROM rt_today_lt
WHERE date_added > (SELECT archive_start FROM customers WHERE id = 3408);
-- ERROR: P01: syntax error, unexpected SELECT...
```

**After (works with plugin):**

```
-- Both queries now work perfectly!
SELECT id FROM rt_today_lt
WHERE ANY(keyword_id) IN (
  SELECT id FROM rt_keywords_customers WHERE customers = 3408
);
-- ✅ Returns results successfully

SELECT * FROM rt_today_lt
WHERE date_added > (SELECT archive_start FROM customers WHERE id = 3408);
-- ✅ Returns results successfully
```

How It Works
------------

[](#how-it-works)

### Basic IN Clause Subquery Resolution

[](#basic-in-clause-subquery-resolution)

1. Plugin detects the subquery pattern: `IN (SELECT ...)`
2. Extracts and executes: `SELECT id FROM rt_keywords_customers WHERE customers = 3408`
3. Gets results, e.g., `[1, 5, 9, 12, ...]`
4. Rewrites query: `SELECT id FROM rt_today_lt WHERE ANY(keyword_id) IN (1, 5, 9, 12, ...)`
5. Executes final query and returns results

### Comparison Operator Subquery Resolution

[](#comparison-operator-subquery-resolution)

1. Plugin detects comparison pattern: `> (SELECT ...)`, `= (SELECT ...)`, etc.
2. Extracts and executes: `SELECT archive_start FROM customers WHERE id = 3408`
3. Gets scalar result, e.g., `2024-01-15`
4. Rewrites query: `SELECT * FROM rt_today_lt WHERE date_added > '2024-01-15'`
5. Executes final query and returns results

**Note:** For comparison operators, only the **first value** is used if multiple rows are returned.

### Nested Subquery Resolution

[](#nested-subquery-resolution)

For nested subqueries, the plugin uses **iterative layer-by-layer resolution**:

1. **Iteration 1**: Finds and executes all innermost subqueries (those without further nesting)
2. Replaces them with their results
3. **Iteration 2**: The previously nested subqueries are now exposed and get resolved
4. **Repeat** until no more subqueries remain (up to 10 levels deep)
5. Execute the fully resolved final query

**Example:**

```
-- Original query with 3 levels of nesting:
WHERE product_id IN (SELECT id FROM products WHERE category_id IN (SELECT id FROM categories WHERE group_id IN (SELECT id FROM groups WHERE name = 'Tech')))

-- After iteration 1 (innermost resolved):
WHERE product_id IN (SELECT id FROM products WHERE category_id IN (SELECT id FROM categories WHERE group_id IN (5, 12, 18)))

-- After iteration 2 (middle level resolved):
WHERE product_id IN (SELECT id FROM products WHERE category_id IN (101, 102, 103, 104))

-- After iteration 3 (outermost resolved):
WHERE product_id IN (1001, 1002, 1003, 1004, 1005)

-- Final query executed
```

### Subquery Deduplication

[](#subquery-deduplication)

When the same subquery appears multiple times in a query, the plugin executes it only once and reuses the cached result for all occurrences. This is common when the same filter is used in multiple places:

```
SELECT id FROM mediamonitoring_all
WHERE aid IN (SELECT id FROM articles WHERE status = 'active')
   OR bid IN (SELECT id FROM articles WHERE status = 'active');
-- The subquery is executed only once, result injected in both positions
```

Deduplication works across all iteration levels — if a subquery resolved in iteration 1 appears again in iteration 2, it is served from cache.

### Same-Table Subquery Merging

[](#same-table-subquery-merging)

When multiple subqueries select different columns from the same table with the same filters, the plugin merges them into a single multi-column query:

```
-- Original: 4 separate subqueries hitting the same table
SELECT id FROM mediamonitoring_all
WHERE ANY(keyword_id) IN (SELECT keyword_id FROM customers WHERE id = 3408)
  AND feed_id IN (SELECT feed_id FROM customers WHERE id = 3408)
  AND date_added > (SELECT archive_start FROM customers WHERE id = 3408)
  AND date_added &1 | grep "extra: subquery-resolver"
# Expected: [BUDDY]   extra: subquery-resolver
```

**Note:** The `plugin_dir` setting ensures plugins persist across container restarts. Without it, plugins would be lost on restart.

**See [INSTALLATION.md](INSTALLATION.md) for detailed installation instructions and troubleshooting.**

Usage Examples
--------------

[](#usage-examples)

### Basic IN Clause Subquery

[](#basic-in-clause-subquery)

```
SELECT * FROM products
WHERE id IN (SELECT product_id FROM orders WHERE customer_id = 123);
```

### NOT IN Subquery

[](#not-in-subquery)

```
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM banned_users);
```

### Comparison Operator Subqueries

[](#comparison-operator-subqueries)

```
-- Greater than
SELECT * FROM rt_today_lt
WHERE date_added > (SELECT archive_start FROM customers WHERE id = 3408);

-- Equals
SELECT * FROM products
WHERE category_id = (SELECT id FROM categories WHERE name = 'Electronics');

-- Less than or equal
SELECT * FROM orders
WHERE total  1000);
```

### Empty Result Handling

[](#empty-result-handling)

```
-- If subquery returns nothing, query returns empty set
SELECT * FROM orders
WHERE product_id IN (SELECT id FROM products WHERE price  (SELECT archive_start FROM customers WHERE id = 3408);

-- Works with all comparison operators
SELECT * FROM products
WHERE price = (SELECT MIN(threshold) FROM inventory_settings)
  AND created_at = (SELECT MAX(updated_at) FROM product_updates WHERE status = 'approved');

-- Can be nested too
SELECT * FROM orders
WHERE total > (
  SELECT AVG(total) FROM orders
  WHERE customer_id IN (SELECT id FROM customers WHERE tier = 'premium')
);
```

Supported Features
------------------

[](#supported-features)

✅ **Supported:**

- **IN/NOT IN clause subqueries** in WHERE clause
- **Comparison operator subqueries** (=, !=, <>, <, >, <=, >=) returning scalar values
- **Multiple subqueries in one query**
- **Nested subqueries** (subqueries within subqueries, up to 10 levels deep)
- **Mixed subquery types** (IN and comparison operators in same query)
- Simple SELECT subqueries returning a single column
- MVA (multi-value attribute) fields (for IN clauses)
- Empty result sets (replaced with NULL)
- Numeric and string values

❌ **Not Supported (yet):**

- Subqueries in HAVING, ORDER BY, SELECT columns, etc.
- Correlated subqueries (subqueries that reference outer query columns)
- Multi-column subqueries

⚠️ **Not Needed (Manticore already supports):**

- FROM clause subqueries (derived tables): `SELECT * FROM (SELECT ...) AS t`

Real-World Example
------------------

[](#real-world-example)

Here's a comprehensive example demonstrating all plugin features working together:

```
-- Complex query with multiple subquery types and nesting
SELECT
  o.id,
  o.customer_id,
  o.total,
  o.created_at
FROM orders o
WHERE
  -- IN clause with nested subquery
  o.customer_id IN (
    SELECT c.id FROM customers c
    WHERE c.tier = 'premium'
      AND c.country_id IN (SELECT id FROM countries WHERE region = 'EU')
  )
  -- Comparison operator subquery
  AND o.created_at > (SELECT last_promotion_date FROM settings WHERE key = 'promo_2024')
  -- NOT IN clause subquery
  AND o.status NOT IN (SELECT code FROM order_statuses WHERE is_cancelled = 1)
  -- Another comparison subquery with nested IN
  AND o.total >= (
    SELECT MIN(min_order_total) FROM tier_settings
    WHERE tier_id IN (SELECT id FROM tiers WHERE name = 'premium')
  );
```

**What happens:**

1. Plugin detects 5 subqueries (2 nested levels)
2. **Iteration 1** resolves innermost subqueries:
    - `SELECT id FROM countries WHERE region = 'EU'` → `(1, 5, 12)`
    - `SELECT id FROM tiers WHERE name = 'premium'` → `(3)`
3. **Iteration 2** resolves mid-level subqueries:
    - `SELECT c.id FROM customers WHERE ... country_id IN (1, 5, 12)` → `(101, 205, 308)`
    - `SELECT MIN(...) FROM tier_settings WHERE tier_id IN (3)` → `500.00`
    - `SELECT last_promotion_date FROM settings ...` → `'2024-01-15'`
    - `SELECT code FROM order_statuses ...` → `('CANC', 'REFUND')`
4. **Final query** executes with all values resolved:

```
SELECT o.id, o.customer_id, o.total, o.created_at
FROM orders o
WHERE o.customer_id IN (101, 205, 308)
  AND o.created_at > '2024-01-15'
  AND o.status NOT IN ('CANC', 'REFUND')
  AND o.total >= 500.00;
```

Testing
-------

[](#testing)

### Verify Installation

[](#verify-installation)

```
-- Connect to Manticore (port 9306)
mysql -h127.0.0.1 -P9306
```

Check plugin is loaded (via Docker logs):

```
docker logs YOUR_CONTAINER 2>&1 | grep "local: subquery-resolver"
# Expected: [BUDDY]   local: subquery-resolver
```

### Run Test Queries

[](#run-test-queries)

```
-- Create test tables
CREATE TABLE test_main (id bigint, value text, created_at timestamp);
CREATE TABLE test_sub (ref_id bigint);
CREATE TABLE test_config (max_date timestamp);

-- Insert data
INSERT INTO test_main VALUES (1, 'a', 1640000000), (2, 'b', 1650000000), (3, 'c', 1660000000);
INSERT INTO test_sub VALUES (1), (3);
INSERT INTO test_config VALUES (1655000000);

-- Test IN clause subquery
SELECT * FROM test_main WHERE id IN (SELECT ref_id FROM test_sub);
-- Expected: Returns rows with id=1 and id=3

-- Test comparison operator subquery
SELECT * FROM test_main WHERE created_at > (SELECT max_date FROM test_config);
-- Expected: Returns row with id=3

-- Test mixed subqueries
SELECT * FROM test_main
WHERE id IN (SELECT ref_id FROM test_sub)
  AND created_at