in reply to difficulty with SQL::Abstract '-in' clauses

It takes an array ref of scalars not an array ref of a scalar with commas (and your single quotes are making it a literal string too: $DocumentReference). :) Try something like this. Note I adjusted it to match what I consider normal/idiomatic syntax- 

if ( $document_reference ) {
    my @docs = split /\|/, $document_reference;

    if ( @docs > 1 )
    {
        $where{DocumentRef} = { -in => \@docs };
    }
    else
    {
        $where{DocumentRef} = $document_reference;
    }
}

[download]

Untested. Have fun.

Replies are listed 'Best First'.
Re^2: difficulty with SQL::Abstract '-in' clauses
by Anonymous Monk on Sep 20, 2009 at 09:47 UTC
    Well that worked perfectly. Thanks so much. Would it be ok to ask for more advice with the same module? Suppose my data may take the form of 'A|B*|C*|D', where i want to make the * a wild card, oracle "Like" query. Is it possible to add this functionality to your way of doing things? I can do it when the data is single values, for example 'A*', but I can't figure out how to make it all work together. Also does my post get bumped when I reply?
    Sarah
[reply]
      Sarah:

      The way I'd approach that would be to first make your code use the '-in' hash element as a hash array, rather than a fixed-format string. Next, I'd do the same with the '-like' element (I'm assuming it's existence from your question). Then, to use it, I'd split the string on the delimiter (|) to make a list and put each item on the appropriate list. Something like this (untested):

      my @vals = split /\|/, $DocumentReference;
for my $V (@vals) {
    if ($V =~ /\*/) {
        $V=~s/\*/%/;
        push @{$where{DocumentRef}{'-like'}}, $V;
    }
    else {
        push @{$where{DocumentRef}{'-in'}}, $V;
    }
}

      [download]

      Then, when it comes time to use it, you can create your IN clause like:

      $SQL .= ' IN (' . join(", ", @{$where{DocumentRef}{'-in'}}), ') ';

      [download]

      and your LIKE clauses something like this:

      $SQL .= join(' OR ', map { "LIKE $_ " } @{$where{DocumentRef}{'-like'}
+});

      [download]

      Note: You'll still need to ensure that the values are all quoted properly, and that you have the proper conjunctions between all your clauses, error handling, etc. etc.

      ...roboticus

      Update: Moved last paragraph (it was accidentally in code tags...)

[reply]
[d/l]
[select]

      I am *not* sure this is right but might be worth playing with- 

      use strict;
use warnings;
use YAML;
use SQL::Abstract;

my $original = 'A*|B|C*|D|Z';

my @refs = split /\|/, $original;

my ( %like, %in );
for my $ref ( @refs )
{
    $ref =~ s/\*\z/%/;
    $ref =~ /%\z/ ?
        $like{$ref}++
        :
        $in{$ref}++;
}

# See what we've got so far-
print Dump \(%like, %in);

my $sqla = SQL::Abstract->new();

my @doc_like;
push @doc_like, doc => { -like => $_ } for keys %like;

my ( $stmt, @bind ) = $sqla
    ->where({
             -or => [
                     doc => { -in => [ keys %in ] },
                     @doc_like
                    ]
            });
print $stmt, $/;
print join(",", @bind), $/;
#
---
A%: 1
C%: 1
---
B: 1
D: 1
Z: 1
 WHERE ( ( doc IN ( ?, ?, ? ) OR doc LIKE ? OR doc LIKE ? ) )
Z,D,B,C%,A%

      [download]

      You get semi-bumped on new posts and the user you're responding to gets a notice. :) Create an account and you'll get them too.

[reply]
[d/l]

In Section Seekers of Perl Wisdom