Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask

How to build up complex SQL queries

by blssu (Pilgrim)
on Sep 10, 2002 at 13:57 UTC ( #196672=note: print w/replies, xml ) Need Help??

in reply to Using hash elements to create a string

When you have a problem like "I need to create a string made up from values stored within arrays" you should think "maybe join will work."

When you have a problem like "has to work for any amount of arrays or values" you should think "maybe map or foreach will work."

First, you need to reduce the problem down to simpler sub-problems. Reformat your desired results so that similar structures are grouped together. Also split up separators and iteration patterns.

view0 full outer join view1 on view0.size = view1.size and view0.buffersize = view1.buffersize full outer join view2 on view1.size = view2.size and view1.buffersize = view2.buffersize full outer join view3 on view2.size = view3.size and view2.buffersize = view3.buffersize

Now it's a bit easier to see the sub-problems. I've also marked each sub-problem as a join, map, or foreach kind of problem. It doesn't really matter if you get this wrong. As you try to solve each sub-problem the right solution will emerge. Here are the sub-problems I see:

  1. create the "view0" statement -- join + map
  2. create the "view? on" clauses
    1. match key columns between tables -- map
    2. generate a SQL comparison -- join
    3. combine with "and" -- join
  3. run the query (DBI)
  4. fetch results for an unknown number of columns (DBI)

The problems marked (DBI) can be handled by DBI directly -- RTFM. I'm not going to step through the process of solving each sub-problem, I'm just going to dump out my first cut at the solutions:

use strict my $last = 'view0'; print join(' full outer join ', 'view0', view_on_clauses()),"\n"; sub view_on_clauses { return map { join(' ', $_, 'on', and_clause($_)) } qw(view1 view2 view3); } sub and_clause { my($view) = @_; return join(' and ', equality_clauses($view)); } sub equality_clauses { my($view) = @_; my @equality_clauses = map { "$last.$_=$view.$_" } qw(size buffersize); $last = $view; return @equality_clauses; }

Right away we see a problem with your input and the first solution cut: hashes are unordered, but the solution requires an array of view names in a specific order. Also, the column names need to be an array, but you have them stored as strings. One thing that the first cut doesn't do is handle different column names for each view. Do you want to do that or should all columns names be the same?

Here's my final solution after fixing up those two issues and then replacing the hard-coded arrays with variables:

use strict; my @views = ( 'view0', 'view1', 'view2', 'view3' ); my %columns = ( 'view0' => [qw(buffersize size value)], 'view1' => [qw(buffersize size value)], 'view2' => [qw(buffersize size value)], 'view3' => [qw(buffersize size value)] ); print build_query(\@views, \%columns), "\n"; my $last; sub build_query { my($views, $columns) = @_; my $first = shift @{$views}; $last = $first; return join(' full outer join ', $first, view_on_clauses($views, $columns)); } sub view_on_clauses { my($views, $columns) = @_; return map { join(' ', $_, 'on', and_clause($_, $columns)) } @{$vi +ews}; } sub and_clause { my($view, $columns) = @_; return join(' and ', equality_clauses($view, $columns)); } sub equality_clauses { my($view, $columns) = @_; my @equality_clauses = ( ); # Convert map into explicit for loop because we need to # merge together two different arrays. If the column names # are always the same, then map can be used. for (my $i = 0; $i < @{$columns->{$view}} - 1; ++$i) { push @equality_clauses, $last . '.' . $columns->{$last}[$i] . '=' . $view . '.' . $columns->{$view}[$i]; } $last = $view; return @equality_clauses; }

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://196672]
[Eily]: you could tie a variable into not having the same value each time, if you like to make people who try to debug your code facepalm
[Corion]: perl -wle 'package o; use overload q("") => sub {warn "str"; ""}, bool => sub{warn "bool"; 1}; package main; my $o={}; bless $o => o; print "Yay" if ($o && !length($o))'
[Corion]: But people writing such code should document the objects they construct and why it makes sense for an object to be invisible as string while being true in a boolean context
[hippo]: That's equal parts clever and horrendous.
[Eily]: the overload version wouldn't return true with "$x" && !length $x though, I guess
[hippo]: The more I look at this code, the more $x is a plain old scalar and the more this condition will never be true. I'm calling it a bug at this point.
[hippo]: Thanks for your input which has soothed my sanity (a little)
[Corion]: Eily: Sure - if you force both things into stringy things, then you break that magic. But that would also mean that you changed the expression, as now $x = 0.00 will be true instead of false as it were before
[Corion]: Ah no, at least in my feeble experiments that doesn't change the meaning
[Corion]: We sell sanity in small packages ;)

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (9)
As of 2017-07-27 13:40 GMT
Find Nodes?
    Voting Booth?
    I came, I saw, I ...

    Results (413 votes). Check out past polls.