Style: Passing the same value for multiple DBI placeholders

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

I'm running the following function against a PostgreSQL database and a few tables to come up with a list of addresses that should be notified about messages that match a certain criteria. The most obvious thing for me to do was create a SQL SELECT statement which given some parameters would just return the right values. The issue I ran into is that I need to use the $message -> {'userid'} value more than once. The existing usage I've detailed looks wrong to me and I'm wondering if any of you have suggestions for either easing my mind or better ideas.

sub notify_users {
    my $message = shift;

    # Get the list of addresses to notify
    my @addr = @{$dbh -> selectall_arrayref(q[
        SELECT addr
        FROM email,
             (SELECT userid
              FROM usernames
              WHERE 0 <> position(lower(username) IN ?)
                AND userid <> ?
              EXCEPT
              SELECT userid
              FROM ignore
              WHERE ignore_userid = ?) ids
        WHERE email.userid = ids.userid],
        undef,
        lc $message -> {'message'},
        $message -> {'userid'},
        $message -> {'userid'})};

    # flatten the array
    $_ = $_ -> [0] for @addr;
    @addr = grep length(), @addr;
    return unless @addr;

    ....

}
[download]

Comment on Style: Passing the same value for multiple DBI placeholders Select or Download Code

Replies are listed 'Best First'.
Re: Style: Passing the same value for multiple DBI placeholders by dws (Chancellor) on Feb 06, 2003 at 19:17 UTC
The issue I ran into is that I need to use the `$message -> {'userid'}` value more than once. The existing usage I've detailed looks wrong to me and I'm wondering if any of you have suggestions for either easing my mind ... You're plugging multiple values in to satisfy placeholders in a query, and some of the values happen to be the same. There's nothing at all wrong with that. My advice is to repeat "there's nothing wrong here" seven times, and then move on to something else, like double-checking your query. We all go through phases where something normal looks wrong until we look at it long/hard enough.	[reply] [d/l]
Re: Style: Passing the same value for multiple DBI placeholders by runrig (Abbot) on Feb 06, 2003 at 22:08 UTC
You could say: `... ($message -> {'userid'}) x 2, ...` [download] But it's fine the way you have it. I would think about doing it the way I have it above if I needed a variable number or large number of the same arguments in a row.	[reply] [d/l]
Re^2: Style: Passing the same value for multiple DBI placeholders by diotalevi (Canon) on Feb 07, 2003 at 02:38 UTC
I'd classify that as an obfuscated usage. There were one or two moments where I almost wrote it that way except that it's ugly. Of course... if as you say there were a "large number of the same arguments in a row" then it makes sense. But then I'd have to wonder whether the SQL call was written sanely. (which is neither here nor there). Thanks for the suggestion though. Seeking Green geeks in Minnesota	[reply]
Re: Re: Style: Passing the same value for multiple DBI placeholders by bart (Canon) on Feb 08, 2003 at 00:59 UTC
You could say: `... ($message -> {'userid'}) x 2, ...` [download] or even `... map({ $_, $_ } $message->{'userid'}) ...` [download]	[reply] [d/l] [select]
Re: Style: Passing the same value for multiple DBI placeholders by steves (Curate) on Feb 06, 2003 at 19:13 UTC
Your IN clause placeholder will only work if there's one value. You either have to dynamically create the query so the number of IN placeholders matches the number of IN bind parameters; or you have to dynamically create the IN clause to expand to what you want. There's a decent thread on this here that has examples.	[reply]
Re^2: Style: Passing the same value for multiple DBI placeholders by diotalevi (Canon) on Feb 06, 2003 at 19:25 UTC
Your IN clause placeholder will only work if there's one value. You either have to dynamically create the query so the number of IN placeholders matches the number of IN bind parameters; or you have to dynamically create the IN clause to expand to what you want. Huh? The clause `0 <> position(lower(username) IN ?)` should return true for as many tuples from `usernames` match. `$message -> {'message'}` is a single text value which may or may not contain a user's username. Or am I misunderstanding something here? Added After reading your link it looks like you misunderstood the question. The `foo IN (bar, baz, qux)` you're mentioning is different than the `position(foo IN bar)` I'm using. See String functions and operators for more detail. Seeking Green geeks in Minnesota	[reply] [d/l] [select]
Re: Re^2: Style: Passing the same value for multiple DBI placeholders by steves (Curate) on Feb 06, 2003 at 19:35 UTC
Ooops ... I did misread and have been duly chastised with a negative vote. Thanks for pointing that out. As for the main point (which I also seem to have overlooked): I use the same value for multiple placeholders all the time. I see nothing wrong with that but I may be missing something else?	[reply]