HQL queries and other short examples

[jmoore]

Feel free to add, request, or comment on any HQL queries or other short snippets of OME code.

[jmoore]

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;


[jmoore]

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;


[bernhard]

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

[jmoore]

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.

[icaoberg]

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

[jmoore]

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

[icaoberg]

sorry i am a little lost. how can i retrieve the actual comment from this object?

[wmoore]

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()



[icaoberg]

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.