4 SGN::Controller::AJAX::Cvterm - a REST controller class to provide the
5 backend for objects linked with cvterms
9 Browse the cvterm database for selecting cvterms (ontology terms, and their evidece codes) to be linked with other objects
13 Naama Menda <nm249@cornell.edu>
17 package SGN
::Controller
::AJAX
::Cvterm
;
21 use List
::MoreUtils qw
/any /;
23 use CXGN
::Page
::FormattingHelpers qw
/ columnar_table_html commify_number /;
24 use CXGN
::Chado
::Cvterm
;
27 BEGIN { extends
'Catalyst::Controller::REST' }
30 default => 'application/json',
32 map => { 'application/json' => 'JSON', 'text/html' => 'JSON' },
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.
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;
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
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\%");
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) {
79 my $relationship_query = $c->dbc->dbh->prepare("SELECT distinct(cvterm.cvterm_id), cvterm.name
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
87 $relationship_query->execute();
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) {
99 my $query = $c->dbc->dbh->prepare(
100 "SELECT distinct(cvterm.cvterm_id), cvterm.name
102 JOIN public.cv USING (cv_id)
103 WHERE cv.name ='Locus Relationship' AND
104 cvterm.is_obsolete = 0
105 ORDER BY cvterm.name;
109 while ( my ($cvterm_id, $cvterm_name) = $query->fetchrow_array() ) {
110 $hashref->{$cvterm_name} = $cvterm_id;
112 $c->stash->{rest
} = $hashref;
117 Public Path: /ajax/cvterm/evidence
119 get a list of available evidence codes from cvterms
120 responds with a JSON array .
124 sub evidence
: Local
: ActionClass
('REST') { }
126 sub evidence_GET
:Args
(0) {
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" );
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) {
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);
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) {
166 my @trait_ids = $c->req->param('trait_ids[]');
167 print STDERR
"Trait ids = @trait_ids\n";
168 my $dbh = $c->dbc->dbh();
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) {
188 my $cvterm = $c->stash->{cvterm
};
189 my $cvterm_id = $cvterm->get_cvterm_id;
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
= ?
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
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');
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
> |;
224 $c->stash->{rest
} = { data
=> \
@stock_data, };
229 sub get_annotated_loci
:Chained
('/cvterm/get_cvterm') :PathPart
('datatables/annotated_loci') Args
(0) {
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);
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
> |;
255 $c->stash->{rest
} = { data
=> \
@data, };
260 sub get_phenotyped_stocks
:Chained
('/cvterm/get_cvterm') :PathPart
('datatables/phenotyped_stocks') Args
(0) {
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
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);
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
> |;
291 $c->stash->{rest
} = { data
=> \
@data, };
294 sub get_direct_trials
:Chained
('/cvterm/get_cvterm') :PathPart
('datatables/direct_trials') Args
(0) {
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
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 );
311 while ( my ($project_id, $project_name, $description) = $sth->fetchrow_array ) {
312 my $link = qq|<a href
="/breeders/trial/$project_id">$project_name</a
> |;
319 $c->stash->{rest
} = { data
=> \
@data, count
=> $count };