Grails 2.5.1 GORM 4.x能否生成PostgreSQL数组交集查询条件？

Absolutely! You can achieve this with Grails 2.5.1's GORM 4.x Criteria API, no column definition changes or PostgreSQL extension plugin required. The key is leveraging GORM's ability to embed raw SQL snippets directly into your criteria queries using sqlRestriction.

Here's a working example tailored to your use case: #

Assuming your domain class is named Stuff (mapped to the stuff table with the thing column storing pipe-separated values):

// Define your search values as a list
def targetValues = ['Value A', 'Value X']

// Build and execute the criteria query
def matchingStuff = Stuff.createCriteria().list {
    // Use sqlRestriction to inject the PostgreSQL array overlap logic
    sqlRestriction "ARRAY[?] && regexp_split_to_array(thing, '\\|')", targetValues
}

Let's break this down: #

1. sqlRestriction: This method lets you insert raw SQL conditions into your GORM criteria. The first argument is a SQL template with placeholders (?), and the second argument is a list of values to bind to those placeholders (GORM handles safe parameter binding to avoid SQL injection).
2. ARRAY[?]: PostgreSQL's syntax for creating an array from your input values. GORM replaces the ? with your targetValues list, generating something like ARRAY['Value A', 'Value X'] in the final SQL.
3. regexp_split_to_array(thing, '\|'): Splits the thing column's pipe-separated string into a PostgreSQL array. Note the double backslash (\\|)—this is required because Groovy uses backslashes as escape characters, so we need to escape it to pass a single backslash to PostgreSQL's regex parser.
4. && operator: PostgreSQL's array overlap operator, which returns true if the two arrays share any common elements—exactly the logic you need for your filter.

Combining with other criteria conditions #

You can easily mix this raw SQL restriction with standard GORM criteria methods. For example, if you wanted to filter by a category column too:

def matchingStuff = Stuff.createCriteria().list {
    eq('category', 'Electronics') // Standard GORM equality check
    sqlRestriction "ARRAY[?] && regexp_split_to_array(thing, '\\|')", ['Value A', 'Value X']
}

A quick note on performance #

Since this approach splits the string into an array on every query, it won't use any indexes on the thing column. If you're working with large datasets, this could lead to slower query times. But since you mentioned you can't modify the column definition right now, this is the most straightforward workaround.

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