Complex JOIN using multiple tables

Question

for instance

I have several profile tables, for example

• music_profile
• sports_profile
• art_profile

All of these tables have corresponding names, and all of them have a column title.

The second table contains alternate headers for each row in the profile table.

Their columns are:

id, parent_id, parent_table, alt_title_001, alt_title_002, alt_title_003, alt_title_004, status, created, updated.

I want to

CHOOSE the values ​​of several columns
FROM music_profile, sports_profile, art_profile
WHERE name, alt_title_001, alt_title_002, alt_title_003, alt_title_004 as the value

Now I can select the columns with WHERE title LIKEand UNION, but I don’t know how to join the table alternate_titlesin the SELECT statement.

I presented my current code below. The table for alternate_titlesis not implemented here.

; , .

sub AdvancedSearchFormResults {
    my $self = shift;
    my $opts = ref $_[0] eq 'HASH' ? shift : {@_};

    my $mode   = shift;
    my $order  = shift;
    my $limit  = shift;

    my @array;
    my $where;
    my $mode = $$opts{mode};

    my $left_join_clause;
    my (@where_stmt, @where_vals, @join);

    if (defined $$opts{platform}) {
        $where = $$opts{platform};
    }
    if ($$opts{'title_like'}) {
        push(@where_stmt, "title like ?");
        push(@where_vals, '%'.$$opts{'title_like'}.'%');
    }
    if ($$opts{'publisher'}) {
        push(@where_stmt, "publisher = ?");
        push(@where_vals, $$opts{'publisher'});
    }
    if ($$opts{'status'}) {
        push(@where_stmt, "status = ?");
        push(@where_vals, $$opts{'status'});
    }

    my $left_join_clause = scalar @join ? join("\n", @join) : "";

    my $where_clause = @where_stmt    ? "WHERE ".join(" AND ", @where_stmt) : "";
    my $order_clause = length($order) ? "ORDER BY $order"                   : "";
    my $limit_clause = length($limit) ? "LIMIT $limit"                      : "";

    my $select_stmt;

    if ($mode eq 'BUILD') {
        $select_stmt = "SELECT 
                            '$where' AS event,
                            ident,
                            title,
                            publisher
                        FROM $where
                          $left_join_clause
                          $where_clause
                          $order_clause
                          $limit_clause";

        my $sth = $schema->prepare($select_stmt) or die $schema->errstr;
        $sth->execute(@where_vals) or die $sth->errstr;

        while (my $row = $sth->fetchrow_hashref()) {
            push(@array, $row);
        }

    }
    elsif ($mode eq 'UNION') {

        my @select_stmts;
        my @platforms       = $self->ProfileTables();
        my $total_platforms = -1;

        foreach my $table (@platforms) {
            $total_platforms++;
            my $stmt = "(SELECT '$table' AS event,ident,title,publisher,status FROM $table $where_clause)";
            push(@select_stmts, $stmt);
        }

        my $select_stmt .= "$select_stmts[0] UNION ALL";
        $select_stmt    .= join(' UNION ALL ', @select_stmts[ 1 .. 28 ]);

        my @new_vals = (@where_vals, (@where_vals) x $total_platforms);

        my $sth = $schema->prepare($select_stmt) or die $schema->errstr;
        $sth->execute(@new_vals) or die $sth->errstr;

        while (my $row = $sth->fetchrow_hashref()) {
            push(@array, $row);
        }
    }
    elsif ($mode eq 'REFRESH') {
        print '
        <div class="alert alert-danger" role="alert">
             <strong>Please fill out at least one field.</strong>
        </div>';
    }

    return @array;
}

.

. .

my $title     = 'Mario';
my $publisher = '';

my %params = (
    title_like => $title,
    publisher  => $publisher,
    status     => 'a',
);

my @results = $results->AdvancedSearchFormResults(\%params);

print Data::Dumper::Dumper(\@results);

$VAR1 = [
          {
            'ident' => '2109',
            'title' => 'Mario Bros.',
            'publisher' => 'Atari'
          },
          {
            'ident' => '30',
            'title' => 'Mario Bros.',
            'publisher' => 'Atari'
          },
          {
            'publisher' => 'Atari',
            'ident' => '43',
            'title' => 'Mario Bros.'
          },
    ];

Answer 1

Google .

:

my @profiles = ('art', 'music', 'sports');
my @results;
my $blah;
my $sql;

foreach my $profile (@profiles) {
    $sql = "SELECT
                $profile.ident,
                $profile.title,
                $profile.status
             FROM $profile
            LEFT JOIN alternate_titles ON $profile.ident = alternate_titles.parent_ident
             WHERE title like '%$blah%' 
                OR 
                CONCAT_WS(' ',alternate_titles.alt_title_001,alternate_titles.alt_title_002,alternate_titles.alt_title_003,alternate_titles.alt_title_004
                AND alternate_titles.parent_table = '$profile'";
}
my $sth = $schema->prepare($sql) or die $schema->errstr;
$sth->execute() or die $sth->errstr;

while (my $data = $sth->fetchrow_hashref()) {
    push(@results, $data);
}

, .

, , - , .