Filter by comparing 2 fields

[Florian Haase]

Hi

what would the best way be to filter an a comparison of 2 fields?

...

batch_aggregate(filter:{_and:[{origin:{_eq:8}},{sumDebitDomestic:{_not_eq:sumCreditDomestic}}]})
{

...

Here I expected to get all batches which do not balance. But this does not seem to work.

Not a very big deal here since there would not be a bunch of batches with the same batchtype. So the comparison could be done in the code on the result but still I wonder if this should work or if I have a syntax error here?

Florian

[Florian Haase]

Is this still not supported? 🙂 

We see that we would have use for that quite often...

[Marius Bancila]

No, it is not. We have looked into this but there is no plain forward solution to do this.

Let me share the possible options, briefly.

First of all, a schema that allows you to do the following is not possible:

batch_aggregate(filter:{sumDebitDomestic:{_not_eq:sumCreditDomestic}})
{
}

What we can do is force the right hand argument (sumCreditDomestic) to be an object itself:

batch_aggregate(filter:{sumDebitDomestic:{_not_eq: {sumCreditDomestic : ???}}})
{
}

But then the question is what do we put in place of ???. This value is meaningless. Should it be a bool, an empty string, a 0? It would look very strange, IMO. Unnatural.

batch_aggregate(filter:{sumDebitDomestic:{_not_eq: {sumCreditDomestic : true}}})
{
}

A possible alternative would be to define enumerations with the columns of a table, and use that on the right-hand side. Again, unnatural to me. Example:

batch_aggregate(filter:{sumDebitDomestic:{_not_eq:SUMCREDITDOMESTIC}})
{
}

What we are left with, is using strings to provide filters:

batch_aggregate(filter:"sumDebitDomestic <> sumCreditDomestic")
{
}

or (just as a dummy example):

batch_aggregate(filter:"sumDebitDomestic > sumCreditDomestic * 0.5")
{
}

But this means we need to build a parser for these strings because we cannot allow you to put any string and as using it in a where clause of an SQL SELECT. This is going to complicate things a lot. It's possible, but I don't know if the effort is worth it.

With these options laid out, which would you feel is the most "friendly" to use? Or perhaps you have other ideas in which case I'd love to see.

[Florian Haase]

It does not exist any standard solution for that?

At least I see that this is a standard issue 🙂

https://github.com/graphile-contrib/postgraphile-plugin-connection-filter/issues/113

I guess that building a parser would be a lot of work and you have to consider the security regarding code injection and building filters which will create a huge load on the sql-server.

The next best solution actually would be the enumeration way. 

But I understand that there is no quick fix on that. 

Florian 

[Marius Bancila]

Using a field for the value of another field in a filter, like sumDebitDomestic:{_not_eq:sumCreditDomestic} is not currently supported.