Query for doc_id possible?

[fortysix2ahead]

This may be a bit of a strange question, but is it possible to use the doc_id for queries? So far, I was not able to find out if and how that would work.

To outline what I was thinking of: imagine I have 3 documents, with doc_ids 1, 2 and 3. I display those to a user (by reusing the doc_id as a regular id):
1: name = Berlin
2: name = London
3: name = Paris

Now the user can decide to remove the latter two with something like: remove_documents( range_from=2, range_to=3). Of course I could introduce a regular document field 'id' so Query().id would work, but the field would have the same value as doc_id. So, why not reuse doc_id?

[MrPigss]

It is possible to do something like this. See the docs.

You can remove multiple documents using their doc_id.

db.remove(doc_ids=[1,2,3,4])

To get the functionality of remove_documents( range_from=1, range_to=4)
you could simply use the python range function like this.

db.remove(doc_ids=range(1, 5)) # range(start, stop, step) where start is included and end is not.

[fortysix2ahead]

Yes, I know. I just wonder if it's possible to manipulate documents with, let's say, doc_id in the range from 100 to 200, field name starts with 'X' and field creation date is older than 1999. In my scenario queries are created dynamically and the id/doc_id is just a field like others.

[MrPigss]

I don't really know if it is possible to use doc_id in queries but you can do this quite easily yourself.

you can search all items that correspond to your queries and then filter only those between 100 and 200.
In the example below a list comprehension is used, i've broken it up into pieces to make it more readable but you can also just make it a oneliner or a for loop (although the list comprehension should be faster).

q = Query()
item_filter = (q.name.matches(r'^X.*')) & (q.creation_date < 1999) # the query you want to use in search
id_filter = lambda item: 100 < item.doc_id < 200 # the check for the correct id's
result = [item for item in db.search(item_filter) if id_filter(item)]

it might also be possible to do this with custom tests

[fortysix2ahead]

Yes, that's what I am currently doing. What I don't like: as queries can be dynamically created by the user, I have to do special treatment for anything that relates to ids. Custom tests (in conjunction with map()) do not help as they work on a dict that has been stripped from the doc_id.

[fortysix2ahead]

Another option I stumbled upon: get() and search() are calling _read_table(). I can use a custom table, overriding _read_table() and 'project' the doc_id into the dict. Afterwards querying is possible. This way I can also project other 'virtual values' into it and query for them. But I don't know (yet) if that has any side effects.

class CustomTable( Table ):
	def __init__( self, storage: Storage, name: str, cache_size: int = Table.default_query_cache_capacity ):
		super().__init__( storage, name, cache_size )

	def _read_table( self ) -> Dict[str, Mapping]:
		_table = super()._read_table()
		if self.name == 'custom_name':
			for i in _table.items():
				i[1]['doc_id'] = int( i[0] )
		return _table

[msiemens]

@fortysix2ahead You're correct that this is not supported by TinyDB at the moment. I agree that in some use cases querying by document ID can be useful. The issue is that adding support for this would 1) require a rather large refactoring of TinyDB's internals and 2) potentially break use cases where users use a legimate field called doc_id.

Regarding 1), this is because TinyDB only converts the dicts it stores in the storage engine to Document objects which contain know their doc_id when returning them to the user. This would have to be changed so the Document objects are passed to the query. Another way would be to add a new argument to query funtions that contains the document ID. But this would be a major breaking change and would have the potential to break quite a few existing use cases.

I think the best option would be to have a custom table class that adds the current document ID during inserts. That way, all objects you insert will have a field that contains the document ID that you can query using the regular query operations.

[fortysix2ahead]

@msiemens I do it differently now. I use a middleware which creates dedicated documents (derived from the TinyDB Document class). This is also used as document class for a table. The subclass is overriding getitem:

class Person( Document ):
  def __getitem__(self, key):
    if key == 'id':
      return self.doc_id
    else:
      return super().__getitem__(key)

This way a Query()['id'] is working as expected. As a side effect I can also establish 'virtual' fields which are calculated on demand. Think of two persistent fields 'monthly_salary' = 1000 and 'number_of_months' = 5 and add a 'virtual' field 'yearly_salary' which is calculated as monthly_salary * number_months. It' possible to query for a yearly_salary > 5000 without having such a field:

class Person( Document ):
  accessors = {
    'yearly_salary': lambda v: v.get( 'monthly_salary' ) * v.get( 'number_months' )
  }
  def __getitem__(self, key):
    if key == 'id':
      return self.doc_id
    elif key in accessors.keys():
      return accessors[key]( self )
    else:
      return super().__getitem__(key)

Works great so far. I could even skip the middleware part if TDB would use the document class when evaluating conditions in get() and search() in table.py. Unfortunately it uses a plain dict and does the document_class conversion after the condition has been evaluated. I remember this was different in earlier versions. Maybe it would be an idea to make this behaviour configurable?

[msiemens]

I'm glad you found a solution!

Unfortunately it uses a plain dict and does the document_class conversion after the condition has been evaluated. I remember this was different in earlier versions.

You're right that TinyDB recently changed how it works in this regard (see #460). This was to improve performance by only converting documents to the Document class that match the query condition. It's all trade-offs one way or another 🤷 I don't think making this behavior configurable would be a good design choice as it would increase the possibility of suble errors significantly. By doing this in a middleware you're doing what TinyDB was doing before anyway 🙂