Skip to content

Instantly share code, notes, and snippets.

@atarkowska

atarkowska/mapr_hql_queries

Last active November 9, 2016 14:58
Show Gist options
  • Save atarkowska/409d5d4a35a6509d3bd69dd69ef6685a to your computer and use it in GitHub Desktop.
Save atarkowska/409d5d4a35a6509d3bd69dd69ef6685a to your computer and use it in GitHub Desktop.
Download ZIP
mapr hql queries
Raw
mapr_hql_queries
# COUNT
2016-11-03 09:53:01,116 DEBUG [ omero_mapr.tree] (proc.00091) count_mapannotations():196 HQL QUERY:
select
count(distinct mv.value) as childCount
from ImageAnnotationLink ial
join ial.child a
join a.mapValue mv
join ial.parent i
left outer join i.wellSamples ws
left outer join i.datasetLinks dil
where mv.name in (:filter) and a.ns in (:ns) and mv.value = :value AND
(
(dil is null and ws is not null)
OR
(ws is null and dil is not null)
)
PARAMS: object #0 (::omero::sys::Parameters)
{
map =
{
key = filter
value = object #1 (::omero::RList)
{
_val =
{
[0] = object #2 (::omero::RString)
{
_val = Organism
}
}
}
key = ns
value = object #3 (::omero::RList)
{
_val =
{
[0] = object #4 (::omero::RString)
{
_val = openmicroscopy.org/mapr/organism
}
}
}
key = value
value = object #5 (::omero::RString)
{
_val = Homo sapiens
}
}
theFilter = <nil>
theOptions = <nil>
}
----
# PATHS to object
2016-11-03 09:53:01,231 DEBUG [ omero_mapr.show] (proc.00091) mapr_paths_to_object():266 HQL QUERY:
SELECT distinct new map(
mv.value as map_value,
i.details.owner.id as owner,
count(i.id) as imgCount)
FROM ImageAnnotationLink ial
join ial.child a
join a.mapValue mv
join ial.parent i
left outer join i.details.owner
left outer join i.wellSamples ws
left outer join ws.well w
left outer join w.plate pl
left outer join pl.screenLinks sl
left outer join i.datasetLinks dil
left outer join dil.parent ds
left outer join ds.projectLinks pdl
WHERE
(
(dil is null
and ds is null and pdl is null
and ws is not null
and w is not null and pl is not null
and sl is not null)
OR
(ws is null
and w is null and pl is null and sl is null
and dil is not null
and ds is not null and pdl is not null )
)
AND
mv.name in (:filter) and a.ns in (:ns) and mv.value = :value group by mv.value, i.details.owner.id
PARAMS: object #0 (::omero::sys::Parameters)
{
map =
{
key = filter
value = object #1 (::omero::RList)
{
_val =
{
[0] = object #2 (::omero::RString)
{
_val = Organism
}
}
}
key = ns
value = object #3 (::omero::RList)
{
_val =
{
[0] = object #4 (::omero::RString)
{
_val = openmicroscopy.org/mapr/organism
}
}
}
key = value
value = object #5 (::omero::RString)
{
_val = Homo sapiens
}
}
theFilter = <nil>
theOptions = <nil>
}
----
# MAPS for SCREENS and PROJECTS
2016-11-03 09:53:01,758 DEBUG [ omero_mapr.tree] (proc.00091) marshal_mapannotations():282 HQL QUERY:
select
mv.value as value,
count(distinct i.id) as imgCount,
count(distinct s.id) as childCount1,
count(distinct p.id) as childCount2
from ImageAnnotationLink ial
join ial.child a
join a.mapValue mv
join ial.parent i
left outer join i.wellSamples ws
left outer join ws.well w
left outer join w.plate pl
left outer join pl.screenLinks sl
left outer join sl.parent s
left outer join i.datasetLinks dil
left outer join dil.parent ds
left outer join ds.projectLinks pdl
left outer join pdl.parent p
where mv.name in (:filter) and a.ns in (:ns) and mv.value = :value AND
(
(dil is null
and ds is null and pdl is null and p is null
and ws is not null
and w is not null and pl is not null
and sl is not null and s is not null)
OR
(ws is null
and w is null and pl is null and sl is null and s is null
and dil is not null
and ds is not null and pdl is not null and p is not null)
)
group by mv.value
order by count(distinct i.id) DESC
PARAMS: object #0 (::omero::sys::Parameters)
{
map =
{
key = filter
value = object #1 (::omero::RList)
{
_val =
{
[0] = object #2 (::omero::RString)
{
_val = Organism
}
}
}
key = ns
value = object #3 (::omero::RList)
{
_val =
{
[0] = object #4 (::omero::RString)
{
_val = openmicroscopy.org/mapr/organism
}
}
}
key = value
value = object #5 (::omero::RString)
{
_val = Homo sapiens
}
}
theFilter = object #6 (::omero::sys::Filter)
{
unique = <nil>
ownerId = <nil>
groupId = <nil>
offset = object #7 (::omero::RInt)
{
_val = 0
}
limit = object #8 (::omero::RInt)
{
_val = 200
}
startTime = <nil>
endTime = <nil>
}
theOptions = <nil>
}
----
# SCREENS
2016-11-03 09:53:02,281 DEBUG [ omero_mapr.tree] (proc.00091) marshal_screens():363 HQL QUERY:
select new map(mv.value as value,
screen.id as id,
screen.name as name,
screen.details.owner.id as ownerId,
screen as screen_details_permissions,
count(distinct pl.id) as childCount,
count(distinct i.id) as imgCount)
from ImageAnnotationLink ial join ial.child a join a.mapValue mv
join ial.parent i join i.wellSamples ws join ws.well w
join w.plate pl join pl.screenLinks sl join sl.parent screen
where mv.name in (:filter) and a.ns in (:ns) and mv.value = :value
group by screen.id, screen.name, mv.value
order by lower(screen.name), screen.id
PARAMS: object #0 (::omero::sys::Parameters)
{
map =
{
key = filter
value = object #1 (::omero::RList)
{
_val =
{
[0] = object #2 (::omero::RString)
{
_val = Organism
}
}
}
key = ns
value = object #3 (::omero::RList)
{
_val =
{
[0] = object #4 (::omero::RString)
{
_val = openmicroscopy.org/mapr/organism
}
}
}
key = value
value = object #5 (::omero::RString)
{
_val = Homo sapiens
}
}
theFilter = object #6 (::omero::sys::Filter)
{
unique = <nil>
ownerId = <nil>
groupId = <nil>
offset = object #7 (::omero::RInt)
{
_val = 0
}
limit = object #8 (::omero::RInt)
{
_val = 200
}
startTime = <nil>
endTime = <nil>
}
theOptions = <nil>
}
# PROJECTS
2016-11-03 09:53:02,427 DEBUG [ omero_mapr.tree] (proc.00091) marshal_projects():446 HQL QUERY:
select new map(mv.value as value,
project.id as id,
project.name as name,
project.details.owner.id as ownerId,
project as project_details_permissions,
count(distinct dataset.id) as childCount,
count(distinct i.id) as imgCount)
from ImageAnnotationLink ial join ial.child a join a.mapValue mv
join ial.parent i join i.datasetLinks dil
join dil.parent dataset join dataset.projectLinks pl
join pl.parent project
where mv.name in (:filter) and a.ns in (:ns) and mv.value = :value
group by project.id, project.name, mv.value
order by lower(project.name), project.id
PARAMS: object #0 (::omero::sys::Parameters)
{
map =
{
key = filter
value = object #1 (::omero::RList)
{
_val =
{
[0] = object #2 (::omero::RString)
{
_val = Organism
}
}
}
key = ns
value = object #3 (::omero::RList)
{
_val =
{
[0] = object #4 (::omero::RString)
{
_val = openmicroscopy.org/mapr/organism
}
}
}
key = value
value = object #5 (::omero::RString)
{
_val = Homo sapiens
}
}
theFilter = object #6 (::omero::sys::Filter)
{
unique = <nil>
ownerId = <nil>
groupId = <nil>
offset = object #7 (::omero::RInt)
{
_val = 0
}
limit = object #8 (::omero::RInt)
{
_val = 200
}
startTime = <nil>
endTime = <nil>
}
theOptions = <nil>
}
---
# PLATE
2016-11-03 10:14:31,728 DEBUG [ omero_mapr.tree] (proc.00091) marshal_plates():619 HQL QUERY:
select new map(mv.value as value,
plate.id as id,
plate.name as name,
plate.details.owner.id as ownerId,
plate as plate_details_permissions,
count(distinct i.id) as childCount)
from ImageAnnotationLink ial join ial.child a join a.mapValue mv
join ial.parent i join i.wellSamples ws join ws.well w
join w.plate plate join plate.screenLinks sl join
sl.parent screen
where mv.name in (:filter) and a.ns in (:ns) and mv.value = :value and screen.id = :sid
group by plate.id, plate.name, mv.value
order by lower(plate.name), plate.id, mv.value
PARAMS: object #0 (::omero::sys::Parameters)
{
map =
{
key = filter
value = object #1 (::omero::RList)
{
_val =
{
[0] = object #2 (::omero::RString)
{
_val = Organism
}
}
}
key = ns
value = object #3 (::omero::RList)
{
_val =
{
[0] = object #4 (::omero::RString)
{
_val = openmicroscopy.org/mapr/organism
}
}
}
key = value
value = object #5 (::omero::RString)
{
_val = Homo sapiens
}
key = sid
value = object #6 (::omero::RLong)
{
_val = 1251
}
}
theFilter = object #7 (::omero::sys::Filter)
{
unique = <nil>
ownerId = <nil>
groupId = <nil>
offset = object #8 (::omero::RInt)
{
_val = 0
}
limit = object #9 (::omero::RInt)
{
_val = 200
}
startTime = <nil>
endTime = <nil>
}
theOptions = <nil>
}
----
# DATASET
2016-11-03 10:16:39,068 DEBUG [ omero_mapr.tree] (proc.00091) marshal_datasets():533 HQL QUERY:
select new map(mv.value as value,
dataset.id as id,
dataset.name as name,
dataset.details.owner.id as ownerId,
dataset as dataset_details_permissions,
count(distinct i.id) as childCount)
from ImageAnnotationLink ial join ial.child a join a.mapValue mv
join ial.parent i join i.datasetLinks dil
join dil.parent dataset join dataset.projectLinks pl
join pl.parent project
where mv.name in (:filter) and a.ns in (:ns) and mv.value = :value and project.id = :pid
group by dataset.id, dataset.name, mv.value
order by lower(dataset.name), dataset.id, mv.value
PARAMS: object #0 (::omero::sys::Parameters)
{
map =
{
key = filter
value = object #1 (::omero::RList)
{
_val =
{
[0] = object #2 (::omero::RString)
{
_val = Organism
}
}
}
key = ns
value = object #3 (::omero::RList)
{
_val =
{
[0] = object #4 (::omero::RString)
{
_val = openmicroscopy.org/mapr/organism
}
}
}
key = pid
value = object #5 (::omero::RLong)
{
_val = 101
}
key = value
value = object #6 (::omero::RString)
{
_val = Mus musculus
}
}
theFilter = object #7 (::omero::sys::Filter)
{
unique = <nil>
ownerId = <nil>
groupId = <nil>
offset = object #8 (::omero::RInt)
{
_val = 0
}
limit = object #9 (::omero::RInt)
{
_val = 200
}
startTime = <nil>
endTime = <nil>
}
theOptions = <nil>
}
----
# IMAGES
2016-11-03 10:15:31,843 DEBUG [ omero_mapr.tree] (proc.00091) marshal_images():753 HQL QUERY:
select new map(image.id as id,
image.name as name,
image.details.owner.id as ownerId,
image as image_details_permissions,
image.fileset.id as filesetId )
from Image image
where image.id in (
select image.id from
ImageAnnotationLink ial
join ial.child a
join a.mapValue mv
join ial.parent image
join image.wellSamples ws join ws.well well
join well.plate plate
where mv.name in (:filter) and a.ns in (:ns) and mv.value = :value and plate.id = :pid
order by lower(image.name))
PARAMS: object #0 (::omero::sys::Parameters)
{
map =
{
key = filter
value = object #1 (::omero::RList)
{
_val =
{
[0] = object #2 (::omero::RString)
{
_val = Organism
}
}
}
key = ns
value = object #3 (::omero::RList)
{
_val =
{
[0] = object #4 (::omero::RString)
{
_val = openmicroscopy.org/mapr/organism
}
}
}
key = pid
value = object #5 (::omero::RLong)
{
_val = 4403
}
key = value
value = object #6 (::omero::RString)
{
_val = Homo sapiens
}
}
theFilter = object #7 (::omero::sys::Filter)
{
unique = <nil>
ownerId = <nil>
groupId = <nil>
offset = object #8 (::omero::RInt)
{
_val = 0
}
limit = object #9 (::omero::RInt)
{
_val = 200
}
startTime = <nil>
endTime = <nil>
}
theOptions = <nil>
}
---
# LOAD MAP
2016-11-03 09:53:02,694 DEBUG [ omero_mapr.tree] (proc.00091) load_mapannotation():862 HQL QUERY:
select distinct a
from Annotation a
join fetch a.details.creationEvent
join fetch a.details.owner
join a.mapValue mv where mv.name in (:filter) and a.ns in (:ns) and mv.value = :value
order by a.ns asc
PARAMS: object #0 (::omero::sys::Parameters)
{
map =
{
key = filter
value = object #1 (::omero::RList)
{
_val =
{
[0] = object #2 (::omero::RString)
{
_val = Organism
}
}
}
key = ns
value = object #3 (::omero::RList)
{
_val =
{
[0] = object #4 (::omero::RString)
{
_val = openmicroscopy.org/mapr/organism
}
}
}
key = value
value = object #5 (::omero::RString)
{
_val = Homo sapiens
}
}
theFilter = object #6 (::omero::sys::Filter)
{
unique = <nil>
ownerId = <nil>
groupId = <nil>
offset = object #7 (::omero::RInt)
{
_val = 0
}
limit = object #8 (::omero::RInt)
{
_val = 200
}
startTime = <nil>
endTime = <nil>
}
theOptions = <nil>
}
2016-11-09 14:57:47,789 DEBUG [ omero_mapr.tree] (proc.00091) marshal_autocomplete():960 HQL QUERY:
select new map(mv.value as value)
from ImageAnnotationLink ial join ial.child a join a.mapValue mv
where mv.name in (:filter) and a.ns in (:ns) and lower(mv.value) like :query
group by mv.value
order by lower(mv.value)
PARAMS: object #0 (::omero::sys::Parameters)
{
map =
{
key = filter
value = object #1 (::omero::RList)
{
_val =
{
[0] = object #2 (::omero::RString)
{
_val = Gene Symbol
}
[1] = object #3 (::omero::RString)
{
_val = Gene Identifier
}
}
}
key = query
value = object #4 (::omero::RString)
{
_val = %cdc%
}
key = ns
value = object #5 (::omero::RList)
{
_val =
{
[0] = object #6 (::omero::RString)
{
_val = openmicroscopy.org/mapr/gene
}
}
}
}
theFilter = object #7 (::omero::sys::Filter)
{
unique = <nil>
ownerId = <nil>
groupId = <nil>
offset = object #8 (::omero::RInt)
{
_val = 0
}
limit = object #9 (::omero::RInt)
{
_val = 200
}
startTime = <nil>
endTime = <nil>
}
theOptions = <nil>
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment