Index Children only with based Condition
kundankumarcasio opened this issue · 9 comments
PGSync version: latest
Postgres version: 15.2
Elasticsearch/OpenSearch version: 7.17.13
Redis version: 7.2
Python version: 3.8
Problem Description:
I want to index children with given condition
[
{
"database": "databaseName",
"index": "indexName",
"nodes": {
"table": "parentTable",
"columns": [],
"children": [
{
"table": "childTable",
"columns": [],
"relationship": {
"variant": "object",
"type": "one_to_many",
"foreign_key": {
"child": [
"parent_id"
],
"parent": [
"id"
],
"condition": {
"child_column1": "some Value" //need help here
}
}
}
}
]
}
}
]
Equivalent SQL Query:
select * from parentTable join childTable where parentTable.id=childTable.parent_id where child_column1="some Value"
Error Message (if any):
I wonder if this could also be achieved within PGSync's plugin API? That way, conditions can be more expressive. Something like:
from pgsync import plugin
class FooPlugin(plugin.Plugin):
name = 'FooPlugin'
def transform(self, doc, **kwargs):
...
def should_index(self, doc, **kwargs):
return doc['childTable']['parent_id'] == 'someValue'
thank you very much.
i will try this.
I don't think an API exists today for something like this. However, it could be an enhancement.
yes.
Any work around for this ?
You can probably write a plugin like the following:
from pgsync import plugin
class FooPlugin(plugin.Plugin):
name = 'FooPlugin'
def transform(self, doc, **kwargs):
return doc if doc['childTable']['parent_id'] == 'someValue' else None
But I am concerned that depending on the condition, this won't necessarily insert/delete documents as expected. It may work for your use case though.
I tried this and it worked, but this creates some sync inconsistencies.
This would be a great enhancement to add for things like soft-deleted or archived records.
I think Plugins are the right/only way to do this.
Can you elaborate on what you mean by sync inconsistencies?
I can assist with a plugin if you need.
@toluaina I believe if you were to have indexed a PostgreSQL record at one point, but then a plugin used were to return None
based on some condition, the correlating document would not necessarily get removed from the index.