Ruby Sequel多列VALUES子句实现：查询不在库中的数据

I get it—unnest only handles single columns, and sql_value_list falls short because it doesn’t wrap things in the full VALUES (...) syntax you need. Here’s a straightforward way to build a multi-column temporary table from your input data and find which rows aren’t present in your target table.

Approach Overview #

We’ll construct a proper VALUES subquery using Sequel’s literal SQL capabilities, then compare it against your database table using either a LEFT JOIN or NOT EXISTS clause to filter out existing records.

Example Code #

Let’s use a hypothetical products table with sku and category columns, and a list of input (sku, category) pairs we want to check.

Step 1: Define Your Input Data and Database Connection

require 'sequel'

# Connect to your database (adjust the URI for your DB type)
DB = Sequel.connect('postgres://username:password@host:port/database_name')

# Your multi-column input data (each inner array is a row)
input_rows = [
  ['SKU123', 'Electronics'],
  ['SKU456', 'Clothing'],
  ['SKU789', 'Home'],
  ['SKU012', 'Beauty']
]

Step 2: Build the VALUES Subquery

We’ll create a dataset representing your input data as a temporary table with named columns, which lets us reference them in the main query:

# Generate the VALUES clause safely (prevents SQL injection)
values_clause = "(VALUES #{input_rows.map { |row| DB.literal(row) }.join(', ')}) AS temp(sku, category)"

# Create a Sequel dataset from the VALUES clause
temp_dataset = DB.from(Sequel.literal(values_clause))

Step 3: Query for Missing Records

Option 1: Using LEFT JOIN (easy to read, works across most databases)

missing_records = temp_dataset
  .left_join(:products, sku: :sku, category: :category)
  .where(Sequel[:products][:sku].nil?) # Filter rows with no match in products
  .select(:temp__sku, :temp__category) # Select only the missing rows from our temp table

Option 2: Using NOT EXISTS (often more efficient for large datasets)

missing_records = temp_dataset
  .where(
    Sequel.not_exists(
      DB[:products].where(
        sku: Sequel[:temp][:sku],
        category: Sequel[:temp][:category]
      )
    )
  )
  .select(:sku, :category)

Step 4: Retrieve Results

# Iterate over the missing records
missing_records.each do |row|
  puts "Missing record: SKU=#{row[:sku]}, Category=#{row[:category]}"
end

Key Notes #

• Safety: Using DB.literal ensures all input values are properly escaped, preventing SQL injection attacks. Never concatenate raw input into your SQL string!
• Reusability: Wrap the VALUES dataset creation in a helper method for easier reuse across different tables and data:

  def create_values_dataset(db, data, column_names)
    clause = "(VALUES #{data.map { |row| db.literal(row) }.join(', ')}) AS temp(#{column_names.join(', ')})"
    db.from(Sequel.literal(clause))
  end
  
  # Usage example
  temp_ds = create_values_dataset(DB, input_rows, [:sku, :category])
  

• Database Compatibility: This approach uses standard SQL VALUES syntax, so it works with PostgreSQL, MySQL 8.0+, SQLite, and most other modern databases.

内容的提问来源于stack exchange，提问作者Andrey Skuratovsky