Document query operators

[kript]

When trying to use the query syntax, its not documented (and not obvious to me from looking through the code and test cases), how to request a partial match. i.e. in iquest I would do;

iquest "%s/%s" "select COLL_NAME, DATA_NAME where DATA_SIZE = '0' AND COLL_NAME like '/seq-dev/illumina/library_merge/%'"

But I am having trouble replicating this in the python client.

To take a simpler example;

>>> query = session.query(Collection.name, DataObject.name).filter(Collection.name >= '/seq-dev/illumina/library_merge/%')
>>> for result in query:
...     print('{}/{}'.format(result[Collection.name], result[DataObject.name]))
... 
/seq-dev/illumina/library_merge/jc18_test/deletele_mac106229i.cram
/seq-dev/npg/test_file
/seq-dev/npg/19900/19900_8#12.cram

How does one both chain filters together (is it as simple as query = session.query(Collection.name, DataObject.name).filter(X).filter(x) ) and how does one request a partial match as with the % operator in iquest?

Apologies if I'm missing something obvious.
This will help with the remediation of Issue #3930

[alanking]

The questions seem pretty similar to the example under Recherché queries, so I would suggest having a look at that.

As for your example case, you could do something like (warning: untested):

>>> in_library_merge = Like(Collection.name, '/seq-dev/illumina/library_merge/%') # child of Criterion class
>>> query = session.query(Collection.name, DataObject.name).filter(DataObject.size = '0').filter(in_library_merge)

To your point made in the title, there are many things available that do not seem to be well-documented (Like, Between, etc.). Perhaps some fleshing-out would be in order?

Hope that helps!

[d-w-moore]

To answer the filter chaining part of the question, you can chain filter()'s together , or combine their arguments into one filter(), so for example:

from irods.models import DataObject
from irods.column import Criterion, Like
# ...
q= session.query (DataObject.size, DataObject.name)
f = q.filter ( Like(DataObject.name,'%stuff%') , Criterion ( '>=', DataObject.size, 5000) )
resultsList = list( f.get_results() )

will get you the same resulting set of objects as if you'd done it with :

#...
f = q.filter ( Like(DataObject.name,'%stuff%') ) 
f = f.filter ( Criterion ( '>=', DataObject.size, 5000) )
#...

[RubenGarcia]

It was my impression that chaining .filter() operations would be equivalent of "and", i.e. the intersection of the queries. But I'm having problems with this.
Is this an "or" instead? Or am I misunderstanding something?

[d-w-moore]

Some confusion may have been caused by 'chaining' having two interpretations.
(a) Method chaining as in the "Builder" pattern looks like:

session.query( *column_list ).filter(condition_1).filter(condition_2) [...] .filter(condition_N)

which during its execution will be exactly equivalent to the iquest query syntax:

  SELECT *column_list WHERE *condition_1 AND *condition_2 [ ... ] AND *condition_N

it may also be abbreviated:

session.query(*column_list).filter(condition_1, condition_2, ... condition_N)

(b) itertools.chain( ) and its use as an effective OR operator:

The OR operator doesn't exist in general query as such, but may be simulated by making separate queries and chaining the results via itertools.chain() in python (you may need to de-duplicate based on an index of the ID's from joined tables, say by using a Python 'dict') or by aggregating the results of several iquest statements together, but that is unfortunately, at present, a burden that falls on the developer.

[d-w-moore]

To answer the original question wrt partial matches, the answer as always is the Like criterion."Like", just as well as its siblings "Between" and (from 0.8.3 forward) "In" , are criteria that can be chained onto a query in the same manner as with the comparison operators >, <, ==, etc. Thus

from irods.column import Like, Between
for result in query(DataObject, Collection) \
      .filter(DataObject.size > 0) \
      .filter( Like(DataObject.name, "%.dat")):
   print result[Collection.name]+"/"+result[DataObject.name]
   #do other useful things

[RubenGarcia]

My issue was that I was hit by
#135
where two filters on the same column name clash (the second overwrites the first).
Queries such as
imeta qu -C publicationYear = 1900 and author = A
have
Criterion('=', CollectionMeta.name, meta1)).filter(
Criterion('=', CollectionMeta.value, value1)).filter
Criterion('=', CollectionMeta.name, meta2)).filter(
Criterion('=', CollectionMeta.value, value2)).filter
which don't work because the second name and value overwrites the first.

[d-w-moore]

That last Python query looks as if it would return empty set (since it's an AND) if meta1 != meta2 or if value1 != value 2... if you need an OR, then maybe add the result sets with itertools.chain().

[RubenGarcia]

I would like the readme or one of the examples to have a tutorial on how to implement
imeta qu -C publicationYear = 1900 and author = A
because it's not trivial to do.

[d-w-moore]

hi Ruben, we're been looking at a possible solution for the lack of multiple criteria based on the same column within a single query , but are not quite there yet.

There is an alternate approach one could take, for the moment:

qu = session.query(Collection,CollectionMeta).filter(
  CollectionMeta.value == A ,
  CollectionMeta.name == 'author')
qu2 = (res for res in qu if res[CollectionMeta.name] == "publicationYear"  \
           and res[CollectionMeta.value] == 1900 )
for r in qu2: print( r[Collection.name] )

[RubenGarcia]

How does this work? Are res[CollectionMeta.name] and res[CollectionMeta.value] multi-values?

I think I'd need to execute the query, get the collection, and then use collection.meta["publicationYear"]==1900 instead.
Which would seem to be quite slow, and defeats the idea of having an irods metadata database.

But thank you for your comments, I don't want to discourage you.

[d-w-moore]

Ruben, upon re-examination of this, I can see the nontriviality to which you were referring. A single instance of a general query would not satisfy the requirement of finding the such "AND" (intersecting) sets as "Collections where one AVU matches attr ('author',a) AND a separate AVU matches ('pubyear',y)". This may require a specific query, possibly.

Short of that, you could narrow the selection of Collections via a single general query as was done above:

# stage 1:
query_coll_metadata = session.query(Collection,CollectionMeta).filter(
  CollectionMeta.value == A ,
  CollectionMeta.name == 'author')

then filter the stream of results pythonically , ie with a comprehension-- but not in the manner of my example above which was flawed (sorry about that). The follow-on stage would have to be something of this sort:

from irods.collection import iRODSCollection
#stage 2:
for c_row_result  in query_coll_metadata:
  colln = iRODSCollection( session.collections, c_row_result )   # instantiate from query result
  for n,v in [ (item.name,item.value) for item in colln.metadata.items() ] : 
    if n == 'year' and v == pubYearTarget: 
        pass # insert your useful code here , where the combined AVU conditions are satisfied
    # ...

This would need to be forged into a proper python function to be more reusable, however. In the end, the specific query may be the better option for the general metadata search case, as it allows all the fine-grained control of true SQL (inclusive of both OR and AND operators such as this example would require).

[d-w-moore]

Ruben - See the example I just gave, above..., You are quite right, AVU's of (name,value) are not multi-values. My specious example was flawed! As mentioned, it may need a specific query and I'm not well versed in those yet . [ Not sure at the moment if "imeta qu" uses these, or just inefficiently rifles through the complete set of result rows! ... ] General queries are only useful for very simple table joins, in any case, on one metadata item at a time (per table). They're not good for this case, especially if we're talking about querying a very large data base.

[cjsmeele]

@d-w-moore I'll weigh in quickly for what it's worth, since I happened to be investigating a related subject:

imeta qu -C meta_foo = foo and meta_bar = bar results in a single non-specific genquery being executed (verified by hooking the gen_query db PEP), and a single DB query.

The python rule engine's genquery interface also allows querying multiple attributes: by ANDing everything together, it filters correctly. So if multiple criteria on the same column are functional in python-irodsclient (and order is preserved?), I assume it would work here as well.
The generated SQL makes sense, though I have never seen this genquery feature documented.

imeta qu -C meta_foo = foo and meta_bar = bar

-> select COLL_NAME where META_COLL_ATTR_NAME = 'meta_foo' and META_COLL_ATTR_VALUE = 'foo' and META_COLL_ATTR_NAME = 'meta_bar' and META_COLL_ATTR_VALUE = 'bar')

LOG:  execute <unnamed>: select distinct R_COLL_MAIN.coll_name  from  R_COLL_MAIN , R_OBJT_METAMAP r_coll_metamap , R_META_MAIN r_coll_meta_main , R_OBJT_METAMAP r_coll_metamap2, R_META_MAIN r_coll_meta_mn02  where r_coll_meta_main.meta_attr_name =$1  AND r_coll_meta_main.meta_attr_value = $2  AND r_coll_meta_mn02.meta_attr_name =$3  AND r_coll_meta_mn02.meta_attr_value = $4  AND R_COLL_MAIN.coll_id = r_coll_metamap.object_id  AND r_coll_metamap.meta_id = r_coll_meta_main.meta_id  AND r_coll_metamap2.meta_id = r_coll_meta_mn02.meta_id AND R_COLL_MAIN.coll_id = r_coll_metamap2.object_id  AND R_COLL_MAIN.coll_id in (select object_id from R_OBJT_ACCESS OA, R_USER_GROUP UG, R_USER_MAIN UM, R_TOKN_MAIN TM where UM.user_name=$5 and UM.zone_name=$6 and UM.user_type_name!='rodsgroup' and UM.user_id = UG.user_id and UG.group_user_id = OA.user_id and OA.object_id = R_COLL_MAIN.coll_id and OA.access_type_id >= TM.token_id and TM.token_namespace ='access_type' and TM.token_name = 'read object') order by R_COLL_MAIN.coll_name
DETAIL:  parameters: $1 = 'meta_foo', $2 = 'foo', $3 = 'meta_bar', $4 = 'bar', $5 = 'researcher', $6 = 'tempZone'

[d-w-moore]

Chris, that looks tempting and I shall check it out. Yesterday I made a commit (tests yet to be written) that seems to solve the multiple-criteria-on-same-column problem you mention, and preserves order, so maybe this will be fruitful after all. Thanks!