How do I access a specific return value from the CDBI::Search function?

Question

I am using a DB::CDBI class for accessing the database in our application. Our project is in object-oriented Perl.

package LT::LanguageImport;
use strict;
use warnings;
use base 'Misk5::CDBI';

__PACKAGE__->table( 'english_text_translation' );
__PACKAGE__->columns( Primary => qw/english language translation/ );
__PACKAGE__->columns( Essential => qw/english language translation/ );
__PACKAGE__->has_a( english => 'LT::EnglishLanguage' );

In one such scenario, I am supposed to check if a row in a table exists. I am using the built-in search API in a CDBI call.

sub find_translation {
    my $translation_exists_r_not = $class->search(
        english     => $english,
        language    => $language,
        translation => $translation
    );
    return;
}

$translation_exists_r_not is getting the expected values depending on the inputs given in the search. If the row exists, then the _data is updated with the row details.

$translation_exists_r_not = bless({
        '_data' => [
            {
                'language'    => 'polish',
                'translation' => 'Admin',
                'english'     => 'admin'
            }
        ],
        '_place'  => 0,
        '_mapper' => [],
        '_class'  => 'LT::LanguageImport'
    },
    'Class::DBI::Iterator'
);

If the row desn't exist, then I get a return value like this.

$translation_exists_r_not = bless({
        '_data'   => [],
        '_place'  => 0,
        '_mapper' => [],
        '_class'  => 'LT::LanguageImport'
    },
    'Class::DBI::Iterator'
);

I want to return the value of translation from this sub find_translation depending on the search result. I am not able to get a best condition for this.

I tried copying the _data into an array, but I'm not sure how to proceed further. As _data will be an empty arrayref and another condition it will have a hashref inside the arrayref.

my @Arr = $translation_exists_r_not->{'_data'};

Answer 1

CDBI's search method will return an iterator, because there may be multiple rows returned depending on your criteria.

If you know there can be only one row that matches your criteria, you want to use the retrieve method, i.e.:

if (my $translation_exists_r_not = $class->retrieve(
    english     => $english,
    language    => $language,
    translation => $translation
)){
    return [$translation_exists_r_not->translation,
            'Misk5::TranslationAlreadyExists']
}
else {
    return [ '', undef ]
}

And if multiple rows can be returned from your search, and you're only interested in the truthiness, then again, don't be rummaging around inside the CDBI::Iterator, but use its methods:

my $translation_exists_r_not = $class->search(
    english     => $english,
    language    => $language,
    translation => $translation
); # returns an iterator
if ($translation_exists_r_not){
    my $first = $translation_exists_r_not->first;
    return [ $first->translation, 'Misk5::TranslationAlreadyExists' ]
}

Have a look at perldoc Class::DBI and perldoc Class::DBI::Iterator. CDBI has excellent documentation.

Answer 2

I think I got the solution. Thanks to whoever has tried to solve it.

my @req_array      = %$translation_exists_r_not->{_data};
my $length_of_data = '9';
foreach my $elem (@req_array) {
    $length_of_data = @{$elem};
}

Now check the length of the array.

if ($length_of_data == 0) {
    $error = '';
    $result = [undef, $error];
}

Now check if it is one.

if ($length_of_data == 1) {
    my @result_array = @{%$translation_exists_r_not->{_data}};
    my $translation  = $result_array[0]{'translation'};
    $error = 'Misk5::TranslationAlreadyExists';
    $result = [$translation, $error];
}
return @$result;