view release on metacpan or  search on metacpan

Perform.pm  view on Meta::CPAN

                            }
                            push @cjvals, $val;
                        }
                        if ($good) {
                            my $sth = $DB->prepare($query);
                            warn "$DBI::errstr\n" unless $sth;
                            $sth->execute(@cjvals);
                            my $val = $sth->fetchrow_array;
if ($::TRACE) {
warn "composite join query =\n$query\n";
warn "vals = " . join (", ", @cjvals) . "\n";
warn "query result = $val\n";
}
                            $fo->set_value($val);
                            $app->{redraw_subform} = 1;
                            my ( $pos, $rc );
                            ( $val, $rc )
                              = $fo->format_value_for_display( $val );
                            $GlobalUi->set_screen_value( $tag, $val );
                        }
                    }
                }
            }
        }


    }
}

#Complicated queries are tricky to get right.  A perfectly valid query
# may be unacceptably slow.  Given 3 tables, t1, t2, and t3, each with
# columns mca, mcb, ca, cb where mca and mcb are "matching columns"
# (t1.mca = t2.mca) and t1.ca is unrelated to t2.ca and so on, we
# want every row from t1, with 1 matching row (if any) from t2 and t3.
# We have to use some means of getting just 1 row from t2 and t3 per row
# from t1.  Speaking of just t1 and t2, an inner join will leave out a row
# from t1 if no rows in t2 match that row.  An outer join will have 2 or
# more rows in the results if more than 1 row of t2 matches a single row
# of t1.  So, neither delivered the desired results.  (Just why sperform
# works that way is another question that doesn't seem to have a good
# answer.)  An answer to this problem was to use a function that would
# return just one row of t2 per row of t1, such as "min".  The query then
# became:
#
# select min(t2.ca) aa, min(t2.cb) ab, t1.ca ac, t1.cb ad
# from t1, outer t2 where t1.mca = t2.mca
# group by t1.ca, t1.cb
#
# This worked except when t1 had duplicate rows.  However, when t3 is
# thrown in the mix, and we join the tables with a relation between t2
# and t3, then we have trouble.  The query below might be extremely slow,
# taking many hours to run:
#
# select min(t2.ca) aa, min(t2.cb) ab, t1.c2 ac, t1.c3 ad,
#        min(t3.ca) ae, min(t3.cb) af, t1.mca ag, min(t2.mcb) ah
#   from t1, outer t2, t3 where t1.mca = t2.mca and t2.mcb = t3.mcb
#   group by t1.c2, t1.c3, t1.mca
#
# As long as all the joins are between t1 and the other tables, the query
# is fast.  To handle the situation when they're not, needed to work out
# another query formulation.  Doing it in 2 queries with a temporary table
# works:
#
# select min(t2.ca) aa, min(t2.cb) ab, t1.ca ac, t1.cb ad,
#        t1.mca ae, min(t2.mcb) af
#   from t1, outer t2 where t1.mca = t2.mca
#   group by t1.ca, t1.cb into temp tmpperl;
# select tmpperl.aa aa, tmpperl.ab ab, tmpperl.ac ac, tmpperl.ad ad,
#        tmpperl.ae ae, min(t3.ca) af, min(t3.cb) ag
#   from tmpperl, outer t3
#   where tmpperl.af = t3.mcb

# Take 6:  Query is still not good enough.
# At least 2 problems:  
# 1. The minimum value of each column may be in different rows,
# and if min is used on more than one col, we want everything to be from
# the same row.
# 2. In a lookup, the form may ask for the same column in more than one place,
# with different conditions.

# Take 7:  The query strategy had to change some more.
# The strategy used in take5 could get columns from different rows of
# joined tables, because all it did was get the minimum of each column
# regardless of what rows the minimums of any other columns came from.
# This version replaces the single query for those minimums with 2.
# The 1st of the 2 queries gets only the minimum rowid.  Then the 2nd does
# not use minimum at all but instead gets the rest of the columns from
# the joined table with "where joined.rowid = pf_tmpx.row_id".

#Make a graph of all the joins.
#  (Each node represents a table, and each edge represents a join.)
sub compute_joins {
    my (%joins, %tags);
    my $fl = $GlobalUi->get_field_list;
    $fl->reset;
    while ( my $fo = $fl->iterate_list ) {
        my ( $tag, $tbl, $col ) = $fo->get_names;
#get joins in lookups
        if ($fo->{active_tabcol}) {
            my $t2 = $fo->{join_table};
            my $c2 = $fo->{join_column};
            my ( $t1, $c1 ) = $fo->{active_tabcol} =~ /(\w+)\.(\w+)/;
            if ($t1 ne $t2) {
                $joins{$t1}->{$t2}->{"$c1 $c2 $tag"} = 1;
                $joins{$t2}->{$t1}->{"$c2 $c1 $tag"} = 1;
            }
        }
#get all other joins
        if ( defined $tags{$tag} ) {
            foreach my $jtag (keys %{$tags{$tag}}) {
                my ( $t1, $c1 ) = $jtag =~ /(\w+)\.(\w+)/;
                if ($t1 ne $tbl) {
                    $joins{$t1}->{$tbl}->{"$c1 $col"} = 1;
                    $joins{$tbl}->{$t1}->{"$col $c1"} = 1;
                }
            }
        }
        $tags{$tag}->{"$tbl.$col"} = 1;
    }
    return %joins;
}