[ome-devel] how to select annotations based on two annotation links

[josh.moore at gmx.de]

Hi Bernhard,

I've attached a python script which does what I think you want it to
do. The query it uses is:

     SELECT sann
       FROM ScreenAcquisition   sa,
            Well                w
       JOIN sa.annotationLinks  sal
       JOIN sal.child           sann
       JOIN w.annotationLinks   wl
      WHERE w.id = :wid
        AND sa.id = :said
        AND sann.id = wl.child.id

and the output from my run was:

  Well:10 Acquisition:11
  Annotations count:1 (id=60)

Let me know if that works for you and I can add the script to the
examples/ directory.

Hope that helps,
~Josh

-------------- next part --------------
A non-text attachment was scrubbed...
Name: screen.py
Type: application/octet-stream
Size: 1144 bytes
Desc: not available
Url : http://lists.openmicroscopy.org.uk/pipermail/ome-devel/attachments/20090611/a1a4e1b6/attachment.obj 
-------------- next part --------------


Bernhard Holl?nder writes:
 > Hi Josh!
 > As discussed during the OME meeting we would like to store analysis
 > results per plate well. This is straightforward using Annotions and
 > WellAnnotationLinks. Since there could be multiple measurements of a
 > single well, I will link the Annotation to the corresponding
 > ScreenAcquistion. Storing  works quite well, however, I could not
 > figure out how to join the two link tables in a single query to get
 > the results back :-(
 > 
 > In regular SQL I would do something like this:
 > 
 > SELECT a.id FROM annotation a
 > INNER JOIN screenacquisitionannotationlink saal ON saal.child = a.id
 > INNER JOIN screenacquisition sa ON sa.id = saal.parent
 > INNER JOIN wellannotationlink wal ON wal.child = a.id
 > INNER JOIN well w ON w.id = wal.parent
 > WHERE w.id = <well_id> AND sa.id = <acquisition_id>
 > 
 > ... in HSQL, at least in the  form the API allows it, the join
 > statement does not allow to specify tables and ON clauses. So I'm a
 > little bit stuck how to translate this query to objects and paths.
 > 
 > Can you help me to build a single query to retrieve the well result
 > given the well id and the acquistion id?
 > 
 > Thanks a lot! Bernhard