Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

How to build up complex SQL queries

by blssu (Pilgrim)
on Sep 10, 2002 at 13:57 UTC ( [id://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?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others learning in the Monastery: (3)
As of 2024-03-19 04:00 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found