Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

3 strings to join

by jptxs (Curate)
on Sep 29, 2000 at 01:29 UTC ( [id://34515]=perlquestion: print w/replies, xml ) Need Help??

jptxs has asked for the wisdom of the Perl Monks concerning the following question:

this is a bit of a theory question. (laugh if you will, but remember my degree is in philosophy and didn't take math from 8th grade on :)

i have three things A, B and C. They will be snippets of SQL. A will always be the sart of the string. If B exists, I want to join it to A by ' where ', and if C exits i want to join it to A with ' where ' if B is not there and with ' and ' if it is there. I know i can

if ( $b and $c ) { $string = "$a" . ' where ' . "$b" . ' and ' . "$c"; } elsif ( $b or $c ) { if ( $b ) { $string = "$a" . ' where ' . "$b"; } else { $string = "$a" . ' where ' . "$c"; } }

my gut tells me there's a better way to do this though I don't know how. ideas?

-- I'm a solipsist, and so is everyone else. (think about it)

Replies are listed 'Best First'.
(Ovid) Re: 3 strings to join
by Ovid (Cardinal) on Sep 29, 2000 at 01:56 UTC
    You might find the following syntax a bit clearer.
    if ( $b and $c ) { $string = "$a where $b and $c"; } elsif ( $b or $c ) { $string = "$a where " . ($b ? $b : $c) ; }
    This relies on using the ternary operator: condition ? true : false Let's say you have the following line:
    $x = $b ? 5 : 17;
    If $b evaluates as true, $x will be set to 5. Otherwise, it will be set to 17.

    (Sorry if I seem pedantic. Not sure if you'd seen it before).

    Cheers,
    Ovid

    Join the Perlmonks Setiathome Group or just go the the link and check out our stats.

      actually I have seen it, but it's one of those things I read about and then say "what could I use that for?", move on, hit an issue and then someone as enlightened as yourself says "You could do this." and I have a small psychotic break (or satori, whatever you prefer :)

      ...and then the monk gained enlightenment?

      -- I'm a solipsist, and so is everyone else. (think about it)

Re: 3 strings to join
by runrig (Abbot) on Sep 29, 2000 at 02:04 UTC
    What I often do:
    my $sql="$a where "; my @where; push @where, $b if $b; push @where, $c if $c; $sql .= join " and ", @where;
    Usually the push'es are in a block where the variables are being created, so I don't need the 'if $b' or 'if $c' clauses.
    This also lets me create an @args array so that I can use placeholders in the where clauses and use the array of arguments during the execute (assuming that you're using DBI). Eg:
    my $sql="$a where "; my (@where, @args); if ($something) { push @where, "field1 = ?"; push @args, $some_value; } if ($something_else) { push @where, "field2 = ?"; push @args, $another_value; } $sql .= join " and ", @where; # Assume RaiseError is true; # If this is executed more than once # in this script, # use 'prepare_cached' instead. my $sth = $dbh->prepare($sql); $sth->execute(@args);
    Some of the many benefits of placeholders are that you don't have to escape quotes (or generally any other character), you can prepare the statement once and execute it many times with different arguments, and even if you DO only execute it once in this script, some databases can cache the statement and so using placeholders can let the database utilize the cache more efficiently over multiple executions with different arguments.

      update: actually i guess Ovid's also assumes a where clause, but then again so did my original post :) but it can be easily adapted by simply using saying  else { $string = $a } at the end...

      I do something very similar alot, but the problem is that this assumes there will be a where clause. I'm in a situation where there could be none or many. It's also not a whole list of things, just a few. I think Ovid's response is what I was looking for, but you have done this a bit nicer than i usually do so i'll take that too :)

      -- I'm a solipsist, and so is everyone else. (think about it)

        If there a possibility of no where clause, then it becomes:
        my $sql = $a; ... $sql .= " where ".join(" and ", @where) if @where;
        And as for placeholders, the @args array will be empty, so there's no harm in including it in the execute.
Re: 3 strings to join
by chromatic (Archbishop) on Sep 29, 2000 at 01:55 UTC
    Here's my test script. I'm not sure how well you like nested ternary operators, but it seemed to work for me:
    #!/usr/bin/perl -w use strict; my $a = "first"; makejoin( 'second', 'third'); makejoin( undef, 'third'); makejoin('second', undef); sub makejoin { my ($b, $c) = (@_); my $join = " where "; $join .= defined ($b) ? $b . (defined($c) ? " and $c " : '') : $c; print $a, $join, "\n"; }
Re: 3 strings to join
by merlyn (Sage) on Sep 29, 2000 at 02:49 UTC
    I'm puzzled about the use of "exists" here, and I don't see that anyone caught that. The variable $c always exists, once mentioned in the program.

    If you mean non-empty, then this will do:

    my @conditions = grep length, $b, $c; $string = $a; $string .= " where ".join(" and ", @conditions) if @conditions;
    For non-whitespace, change length to /\S/.

    -- Randal L. Schwartz, Perl hacker

      another problem of a philosopher in a programmers world...there's no use strict; when interpreting Nietzsche.

      I meant non-empty. : )

      -- I'm a solipsist, and so is everyone else. (think about it)

Re: 3 strings to join
by BlaisePascal (Monk) on Sep 29, 2000 at 02:39 UTC
    From the "gratuitous use of grep" club...
    $string = "$a where " . join ' and ',grep {$_} ($b,$c);
    Explanation: Read this from right-to-left. The grep takes as arguments a block of code (or a sub) and a list of items and return just the items in the list which the block of code is true for. In this case, the block of code is simply $_, so grep returns all items in the list ($b,$c) which are true (not undef, '', 0, or '0'). The join then takes those true items, and combines that list with ' and '. If there only one item, join omits the ' and '. It then concatenates the resulting string of 'where' clauses to the string formed by "$a where ", to get the resulting string.

      nice.

      throw in another join and grep:

      $string = join ' where ', grep {$_} ($first, join ' and ', grep {$_} ( + $b, $c ));
      and it can even handle the case where there are no where clauses...

      -- I'm a solipsist, and so is everyone else. (think about it)

Re: 3 strings to join
by little (Curate) on Sep 29, 2000 at 01:48 UTC
    join $b and $c by 'and'.
    further you might look at SQL::Statement.
    Have a nice day
    All decision is left to your taste
RE: 3 strings to join
by Adam (Vicar) on Sep 29, 2000 at 02:02 UTC
    $string = $a . ' where '; if( $b ) { $string .= $c ? "$b and $c" : $b } elsif( $c ) { $string .= $c } else { # Assert }

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://34515]
Approved by root
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others admiring the Monastery: (6)
As of 2024-03-28 19:54 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found