We're Hiring!

HQL queries and other short examples

General and open developer discussion about using OMERO APIs from C++, Java, Python, Matlab and more! Please new questions at https://forum.image.sc/tags/omero
Please note:
Historical discussions about OMERO. Please look for and ask new questions at https://forum.image.sc/tags/omero

If you are having trouble with custom code, please provide a link to a public repository, ideally GitHub.

HQL queries and other short examples

Postby jmoore » Fri Sep 04, 2009 3:45 pm

Feel free to add, request, or comment on any HQL queries or other short snippets of OME code.
User avatar
jmoore
Site Admin
 
Posts: 1591
Joined: Fri May 22, 2009 1:29 pm
Location: Germany

Re: HQL queries and other short examples

Postby jmoore » Fri Sep 04, 2009 3:46 pm

Loading annotations for a Plate in Matlab:
Code: Select all
[c,s]=loadOmero;
try
  imetadata = s.getMetadataService();
  ids = java.util.ArrayList();
  ids.add(java.lang.Long(51));
  anns = imetadata.loadAnnotations('Plate', ids, [],[],[]);
  disp(anns);
catch ME
  disp(ME);
  disp(char(ME.message));
end
c.closeSession();
clear c
clear s
clear imetadata
clear anns
unloadOmero;
User avatar
jmoore
Site Admin
 
Posts: 1591
Joined: Fri May 22, 2009 1:29 pm
Location: Germany

Re: HQL queries and other short examples

Postby jmoore » Fri Sep 04, 2009 3:47 pm

Changing plate status from Matlab
Code: Select all
[c,s]=loadOmero;
try
  iquery = s.getQueryService();
  iupdate = s.getUpdateService();
 
  filter = omero.sys.Filter();
  filter.limit = omero.rtypes.rint(1);
  plate = iquery.findAll('Plate',filter).get(0);
  plate.setStatus( omero.rtypes.rstring('first plate I found') );
  plate = iupdate.saveAndReturnObject( plate );
 
  disp(plate.getStatus().getValue());
 
catch ME
  disp(ME);
  disp(char(ME.message));
end
c.closeSession();
clear c
clear s
clear iquery
clear iupdate
clear filter
clear plate
unloadOmero;
User avatar
jmoore
Site Admin
 
Posts: 1591
Joined: Fri May 22, 2009 1:29 pm
Location: Germany

Re: HQL queries and other short examples

Postby bernhard » Wed Dec 16, 2009 3:01 pm

Hi Josh!

I'd like to list all FileAnnotations having a certain namespace, some of those are linked to ScreenAcquisitions and if this is the case I'd like to join the ScreenAcquisistion as well. In pure SQL I'd do something like this:

SELECT a.id, a.ns, a.file, saal.id, saal.child, saal.parent
FROM annotation a
LEFT OUTER JOIN screenacquisitionannotationlink saal ON saal.child=a.id
WHERE a.discriminator='/type/OriginalFile/' and a.ns='special';

Unfortunately I did not know how to translate the outer join into HQL - is this possible at all?

Thanks for your help! Bernhard
bernhard
 
Posts: 37
Joined: Mon Jun 22, 2009 7:18 am

Re: HQL queries and other short examples

Postby jmoore » Wed Dec 16, 2009 3:58 pm

Hi Bernhard,

unfortunately a join from annotation to anything is not possible in HQL, since annotations are one of the (few) unidirectional types in OMERO. You will have to use two queries to do what you want. Sorry. ~Josh.
User avatar
jmoore
Site Admin
 
Posts: 1591
Joined: Fri May 22, 2009 1:29 pm
Location: Germany

Re: HQL queries and other short examples

Postby icaoberg » Mon Nov 14, 2011 5:05 pm

I know I can get the comment annotation given the comment id as
Code: Select all
%get comment from an image
query = session.getQueryService();
parameters = omero.sys.Parameters();
parameters.map = {}
parameters.map["id"] = omero.rtypes.rlong( 899878 );
result = query.findAllByQuery( "select comment from CommentAnnotation comment where comment.id=:id", parameters )


But now I am trying to get comment given an image id. Can you help me out with the query?

Ivan
icaoberg
 
Posts: 145
Joined: Fri Sep 17, 2010 9:05 pm
Location: Pittsburgh, PA

Re: HQL queries and other short examples

Postby jmoore » Mon Nov 14, 2011 6:59 pm

The following should work:
Code: Select all
select c from ImageAnnotationLink l, CommentAnnotation c join l.child where l.child = c and l.parent.id = :id


To go from an annotation up to other objects, you need to use the links themselves.

Cheers,
~Josh
User avatar
jmoore
Site Admin
 
Posts: 1591
Joined: Fri May 22, 2009 1:29 pm
Location: Germany

Re: HQL queries and other short examples

Postby icaoberg » Thu Nov 17, 2011 8:21 pm

sorry i am a little lost. how can i retrieve the actual comment from this object?
icaoberg
 
Posts: 145
Joined: Fri Sep 17, 2010 9:05 pm
Location: Pittsburgh, PA

Re: HQL queries and other short examples

Postby wmoore » Thu Nov 17, 2011 9:37 pm

Code: Select all
params = omero.sys.Parameters()
params.map = {'id': rlong(imageId)}
query = "select c from ImageAnnotationLink l, CommentAnnotation c join l.child where l.child = c and l.parent.id = :id"
queryService = conn.getQueryService()
anns = queryService.findAllByQuery(query, params)

for a in anns:
    print a.getTextValue().getValue()

User avatar
wmoore
Team Member
 
Posts: 674
Joined: Mon May 18, 2009 12:46 pm

Re: HQL queries and other short examples

Postby icaoberg » Thu Apr 26, 2012 6:54 pm

is it possible to retrieve the number of file annotations given a filename. i tried

Code: Select all
string = "select fileann from FileAnnotation  where fileann.file.name=:filename"


but failed. :(
icaoberg
 
Posts: 145
Joined: Fri Sep 17, 2010 9:05 pm
Location: Pittsburgh, PA

Next

Return to Developer Discussion

Who is online

Users browsing this forum: No registered users and 1 guest