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

HI All, I would like your input on how to solve my problem with one to many relation ship. This is how the data come to the code: 
 tar : ERGC [1] | ALX5 [2] | PT2 [3] | PRTD [4]  act:  Ago [1] : Inhib
+it [2,3,4]

[download]
I need to insert in my database ERGC and AGO and the rest ALX5, PT2,PRTD all should have inhibit. like :
insert into Table1 values(sq_tab1.nextval,'ERGC','AGO');
insert into table1 values(sq_tab1.nextval,'ALX5','inhibit');
insert into table1 values(sq_tab1.nextval,'PT2','inhibit');
insert into table1 values(sq_tab1.nextval,'PRTD','inhibit');

[download]
I have a code that parses the input to a format of : actual target ERGC and its reference 1 and similarly actual action AGO and its reference 1,2 or 1,2,3,4.

      my @targets = split (/\|/,$mech_results->{'Targets'}); 
    my @actions = split (/\|/,$mech_results->{'Mechanism of Action_typ
+e'} );
    my $a = sprintf "targets: %s | actions: %s |", join( ":", @targets
+ ), join ( ":", @actions );
    warn $a;
    my $iteration = each_array(@targets,@actions);
    #my ($targets,$actions) = targetsSynced(\@targets,\@actions);
    #warn scalar @targets. '|' . @actions;
    if(scalar @targets  eq scalar @actions){
        
    }else{
        while(my($targets,$actions) = $iteration->()){
          next if ($targets|$actions) eq '';
          # targets with more than one reference.
          if($targets =~ /(\(?\w+\))?(?:[ \/](.*))/){
            my ($target, $tar_ref) = $targets =~ /\(?(\w+)\)?(?:[ \/](
+(.*)))/;
            my ($tarref_no) = $tar_ref =~ /\[(\d)\]/;
            my ($action,$act_ref) = $actions =~ /(.*)?\s\[(.*)\]/;
            my @act = split(/,/,$act_ref); 
            foreach my $af(@act){
            
                #warn "$af,$tarref_no $target, $action";
                
            }
                
          }
        }

[download]
But with this I get this: 
1,1 ERGC, Ago
2,2 ALX5,  Inhibit
3,2 ALX5,  Inhibit
4,2 ALX5, Inhibit

[download]
But I would like to have 
1,1 ERGC, Ago
2,2 ALX5,  Inhibit
3,3 PT2,  Inhibit
4,4 PRTD, Inhibit

[download]
Any suggestion please. Thanks

Replies are listed 'Best First'.
Re: how to access the data to one to many relationship
by choroba (Cardinal) on Mar 22, 2015 at 20:34 UTC
    I would use split and regular expressions, too, but in a less complicated way: 
    #!/usr/bin/perl
use warnings;
use strict;

my $input = 'tar : ERGC [1] | ALX5 [2] | PT2 [3] | PRTD [4]  act:  Ago
+ [1] : Inhibit [2,3,4]';

my ($left, $right) = split /act:/, $input;

my %num2left;
while ($left =~ /(\S+) \[([0-9])\]/g) {
    $num2left{$2} = $1;
}

while ($right =~ /(\S+) \[([0-9,]+)\]/g) {
    my ($target, @indices) = ($1, split /,/, $2);
    for my $i (@indices) {
        print "$i $num2left{$i}\t$target\n";
    }
}

    [download]

    Output: 

    1 ERGC  Ago
2 ALX5  Inhibit
3 PT2   Inhibit
4 PRTD  Inhibit

    [download]
    لսႽ† ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ
[reply]
[d/l]
[select]
      ITs my fault that I didn't add the other variations in input. 
       
my $input = 'tar : ERGC   act : Ago'

      [download]
      That's with out indices. With this I am missing those sets which doesn't have a reference. How should I fix them? Using another while loop to capture those without reference is not working. Also, I tried if else condition to check whether the targets have reference before passing them to the while loops. Doesn't help either. Thanks
[reply]
[d/l]
        Oh, come on: 
            if (not %num2left) {
        $left =~ s/tar : //;
        print "NOINDEX\t$left\t$right\n";
    }

        [download]
        لսႽ† ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ
[reply]
[d/l]

Back to Seekers of Perl Wisdom