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

[Bernhard Holländer]

Hi Josh!

Excellent, works like a charm. Thanks for your help and sharing you
superior expertise.

Best! Bernhard

On Thu, Jun 11, 2009 at 2:44 PM, <josh.moore at gmx.de> wrote:
>
> 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
>
>
>
>
> 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
>
> _______________________________________________
> ome-devel mailing list
> ome-devel at lists.openmicroscopy.org.uk
> http://lists.openmicroscopy.org.uk/mailman/listinfo/ome-devel
>
>