DATABOX and complex queries

Functional How-to
1

hi!

hope everyone upgraded with the latest build and enjoy great reporting tool - DATABOX, which totally replaced prior solution.

I’ve a question regarding building some complex queries with “join”:

my current task is to build report which selects analyses which parent object (AR) has date of verification in particular range.

Or lets take other example: select analyses items w/ results for chosen patient gender (or age?)

As far as we build such report in Databox around analysis entity, the only way we found is to add customs index to the entity or even worse extend it with some supplementary field.

But my approach doesn’t look elegant (either correct in any way) because it involve modifications of business entities for just building report.

Is there right way to build such reports?

regards
–leo

2

Hi Leo,

Thanks for your great feedback regarding senaite.databox!
It is good to hear that it serves as a valuable tool for you.

However, it has it flaws and limitations as you already found out, e.g. that it can currently only query one type and complex joins are not really possible.

It might be (technically) possible to allow a databox inside another databox, so that it inherits the results from the parent and operates only on this result set for another (related) content type.
E.g. to “feed” the result UIDs of the parent query into an appropriate index of an analysis query. Unfortunately, this was not further investigated.

Therefore, the only chance you have is to query from bottom upwards and add the required indexes and filters on the analysis itself, e.g. to have an index there for Patient gender as well.
Alternatively, you need to copy the sample IDs from your “sample databox” export manually into your “analysis databox” inside the getRequestID index as a list.

The other way, and probably the more faster one, is to create a Script (Python) via the ZMI portal_skins tool inside the custom folder and export the data as CSV:

from Products.CMFCore.utils import getToolByName
from DateTime import DateTime

request = container.REQUEST
response =  request.response
catalog = getToolByName(context, "senaite_catalog_sample")

header = [
    "Client Name",
    "Client ID",
    "SampleID",
    "Created",
    "Received",
    "Published",
]
print ",".join(header)

results = catalog({
    "sort_on": "created",
    "review_state": "published",
    "sort_order": "descending",
})

def to_date(date):
    dt = DateTime(date)
    return dt.strftime("%d.%m.%Y %H:%M")

for result in results:
    sample = result.getObject()
    client = sample.getClient()
  
    created = sample.created()
    received = sample.getDateReceived()
    published = sample.getDatePublished()

    row = [
        '"{}"'.format(client.getName()),
        '"{}"'.format(client.getClientID()),
        '"{}"'.format(sample.getId()),
        to_date(created),
        to_date(received),
        to_date(published),
    ]
    print ",".join(row)

return printed

Well, not the most elegant way to create a CSV, but it get you to a result.
Just take care of unicode decoding by maybe using api.to_utf8(...) for the client name or other values.

Finally, you could consider do all the things above in an browser listing view;)

Hope that helps

Best regards,
Ramon

1 Like
3

Hi @ramonski
I successfully got your example working.
But trying your api.to_utf8-thing (which I find useful) left me helpless.
I supposed from bika.lims import api is needed for that, but I get a unsufficient privileges-error with that line in the code. Any idea?

4

I found out, that scripting via ZMI is somehow restricted concerning imports of libraries: 14. Advanced Zope Scripting — Zope 5.9 documentation