DBIx::Class中OR条件单条查询及one_row连接未关闭问题

Hey there! Let's work through your two DBIx::Class issues step by step:

You mentioned that using search with -or returned "1" instead of your actual data, and you wanted to use find but it doesn't support OR logic. Here's what's going on and how to fix it:

Why you saw "1" #

When you call search alone, it returns a ResultSet object, not individual records. In scalar context (like if you tried to print it directly or use it in a boolean check), this object evaluates to a true value (often "1"), which is why you got that unexpected result.

Solutions for OR-based single record fetch #

Since find doesn't support OR conditions, use one of these two reliable approaches:

Option A: Use search + single

The single method pulls the first matching record from the ResultSet and automatically adds LIMIT 1 to your SQL (perfect for your unique constraint scenario):

my $param = 'VALUE';
my $product = $Products->search(
    {
        -or => [
            code  => $param,  # Shorthand for { '=', $param }
            name  => { like => "%$param%" }
        ]
    }
)->single;

If no match exists, single returns undef—ideal since your unique constraint guarantees at most one match.

Option B: Stick with one_row (your existing approach)

You already loaded Helper::ResultSet::OneRow, which works similarly to single but throws an exception if no record is found (great if your business logic ensures a match exists). Your current code in the index method is correct here—just make sure you're always calling ->one_row on the ResultSet to get the actual record object, not the ResultSet itself.

If your SQL statement stays open until the app closes, this is usually tied to database connection lifecycle or unclosed statement handles. Let's address this:

1. Verify AutoCommit is working #

Your Model has AutoCommit => 1 set, which is correct—this ensures each query auto-commits and doesn't hold open transactions. To confirm it's working, you can explicitly trigger a commit after your query (though AutoCommit should handle this automatically):

$c->model('DB_T')->storage->dbh->commit();

2. Restrict variable scope to clean up handles #

Make sure your ResultSet and record objects are local to the request scope. Using a block to limit their lifecycle can help garbage collection clean up open handles:

sub index :Path :Args(0) {
    my ($self, $c) = @_;
    {
        # Block scopes variables to this section
        my $Products = $c->model('DB_T::Products');
        my $product = $Products->search(
            -or => [
                code  => 'x',
                name  => { like => '%X%' }
            ]
        )->one_row;
        $c->response->body("product: " . $product->name);
    } # $product and $Products are destroyed here, releasing handles
}

3. Update your DBIx::Class and DBD::Pg versions #

Older versions of these modules had handle leaks. Update to the latest stable releases:

cpanm DBIx::Class DBD::Pg

4. Tweak connection pool settings (if applicable) #

If your Model uses connection pooling, some settings can keep connections alive longer than needed. Add these to your connect_info to ensure handles are cleaned up:

connect_info => {
    dsn => 'dbi:Pg:dbname=p12;host=localhost',
    user => 'username',
    password => 'password',
    AutoCommit => 1,
    RaiseError => 1, # Helps debug unexpected errors
    PrintError => 0,
    pg_enable_utf8 => 1, # Handles UTF-8 correctly
    keepalive => 0, # Disables persistent connection checks
}

• You can shorten code => { '=', $param } to code => $param—DBIx::Class defaults to equality checks.
• Double-check field names: your problem mentions description, but your Products result class uses name. Make sure these match your actual database schema!
• Enable RaiseError in your Model—it'll make debugging issues like missing fields or permission errors way easier.

内容的提问来源于stack exchange，提问作者Kérdezősködő Indián