clean
[sgn.git] / lib / SGN / Controller / AJAX / Cvterm.pm
blob12f7521a8d684fdcc789e25c1353b348430e7dc6
2 =head1 NAME
4 SGN::Controller::AJAX::Cvterm - a REST controller class to provide the
5 backend for objects linked with cvterms
7 =head1 DESCRIPTION
9 Browse the cvterm database for selecting cvterms (ontology terms, and their evidece codes) to be linked with other objects
11 =head1 AUTHOR
13 Naama Menda <nm249@cornell.edu>
15 =cut
17 package SGN::Controller::AJAX::Cvterm;
19 use Moose;
21 use List::MoreUtils qw /any /;
22 use Try::Tiny;
23 use CXGN::Page::FormattingHelpers qw/ columnar_table_html commify_number /;
24 use CXGN::Chado::Cvterm;
25 use Data::Dumper;
27 BEGIN { extends 'Catalyst::Controller::REST' }
29 __PACKAGE__->config(
30 default => 'application/json',
31 stash_key => 'rest',
32 map => { 'application/json' => 'JSON', 'text/html' => 'JSON' },
36 =head2 autocomplete
38 Public Path: /ajax/cvterm/autocomplete
40 Autocomplete a cvterm name. Takes a single GET param,
41 C<term>, responds with a JSON array of completions for that term.
43 =cut
45 sub autocomplete : Local : ActionClass('REST') { }
47 sub autocomplete_GET :Args(0) {
48 my ( $self, $c ) = @_;
50 #my $term = $c->req->param('term_name');
51 my $db_name = $c->request->param('db_name');
52 # trim and regularize whitespace
53 #$term =~ s/(^\s+|\s+)$//g;
54 #$term =~ s/\s+/ /g;
55 my $term_name = $c->request->param("term");
57 my $schema = $c->dbic_schema('Bio::Chado::Schema', 'sgn_chado');
58 my $query = "SELECT distinct cvterm.cvterm_id as cvterm_id , cv.name as cv_name , cvterm.name as cvterm_name , db.name || ':' || dbxref.accession as accession
59 FROM db
60 JOIN dbxref USING (db_id ) JOIN cvterm USING (dbxref_id)
61 JOIN cv USING (cv_id )
62 LEFT JOIN cvtermsynonym USING (cvterm_id )
63 WHERE db.name = ? AND (cvterm.name ilike ? OR cvtermsynonym.synonym ilike ? OR cvterm.definition ilike ?) AND cvterm.is_obsolete = 0 AND is_relationshiptype = 0
64 GROUP BY cvterm.cvterm_id,cv.name, cvterm.name, dbxref.accession, db.name
65 ORDER BY cv.name, cvterm.name limit 30";
66 my $sth= $schema->storage->dbh->prepare($query);
67 $sth->execute($db_name, "\%$term_name\%", "\%$term_name\%", "\%$term_name\%");
68 my @response_list;
69 while (my ($cvterm_id, $cv_name, $cvterm_name, $accession) = $sth->fetchrow_array() ) {
70 push @response_list, $cv_name . "--" . $accession . "--" . $cvterm_name ;
72 $c->stash->{rest} = \@response_list;
75 sub relationships : Local : ActionClass('REST') { }
77 sub relationships_GET :Args(0) {
78 my ($self, $c) = @_;
79 my $relationship_query = $c->dbc->dbh->prepare("SELECT distinct(cvterm.cvterm_id), cvterm.name
80 FROM public.cvterm
81 JOIN public.cv USING (cv_id)
82 JOIN public.cvterm_relationship ON (cvterm.cvterm_id= cvterm_relationship.subject_id)
83 WHERE cv.name ='relationship' AND
84 cvterm.is_obsolete = 0
85 ORDER BY cvterm.name;
86 ");
87 $relationship_query->execute();
88 my $hashref={};
89 while ( my ($cvterm_id, $cvterm_name) = $relationship_query->fetchrow_array() ) {
90 $hashref->{$cvterm_name} = $cvterm_id;
92 $c->stash->{rest} = $hashref;
95 sub locus_relationships : Local : ActionClass('REST') { }
97 sub locus_relationships_GET :Args(0) {
98 my ($self, $c) = @_;
99 my $query = $c->dbc->dbh->prepare(
100 "SELECT distinct(cvterm.cvterm_id), cvterm.name
101 FROM public.cvterm
102 JOIN public.cv USING (cv_id)
103 WHERE cv.name ='Locus Relationship' AND
104 cvterm.is_obsolete = 0
105 ORDER BY cvterm.name;
107 $query->execute();
108 my $hashref={};
109 while ( my ($cvterm_id, $cvterm_name) = $query->fetchrow_array() ) {
110 $hashref->{$cvterm_name} = $cvterm_id;
112 $c->stash->{rest} = $hashref;
115 =head2
117 Public Path: /ajax/cvterm/evidence
119 get a list of available evidence codes from cvterms
120 responds with a JSON array .
122 =cut
124 sub evidence : Local : ActionClass('REST') { }
126 sub evidence_GET :Args(0) {
127 my ($self, $c) = @_;
128 my $query = $c->dbc->dbh->prepare(
129 "SELECT distinct(cvterm.cvterm_id), cvterm.name
130 FROM public.cvterm_relationship
131 JOIN public.cvterm ON (cvterm.cvterm_id= cvterm_relationship.subject_id)
132 WHERE object_id= (select cvterm_id FROM cvterm where name = 'evidence_code')
133 AND cvterm.is_obsolete = 0
134 ORDER BY cvterm.name" );
135 $query->execute();
136 my $hashref={};
137 while ( my ($cvterm_id, $cvterm_name) = $query->fetchrow_array() ) {
138 $hashref->{$cvterm_name} = $cvterm_id;
140 $c->stash->{rest} = $hashref;
144 sub evidence_description : Local : ActionClass('REST') { }
146 sub evidence_description_GET :Args(0) {
147 my ($self, $c) = @_;
148 my $evidence_code_id = $c->request->param("evidence_code_id");
149 my $query = $c->dbc->dbh->prepare("SELECT cvterm_id, cvterm.name FROM cvterm
150 JOIN cvterm_relationship ON cvterm_id=subject_id
151 WHERE object_id= (select cvterm_id FROM public.cvterm WHERE cvterm_id= ?)
152 AND cvterm.is_obsolete = 0"
154 $query->execute($evidence_code_id);
155 my $hashref={};
156 while ( my ($cvterm_id, $cvterm_name) = $query->fetchrow_array() ) {
157 $hashref->{$cvterm_name} = $cvterm_id;
159 $c->stash->{rest} = $hashref;
162 sub get_synonyms : Path('/ajax/cvterm/get_synonyms') Args(0) {
164 my $self = shift;
165 my $c = shift;
166 my @trait_ids = $c->req->param('trait_ids[]');
167 print STDERR "Trait ids = @trait_ids\n";
168 my $dbh = $c->dbc->dbh();
169 my $synonyms = {};
171 foreach my $trait_id (@trait_ids) {
172 print STDERR "Trait with trait id ".$trait_id;
173 my $cvterm = CXGN::Chado::Cvterm->new( $dbh, $trait_id );
174 my $found_cvterm_id = $cvterm->get_cvterm_id;
175 print STDERR "found cvterm_id = $found_cvterm_id";
176 my @synonyms = $cvterm->get_synonyms();
177 my $synonym = pop @synonyms;
178 print STDERR " has synonym: ".$synonym."\n";
179 $synonyms->{$trait_id} = $synonym;
182 $c->stash->{rest} = { synonyms => $synonyms };
186 sub get_annotated_stocks :Chained('/cvterm/get_cvterm') :PathPart('datatables/annotated_stocks') Args(0) {
187 my ($self, $c) = @_;
188 my $cvterm = $c->stash->{cvterm};
189 my $cvterm_id = $cvterm->get_cvterm_id;
190 my $q = <<'';
191 SELECT DISTINCT
192 type.name,
193 stock_id,
194 stock.uniquename,
195 stock.description
196 FROM cvtermpath
197 JOIN cvterm on (cvtermpath.object_id = cvterm.cvterm_id OR cvtermpath.subject_id = cvterm.cvterm_id )
198 JOIN stock_cvterm on (stock_cvterm.cvterm_id = cvterm.cvterm_id)
199 JOIN stock USING (stock_id)
200 JOIN cvterm as type on type.cvterm_id = stock.type_id
201 WHERE cvtermpath.object_id = ?
202 AND stock.is_obsolete = ?
203 AND pathdistance > 0
204 AND 0 = ( SELECT COUNT(*)
205 FROM stock_cvtermprop p
206 WHERE type_id IN ( SELECT cvterm_id FROM cvterm WHERE name = 'obsolete' )
207 AND p.stock_cvterm_id = stock_cvterm.stock_cvterm_id
208 AND value = '1'
210 ORDER BY stock.uniquename
212 my $sth = $c->dbc->dbh->prepare($q);
213 my $rows = $c->stash->{rest}{count} = 0 + $sth->execute($cvterm_id, 'false');
215 my @stock_data;
216 while ( my ($type, $stock_id , $stock_name, $description) = $sth->fetchrow_array ) {
217 my $stock_link = qq|<a href="/stock/$stock_id/view">$stock_name</a> |;
218 push @stock_data, [
219 $type,
220 $stock_link,
221 $description,
224 $c->stash->{rest} = { data => \@stock_data, };
229 sub get_annotated_loci :Chained('/cvterm/get_cvterm') :PathPart('datatables/annotated_loci') Args(0) {
230 my ($self, $c) = @_;
231 my $cvterm = $c->stash->{cvterm};
232 my $cvterm_id = $cvterm->get_cvterm_id;
234 my $q = "SELECT DISTINCT locus_id, locus_name, locus_symbol, common_name FROM cvtermpath
235 JOIN cvterm ON (cvtermpath.object_id = cvterm.cvterm_id OR cvtermpath.subject_id = cvterm.cvterm_id)
236 JOIN phenome.locus_dbxref USING (dbxref_id )
237 JOIN phenome.locus USING (locus_id)
238 JOIN sgn.common_name USING (common_name_id)
239 WHERE (cvtermpath.object_id = ?) AND locus_dbxref.obsolete = 'f' AND locus.obsolete = 'f' AND pathdistance > 0";
241 my $sth = $c->dbc->dbh->prepare($q);
242 $sth->execute($cvterm_id);
243 my @data;
244 while ( my ($locus_id, $locus_name, $locus_symbol, $common_name) = $sth->fetchrow_array ) {
245 my $link = qq|<a href="/locus/$locus_id/view">$locus_symbol</a> |;
246 push @data,
249 $common_name,
250 $link,
251 $locus_name,
255 $c->stash->{rest} = { data => \@data, };
260 sub get_phenotyped_stocks :Chained('/cvterm/get_cvterm') :PathPart('datatables/phenotyped_stocks') Args(0) {
261 my ($self, $c) = @_;
262 my $cvterm = $c->stash->{cvterm};
263 my $cvterm_id = $cvterm->get_cvterm_id;
265 my $q = "SELECT DISTINCT stock_id, stock.uniquename, stock.description, type.name
266 FROM cvtermpath
267 JOIN cvterm ON (cvtermpath.object_id = cvterm.cvterm_id
268 OR cvtermpath.subject_id = cvterm.cvterm_id )
269 JOIN phenotype on cvterm.cvterm_id = phenotype.observable_id
270 JOIN nd_experiment_phenotype USING (phenotype_id)
271 JOIN nd_experiment_stock USING (nd_experiment_id)
272 JOIN stock USING (stock_id)
273 JOIN cvterm as type on type.cvterm_id = stock.type_id
275 WHERE pathdistance > 0
276 AND cvtermpath.object_id = ? ORDER BY stock_id " ;
279 my $sth = $c->dbc->dbh->prepare($q);
280 $sth->execute($cvterm_id) ;
281 #$c->stash->{rest}{count} = 0 + $sth->execute($cvterm_id);
282 my @data;
283 while ( my ($stock_id, $stock_name, $description, $type) = $sth->fetchrow_array ) {
284 my $link = qq|<a href="/stock/$stock_id/view">$stock_name</a> |;
285 push @data, [
286 $type,
287 $link,
288 $description,
291 $c->stash->{rest} = { data => \@data, };
294 sub get_direct_trials :Chained('/cvterm/get_cvterm') :PathPart('datatables/direct_trials') Args(0) {
295 my ($self, $c) = @_;
296 my $cvterm = $c->stash->{cvterm};
297 my $cvterm_id = $cvterm->get_cvterm_id;
298 my $q = "SELECT DISTINCT project_id, project.name, project.description
299 FROM public.project
300 JOIN nd_experiment_project USING (project_id)
301 JOIN nd_experiment_stock USING (nd_experiment_id)
302 JOIN nd_experiment_phenotype USING (nd_experiment_id)
303 JOIN phenotype USING (phenotype_id)
304 JOIN cvterm on cvterm.cvterm_id = phenotype.observable_id
305 WHERE observable_id = ?
308 my $sth = $c->dbc->dbh->prepare($q);
309 my $count = 0 + $sth->execute($cvterm_id );
310 my @data;
311 while ( my ($project_id, $project_name, $description) = $sth->fetchrow_array ) {
312 my $link = qq|<a href="/breeders/trial/$project_id">$project_name</a> |;
313 push @data,
315 $link,
316 $description,
319 $c->stash->{rest} = { data => \@data, count => $count };