lib/CXGN/Image/Search.pm

   1 package CXGN::Image::Search;
   2
   3 =head1 NAME
   4
   5 CXGN::Image::Search - an object to handle searching for images given criteria
   6
   7 =head1 USAGE
   8
   9 my $image_search = CXGN::Image::Search->new({
  10     bcs_schema=>$schema,
  11     image_id_list=>\@image_id_list,
  12     image_names_exact=>0,
  13     image_name_list=>\@image_name_list,
  14     include_obsolete_images=>0,
  15     original_filenames_exact=>0,
  16     original_filename_list=>\@original_filename_list,
  17     descriptions_exact=>0,
  18     description_list=>\@description_list,
  19     tag_list_exact=>0,
  20     tag_list=>\@tag_list,
  21     include_obsolete_tags=>0,
  22     include_obsolete_image_tags=>0,
  23     submitter_usernames_exact=>0,
  24     submitter_username_list=>\@submitter_username_list,
  25     submitter_first_names_exact=>0,
  26     submitter_first_name_list=>\@submitter_first_name_list,
  27     submitter_last_names_exact=>0,
  28     submitter_last_name_list=>\@submitter_last_name_list,
  29     submitter_id_list=>\@submitter_id_list,
  30     stock_type=>$stock_type,
  31     stock_id_list=>\@stock_id_list,
  32     stock_names_exact=>0,
  33     stock_name_list=>\@stock_name_list,
  34     project_id_list=>\@project_id_list,
  35     project_names_exact=>0,
  36     project_name_list=>\@project_name_list,
  37     project_md_image_type_name_list=>\@project_md_image_type_name_list,
  38     limit=>$limit,
  39     offset=>$offset
  40 });
  41 my ($result, $total_count) = $image_search->search();
  42
  43 =head1 DESCRIPTION
  44
  45
  46 =head1 AUTHORS
  47
  48
  49 =cut
  50
  51 use strict;
  52 use warnings;
  53 use Moose;
  54 use Try::Tiny;
  55 use Data::Dumper;
  56 use SGN::Model::Cvterm;
  57 use JSON;
  58
  59 has 'bcs_schema' => ( isa => 'Bio::Chado::Schema',
  60     is => 'rw',
  61     required => 1,
  62 );
  63
  64 has 'image_id_list' => (
  65     isa => 'ArrayRef[Int]|Undef',
  66     is => 'rw',
  67 );
  68
  69 has 'image_names_exact' => (
  70     isa => 'Bool|Undef',
  71     is => 'rw',
  72     default => 0
  73 );
  74
  75 has 'image_name_list' => (
  76     isa => 'ArrayRef[Str]|Undef',
  77     is => 'rw',
  78 );
  79
  80 has 'include_obsolete_images' => (
  81     isa => 'Bool|Undef',
  82     is => 'rw',
  83     default => 0
  84 );
  85
  86 has 'original_filenames_exact' => (
  87     isa => 'Bool|Undef',
  88     is => 'rw',
  89     default => 0
  90 );
  91
  92 has 'original_filename_list' => (
  93     isa => 'ArrayRef[Str]|Undef',
  94     is => 'rw',
  95 );
  96
  97 has 'descriptions_exact' => (
  98     isa => 'Bool|Undef',
  99     is => 'rw',
 100     default => 0
 101 );
 102
 103 has 'description_list' => (
 104     isa => 'ArrayRef[Str]|Undef',
 105     is => 'rw',
 106 );
 107
 108 has 'tag_list_exact' => (
 109     isa => 'Bool|Undef',
 110     is => 'rw',
 111     default => 0
 112 );
 113
 114 has 'tag_list' => (
 115     isa => 'ArrayRef[Str]|Undef',
 116     is => 'rw',
 117 );
 118
 119 has 'include_obsolete_tags' => (
 120     isa => 'Bool|Undef',
 121     is => 'rw',
 122     default => 0
 123 );
 124
 125 has 'include_obsolete_image_tags' => (
 126     isa => 'Bool|Undef',
 127     is => 'rw',
 128     default => 0
 129 );
 130
 131 has 'submitter_usernames_exact' => (
 132     isa => 'Bool|Undef',
 133     is => 'rw',
 134     default => 0
 135 );
 136
 137 has 'submitter_username_list' => (
 138     isa => 'ArrayRef[Str]|Undef',
 139     is => 'rw',
 140 );
 141
 142 has 'submitter_first_names_exact' => (
 143     isa => 'Bool|Undef',
 144     is => 'rw',
 145     default => 0
 146 );
 147
 148 has 'submitter_first_name_list' => (
 149     isa => 'ArrayRef[Str]|Undef',
 150     is => 'rw',
 151 );
 152
 153 has 'submitter_last_names_exact' => (
 154     isa => 'Bool|Undef',
 155     is => 'rw',
 156     default => 0
 157 );
 158
 159 has 'submitter_last_name_list' => (
 160     isa => 'ArrayRef[Str]|Undef',
 161     is => 'rw',
 162 );
 163
 164 has 'submitter_id_list' => (
 165     isa => 'ArrayRef[Int]|Undef',
 166     is => 'rw',
 167 );
 168
 169 has 'stock_type' => (
 170     isa => 'Str|Undef',
 171     is => 'rw',
 172 );
 173
 174 has 'stock_id_list' => (
 175     isa => 'ArrayRef[Int]|Undef',
 176     is => 'rw',
 177 );
 178
 179 has 'stock_names_exact' => (
 180     isa => 'Bool|Undef',
 181     is => 'rw',
 182     default => 0
 183 );
 184
 185 has 'stock_name_list' => (
 186     isa => 'ArrayRef[Str]|Undef',
 187     is => 'rw',
 188 );
 189
 190 has 'project_id_list' => (
 191     isa => 'ArrayRef[Int]|Undef',
 192     is => 'rw',
 193 );
 194
 195 has 'project_names_exact' => (
 196     isa => 'Bool|Undef',
 197     is => 'rw',
 198     default => 0
 199 );
 200
 201 has 'project_name_list' => (
 202     isa => 'ArrayRef[Str]|Undef',
 203     is => 'rw',
 204 );
 205
 206 has 'project_md_image_type_names_exact' => (
 207     isa => 'Bool|Undef',
 208     is => 'rw',
 209     default => 0
 210 );
 211
 212 has 'project_md_image_type_name_list' => (
 213     isa => 'ArrayRef[Str]|Undef',
 214     is => 'rw',
 215 );
 216
 217 has 'limit' => (
 218     isa => 'Int|Undef',
 219     is => 'rw'
 220 );
 221
 222 has 'offset' => (
 223     isa => 'Int|Undef',
 224     is => 'rw'
 225 );
 226
 227 sub search {
 228     my $self = shift;
 229     my $schema = $self->bcs_schema();
 230     my $image_id_list = $self->image_id_list;
 231     my $image_name_list = $self->image_name_list;
 232     my $image_names_exact = $self->image_names_exact;
 233     my $original_filename_list = $self->original_filename_list;
 234     my $original_filenames_exact = $self->original_filenames_exact;
 235     my $description_list = $self->description_list;
 236     my $descriptions_exact = $self->descriptions_exact;
 237     my $tag_list = $self->tag_list;
 238     my $tag_list_exact = $self->tag_list_exact;
 239     my $submitter_username_list = $self->submitter_username_list;
 240     my $submitter_usernames_exact = $self->submitter_usernames_exact;
 241     my $submitter_first_name_list = $self->submitter_first_name_list;
 242     my $submitter_first_names_exact = $self->submitter_first_names_exact;
 243     my $submitter_last_name_list = $self->submitter_last_name_list;
 244     my $submitter_last_names_exact = $self->submitter_last_names_exact;
 245     my $submitter_id_list = $self->submitter_id_list;
 246     my $stock_type = $self->stock_type;
 247     my $stock_id_list = $self->stock_id_list;
 248     my $stock_name_list = $self->stock_name_list;
 249     my $stock_names_exact = $self->stock_names_exact;
 250     my $project_id_list = $self->project_id_list;
 251     my $project_name_list = $self->project_name_list;
 252     my $project_names_exact = $self->project_names_exact;
 253     my $project_md_image_type_name_list = $self->project_md_image_type_name_list;
 254     my $project_md_image_type_names_exact = $self->project_md_image_type_names_exact;
 255     my $include_obsolete_images = $self->include_obsolete_images;
 256     my $include_obsolete_tags = $self->include_obsolete_tags;
 257     my $include_obsolete_image_tags = $self->include_obsolete_image_tags;
 258
 259     my @where_clause;
 260     my @or_clause;
 261
 262
 263     my @question_mark_values;
 264
 265     if ($stock_type){
 266         my $stock_type_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, $stock_type, 'stock_type')->cvterm_id();
 267         push @where_clause, "stock.type_id = ?";
 268         push @question_mark_values, $stock_type_cvterm_id;
 269
 270     }
 271
 272     if ($image_id_list && scalar(@$image_id_list)>0) {
 273         my $sql = join ("," , @$image_id_list);
 274         push @where_clause, "image.image_id in (?)";
 275         push @question_mark_values, $sql;
 276     }
 277     if ($image_name_list && scalar(@$image_name_list)>0) {
 278         if ($image_names_exact) {
 279             my $sql = join ("','" , @$image_name_list);
 280             my $name_sql = "'" . $sql . "'";
 281             push @where_clause, "image.name in (?)";
 282             push @question_mark_values, $name_sql;
 283         } else {
 284             foreach (@$image_name_list) {
 285                 push @or_clause, "image.name ilike ?";
 286                 push @question_mark_values, $_;
 287             }
 288         }
 289     }
 290     if ($original_filename_list && scalar(@$original_filename_list)>0) {
 291         if ($original_filenames_exact) {
 292             my $sql = join ("','" , @$original_filename_list);
 293             my $name_sql = "'" . $sql . "'";
 294             push @where_clause, "image.original_filename in (?)";
 295             push @question_mark_values, $name_sql;
 296         } else {
 297             foreach (@$original_filename_list) {
 298                 push @or_clause, "image.original_filename ilike ?";
 299                 push @question_mark_values, $_;
 300             }
 301         }
 302     }
 303     if ($description_list && scalar(@$description_list)>0) {
 304         if ($descriptions_exact) {
 305             my $sql = join ("','" , @$description_list);
 306             my $name_sql = "'" . $sql . "'";
 307             push @where_clause, "image.description in (?)";
 308             push @question_mark_values, $name_sql;
 309         } else {
 310             foreach (@$description_list) {
 311                 push @or_clause, "image.description ilike ?";
 312                 push @question_mark_values, $_;
 313             }
 314         }
 315     }
 316     if ($tag_list && scalar(@$tag_list)>0) {
 317         if ($tag_list_exact) {
 318             my $sql = join ("','" , @$tag_list);
 319             my $name_sql = "'" . $sql . "'";
 320             push @where_clause, "tags.name in (?)";
 321             push @question_mark_values, $name_sql;
 322         } else {
 323             foreach (@$tag_list) {
 324                 push @or_clause, "tags.name ilike ?";
 325                 push @question_mark_values, $_;
 326             }
 327         }
 328         if (!$include_obsolete_tags) {
 329             push @where_clause, "image_tag.obsolete = 'f'";
 330         }
 331         if (!$include_obsolete_image_tags) {
 332             push @where_clause, "tags.obsolete = 'f'";
 333         }
 334     }
 335     if ($submitter_username_list && scalar(@$submitter_username_list)>0) {
 336         if ($submitter_usernames_exact) {
 337             my $sql = join ("','" , @$submitter_username_list);
 338             my $name_sql = "'" . $sql . "'";
 339             push @where_clause, "submitter.username in (?)";
 340             push @question_mark_values, $name_sql;
 341         } else {
 342             foreach (@$submitter_username_list) {
 343                 push @or_clause, "submitter.username ilike ?";
 344                 push @question_mark_values, $_;
 345             }
 346         }
 347     }
 348     if ($submitter_first_name_list && scalar(@$submitter_first_name_list)>0) {
 349         if ($submitter_first_names_exact) {
 350             my $sql = join ("','" , @$submitter_first_name_list);
 351             my $name_sql = "'" . $sql . "'";
 352             push @where_clause, "submitter.first_name in (?)";
 353             push @question_mark_values, $name_sql;
 354         } else {
 355             foreach (@$submitter_first_name_list) {
 356                 push @or_clause, "submitter.first_name ilike ?";
 357                 push @question_mark_values, $_;
 358             }
 359         }
 360     }
 361     if ($submitter_last_name_list && scalar(@$submitter_last_name_list)>0) {
 362         if ($submitter_last_names_exact) {
 363             my $sql = join ("','" , @$submitter_last_name_list);
 364             my $name_sql = "'" . $sql . "'";
 365             push @where_clause, "submitter.last_name in (?)";
 366             push @question_mark_values, $name_sql;
 367         } else {
 368             foreach (@$submitter_last_name_list) {
 369                 push @or_clause, "submitter.last_name ilike ?";
 370                 push @question_mark_values, $_;
 371             }
 372         }
 373     }
 374     if ($submitter_id_list && scalar(@$submitter_id_list)>0) {
 375         my $sql = join ("," , @$submitter_id_list);
 376         push @where_clause, "submitter.sp_person_id in (?)";
 377         push @question_mark_values, $sql;
 378
 379     }
 380     if ($stock_id_list && scalar(@$stock_id_list)>0) {
 381         my $sql = join ("," , @$stock_id_list);
 382         push @where_clause, "stock.stock_id in (?)";
 383         push @question_mark_values, $sql;
 384     }
 385     if ($stock_name_list && scalar(@$stock_name_list)>0) {
 386         if ($stock_names_exact) {
 387             my $sql = join ("','" , @$stock_name_list);
 388             my $name_sql = "'" . $sql . "'";
 389             push @where_clause, "stock.uniquename in (?)";
 390             push @question_mark_values, $name_sql;
 391         } else {
 392             foreach (@$stock_name_list) {
 393                 push @or_clause, "stock.uniquename ilike ?";
 394                 push @question_mark_values, $_;
 395             }
 396         }
 397     }
 398     if ($project_id_list && scalar(@$project_id_list)>0) {
 399         my $sql = join ("," , @$project_id_list);
 400         push @where_clause, "project.project_id in (?)";
 401         push @question_mark_values, $sql;
 402     }
 403     if ($project_name_list && scalar(@$project_name_list)>0) {
 404         if ($project_names_exact) {
 405             my $sql = join ("','" , @$project_name_list);
 406             my $name_sql = "'" . $sql . "'";
 407             push @where_clause, "project.name in (?)";
 408             push @question_mark_values, $name_sql;
 409         } else {
 410             foreach (@$project_name_list) {
 411                 push @or_clause, "project.name ilike ?";
 412                 push @question_mark_values, $_;
 413             }
 414         }
 415     }
 416     if ($project_md_image_type_name_list && scalar(@$project_md_image_type_name_list)>0) {
 417         if ($project_md_image_type_names_exact) {
 418             my $sql = join ("','" , @$project_md_image_type_name_list);
 419             my $name_sql = "'" . $sql . "'";
 420             push @where_clause, "project_image_type.name in (?)";
 421             push @question_mark_values, $name_sql;
 422         } else {
 423             foreach (@$project_md_image_type_name_list) {
 424                 push @or_clause, "project_image_type.name ilike ?";
 425                 push @question_mark_values, $_;
 426             }
 427         }
 428     }
 429     if (!$include_obsolete_images) {
 430         push @where_clause, "image.obsolete = 'f'";
 431     }
 432
 433     if (scalar(@or_clause)>0) {
 434         my $w = " ( ".(join (" OR ", @or_clause) )." ) ";
 435         push @where_clause, $w;
 436     }
 437     my $where_clause = scalar(@where_clause)>0 ? " WHERE " . (join (" AND " , @where_clause)) : '';
 438
 439     my $offset_clause = '';
 440     my $limit_clause = '';
 441     if ($self->limit){
 442         $limit_clause = " LIMIT ".$self->limit;
 443     }
 444     if ($self->offset){
 445         $offset_clause = " OFFSET ".$self->offset;
 446     }
 447
 448     my $q = "SELECT image.image_id, image.name, image.description, image.original_filename, image.file_ext, image.sp_person_id, submitter.username,
 449         to_char (image.create_date::timestamp at time zone current_setting('TIMEZONE'), 'YYYY-MM-DD\"T\"HH24:MI:SSOF:00') as create_date,
 450         to_char (image.modified_date::timestamp at time zone current_setting('TIMEZONE'), 'YYYY-MM-DD\"T\"HH24:MI:SSOF:00') as modified_date,
 451         image.obsolete, image.md5sum, stock.stock_id, stock.uniquename, stock_type.name, project.project_id, project.name, project_image.project_md_image_id, project_image_type.name,
 452         COALESCE(
 453             json_agg(json_build_object('tag_id', tags.tag_id, 'name', tags.name, 'description', tags.description, 'sp_person_id', tags.sp_person_id, 'modified_date', tags.modified_date, 'create_date', tags.create_date, 'obsolete', tags.obsolete))
 454             FILTER (WHERE tags.tag_id IS NOT NULL), '[]'
 455         ) AS tags,
 456         COALESCE(
 457             json_agg(json_build_object('phenotype_id', phenotype.phenotype_id, 'value', phenotype.value, 'observationvariable_name', phenotype_variable.name))
 458             FILTER (WHERE phenotype.phenotype_id IS NOT NULL), '[]'
 459         ) AS observations,
 460         count(image.image_id) OVER() AS full_count
 461         FROM metadata.md_image AS image
 462         JOIN sgn_people.sp_person AS submitter ON (submitter.sp_person_id=image.sp_person_id)
 463         LEFT JOIN metadata.md_tag_image AS image_tag ON (image.image_id=image_tag.image_id)
 464         LEFT JOIN metadata.md_tag AS tags ON (image_tag.tag_id=tags.tag_id)
 465         LEFT JOIN phenome.stock_image AS stock_image ON (image.image_id=stock_image.image_id)
 466         LEFT JOIN stock ON (stock_image.stock_id=stock.stock_id)
 467         LEFT JOIN cvterm AS stock_type ON (stock.type_id=stock_type.cvterm_id)
 468         LEFT JOIN phenome.project_md_image AS project_image ON(project_image.image_id=image.image_id)
 469         LEFT JOIN cvterm AS project_image_type ON(project_image.type_id=project_image_type.cvterm_id)
 470         LEFT JOIN project ON(project_image.project_id=project.project_id)
 471         LEFT JOIN phenome.nd_experiment_md_images AS nd_experiment_md_images ON(image.image_id = nd_experiment_md_images.image_id)
 472         LEFT JOIN nd_experiment_phenotype ON (nd_experiment_phenotype.nd_experiment_id = nd_experiment_md_images.nd_experiment_id)
 473         LEFT JOIN phenotype ON (nd_experiment_phenotype.phenotype_id = phenotype.phenotype_id)
 474         LEFT JOIN cvterm AS phenotype_variable ON (phenotype.cvalue_id=phenotype_variable.cvterm_id)
 475         $where_clause
 476         GROUP BY(image.image_id, image.name, image.description, image.original_filename, image.file_ext, image.sp_person_id, submitter.username, image.create_date, image.modified_date, image.obsolete, image.md5sum, stock.stock_id, stock.uniquename, stock_type.name, project.project_id, project.name, project_image.project_md_image_id, project_image_type.name)
 477         ORDER BY image.image_id
 478         $limit_clause
 479         $offset_clause;";
 480
 481     # print STDERR Dumper $q;
 482     my $h = $schema->storage->dbh()->prepare($q);
 483
 484     $h->execute(@question_mark_values);
 485
 486     my @result;
 487     my $total_count = 0;
 488     while (my ($image_id, $image_name, $image_description, $image_original_filename, $image_file_ext, $image_sp_person_id, $image_username, $image_create_date, $image_modified_date, $image_obsolete, $image_md5sum, $stock_id, $stock_uniquename, $stock_type_name, $project_id, $project_name, $project_md_image_id, $project_image_type_name, $tags, $observations, $full_count) = $h->fetchrow_array()) {
 489         push @result, {
 490             image_id => $image_id,
 491             image_name => $image_name,
 492             image_description => $image_description,
 493             image_original_filename => $image_original_filename,
 494             image_file_ext => $image_file_ext,
 495             image_sp_person_id => $image_sp_person_id,
 496             image_username => $image_username,
 497             image_create_date => $image_create_date,
 498             image_modified_date => $image_modified_date,
 499             image_obsolete => $image_obsolete,
 500             image_md5sum => $image_md5sum,
 501             stock_id => $stock_id,
 502             stock_uniquename => $stock_uniquename,
 503             stock_type_name => $stock_type_name,
 504             project_id => $project_id,
 505             project_name => $project_name,
 506             project_md_image_id => $project_md_image_id,
 507             project_image_type_name => $project_image_type_name,
 508             tags_array => decode_json $tags,
 509             observations_array => decode_json $observations,
 510         };
 511         $total_count = $full_count;
 512     }
 513     #print STDERR Dumper \@result;
 514     return (\@result, $total_count);
 515 }
 516
 517 1;