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.

Re: HQL queries and other short examples

Postby wmoore » Thu Apr 26, 2012 9:23 pm

Code: Select all
query = "select fileann from FileAnnotation fileann join fileann.file as f where f.name = :filename"


If you need to retrieve other details of the file itself, use "fetch"

Code: Select all
query = "select fileann from FileAnnotation fileann join fetch fileann.file as f where f.name = :filename"
User avatar
wmoore
Team Member
 
Posts: 674
Joined: Mon May 18, 2009 12:46 pm

Re: HQL queries and other short examples

Postby jmoore » Fri Apr 27, 2012 8:46 am

Ivan,

can you show your complete example along with what error message you are receiving? In general, to retrieve a count you should use:
Code: Select all
select count(f) from FileAnnotation f where f.file.name = 'example.jpg'


This returns a list of lists of RTypes. So to unwrap the result, you'll need:
Code: Select all
rv = iquery.projection(...)
count = rv[0][0].val


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 PaulVanSchayck » Wed Feb 11, 2015 3:20 pm

For backup and automated testing purposes I'm trying to retrieve all files from a specific user from the OMERO data dir.

Copying the managedrepository files has been no problem. Now, I'm trying to copy the pyramid files from Pixels/. I've been trying this HQL query:

Code: Select all
omero hql -q --all --style plain --limit -1 --ids-only 'from Pixels where details.owner = 2'


But this returns for me many IDs which do not have an associated Pixel file. While I could ignore this, I'm wondering why, and whether I can improve the query.
PaulVanSchayck
 
Posts: 41
Joined: Tue May 20, 2014 7:04 am

Re: HQL queries and other short examples

Postby mtbc » Wed Feb 11, 2015 4:33 pm

Regarding the "why", since OMERO 5.0 a Pixels file is not usually generated for imported images. http://ome.github.io/presentations/2013 ... -paris/#/3 describes a "big images without subresolutions" caveat: basically, one will have Pixels files for OMERO 4 images and for large images of just certain formats such as JPEG. Additionally, I believe that images generated by scripts may have a Pixels file. Wherever possible, the OMERO server uses the original image files, and Bio-Formats on the fly for rendering, in trying to prevent users from having to duplicate their data anywhere.

The "whether" is also a good question and I do hope that somebody can help with that one.

Cheers,
Mark
User avatar
mtbc
Team Member
 
Posts: 282
Joined: Tue Oct 23, 2012 10:59 am
Location: Dundee, Scotland

Re: HQL queries and other short examples

Postby mtbc » Mon Feb 16, 2015 10:34 am

There is a shortage of ideas from others about the "whether" I'm afraid! From the client, one's principal clues are image dimensions and format and if there is an associated fileset. In HQL, to find the images that do not have a fileset, thus probably do have a file under the Pixels directory,

Code: Select all
SELECT id FROM Image WHERE fileset IS NULL


For images with filesets, for which only large enough images in certain formats will have a file under the Pixels directory, there will generally also be a Bio-Formats reader already noted for them, permitting in SQL,

Code: Select all
SELECT i.id, p.sizex, p.sizey, vi.value FROM image i, pixels p, fileset f, filesetjoblink fjl, uploadjob uj, uploadjob_versioninfo vi WHERE i.id = p.image AND i.fileset = f.id AND fjl.parent = f.id AND fjl.child = uj.job_id AND uj.job_id = vi.uploadjob_id AND vi.name = 'bioformats.reader'


Unfortunately, what that latter query would be in HQL, I've no idea; since the latest changes for 5.1 I do not yet know how to query the new map values' key-value pairs from HQL in this kind of situation. However, it has to be possible, even if it is best split up into multiple queries.

If you have further questions or ideas on this issue please do feel free to bounce them off us. I will make a note that we should look again at the Pixels directory issue, especially for your use case, and I'll check again if someone else can add more to this thread here.

Cheers,
Mark
User avatar
mtbc
Team Member
 
Posts: 282
Joined: Tue Oct 23, 2012 10:59 am
Location: Dundee, Scotland

Re: HQL queries and other short examples

Postby mtbc » Tue Feb 17, 2015 11:49 am

I realized that one workaround exists from OMERO 5.1 onward: using the disk usage counter to check what file storage is directly associated with an image's Pixels object. This would include pyramids, which also appear in the Pixels/ directory, but would not include empty files. Filling in the login details appropriately, a simple script might be,

Code: Select all
from omero.callbacks import CmdCallbackI
from omero.cmd import DiskUsage, DoAll
from omero.gateway import BlitzGateway

conn = BlitzGateway(username, password, host=server_host, port=server_port)
conn.connect()

query = 'SELECT id FROM Image ORDER BY id'
ids = [result[0].val for result in
       conn.getQueryService().projection(query, None)]

do_all = DoAll()
do_all.requests = [DiskUsage(objects={'Image': [id]}) for id in ids]

prx = conn.c.sf.submit(do_all)

have_pixels = []

for response in CmdCallbackI(conn.c, prx).loop(500, 500).responses:
    have_pixels.append(any('Pixels' in counts
                           for counts in response.fileCountByReferer.values()))

conn._closeSession()

for report in zip(ids, have_pixels):
    print "Image id #%s has pixels? %s" % report


I hope that helps.

Cheers,
Mark
User avatar
mtbc
Team Member
 
Posts: 282
Joined: Tue Oct 23, 2012 10:59 am
Location: Dundee, Scotland

Re: HQL queries and other short examples

Postby PaulVanSchayck » Tue Feb 17, 2015 2:55 pm

Hi Mark,

Thanks for your detailed help. It's interesting to know that for many formats OMERO will no longer build pyramids, I don't realize that yet. Unfortunately, we are working with true JPEG files, with pixel sizes which have a mix between being pyramided or not.

I will keep your last solution in mind, and revisit this when version 5.1 is deployed. For now, I will simply ignore non existent files.

Thanks,

Paul
PaulVanSchayck
 
Posts: 41
Joined: Tue May 20, 2014 7:04 am

Re: HQL queries and other short examples

Postby mtbc » Mon Aug 03, 2015 11:50 am

I should follow up to mention that http://www.openmicroscopy.org/site/supp ... nd-queries now provides some HQL map annotation examples.

Cheers,
Mark
User avatar
mtbc
Team Member
 
Posts: 282
Joined: Tue Oct 23, 2012 10:59 am
Location: Dundee, Scotland

Re: HQL queries and other short examples

Postby evenhuis » Tue Oct 02, 2018 11:09 am

Hi,

still learning SQL and HQL so a super basic question... how do I join two tables in HQL?

I've rebuilt a psql database from a dump so I can get used to structure of the tables. Simple queries like this work in sql:

Code: Select all
SELECT i.name,e.firstName,e.lastName  FROM Image i  LEFT OUTER JOIN Experimenter e ON (e.id=i.owner_id)


but I can't get the HQL query to work. There some stack overflow questions that indicate that his type of is possible in HQL and the there needs to be a relationship between the two table foreign and primary key:
https://stackoverflow.com/questions/18545390/how-to-perform-left-join-in-hibernate-query-language

Any pointers would be greatly appreciated,

Chris
evenhuis
 
Posts: 61
Joined: Tue Jan 30, 2018 4:47 am

Re: HQL queries and other short examples

Postby manics » Tue Oct 02, 2018 12:27 pm

OMERO already contains some magic to return the details of the user who created an object, e.g.
Code: Select all
omero hql 'SELECT name, details.owner.firstName, details.owner.lastName FROM Image'
User avatar
manics
Team Member
 
Posts: 261
Joined: Mon Oct 08, 2012 11:01 am
Location: Dundee

PreviousNext

Return to Developer Discussion

Who is online

Users browsing this forum: Bing [Bot] and 1 guest

cron