Problem with query filters

francer92 · August 19, 2021, 8:07am

Hello!
I'm having trouble getting this query to work:

The problem arises when I run 'FIRST' OR 'SECOND': running 'FIRST' the query, despite the "not" returns also prod.contractType in ['95|FUNDS']; running 'SECOND' the return is right.

I'm not so good with neo4j (first experience) and i can't solve the problem: i tried almost 20 different combinations but it doesn't work.

It looks like they are mirrored: when i try to modify 'FIRST', sometimes it changes the return of 'SECOND'.

'ALL' works fine.

How can i solve this problem? Should i try a CASE/WHEN?

Can you help me to get the right return?

Thanks

[spoiler]
MATCH (pb:PRO{code: $pb, letter:$L})-[customerof:CUSTOMER_OF]->(c:CUSTOMER)-[:HAS_CONTRACT]->(con:CONTRACT)-[:HAS_POSITION]->(pos:POSITION)-[:HAS_PRODUCTS]->(prod:PRODUCT) WHERE
    (
        'ALL' in $filter
        or
        (
            'FIRST' in $filter
            and (prod.famprod in ['AC','DT'] and not prod.contractType in ['95|FUNDS']))
        or
        (
            'SECOND' in $filter
            and not prod.famprod in ['AC'] OR(prod.famprod IN ['AC','DT'] and prod.contractType  in ['95|FUNDS'])
        )
    )
    and
    (
        $customerType='ALL'
        or
        (
            $customerType='SINGLE'
            and
            customerof.flagSingle='1'
        )
        or
        (
            $customerType='NOTSINGLE'
            and
            customerof.flagSingle='0'
        )
    )

Bennu · August 19, 2021, 10:12am

Hi!

I thinks the problem is here

Maybe you want this

 'SECOND' in $filter
            and (not prod.famprod in ['AC'] OR(prod.famprod IN ['AC','DT'] and prod.contractType  in ['95|FUNDS']))

H

francer92 · August 19, 2021, 10:27am

Thanks for the answer, i will try soon your solution.

Do you think that your advice will make work also the 'FIRST' $filter?

Actually, 'SECOND' works but i got problems with 'FIRST'.

Thanks.

Bennu · August 19, 2021, 11:18am

Hi @francer92 ,

The problem is that while you call for FIRST, second is not properly isolated on the OR condition so it retrieves part of the results you see while you call first.

H

francer92 · August 19, 2021, 3:49pm

Hi @Bennu ,

your advice is very good! The query called 'FIRST' now works and i got the right result.

But now i got a problem with 'SECOND':

 'SECOND' in $filter
            and (not prod.famprod in ['AC'] OR(prod.famprod IN ['AC','DT'] and prod.contractType  in ['95|FUNDS']))

Before the change, it works good, now I have another problem with the condition.
It shows every "prod.famprod = 'DT'" and not only the "prod.famprod = 'DT' and prod.contractType in ['95|FUNDS']": looks like not filtering as i ask by the AND (and prod.contractType in ['95|FUNDS']).

I need prod.famprod = 'DT' and prod.contratType in ['95|FUNDS'] and not the whole prod.famprod = 'DT'.

How can i solve?

Thank you for your kindness.

Bennu · August 19, 2021, 4:23pm

Hi!

What are the intentions of

not prod.famprod in ['AC']

This will somehow show everything but prod.famprod = 'AC'

Can you phrase your second rule? So maybe we can find the right AND's OR's together?

Regards,

Harold

francer92 · August 19, 2021, 5:33pm

Hi @Bennu,
i'll try to explain what i need to do.

For 'SECOND' i don't want to show the prod.famprod ['AC' ] and the prod.famprod ['DT'], but i need to show the prod.famprod ['DT'] just when exist a row with prod.contractType in ['95|FUNDS'].

For 'FIRST' i need to show just the prod.famprod ['AC' ] and the prod.famprod ['DT'], but i need to exclude the the prod.famprod ['DT'] just when exist a row with prod.contractType in ['95|FUNDS'].

For example, the original code was simple (after, they asked me another filter about the prod.contractType) , i show you an example:

ALL' in $filter
        or
        (
            'FIRST' in $filter
            and prod.famprod in ['AC','DT'] 
        )
        or
        (
            'SECOND' in $filter
            and not prod.famprod in ['AC', 'DT'] 
    )

I hope that my post can be helpful.

Thanks a lot.

Bennu · August 19, 2021, 6:39pm

Hi!

You should always focus on what you have to show. Having not rules are for 'double condition' on the node and not just for a first exclusion, those are going to be out 'automatically'.

'ALL' in $filter
        or
        (
            'FIRST' in $filter
             AND ( 
                  prod.famprod = 'AC'
                  OR
                  ( prod.famprod = 'DT' and not prod.contractType in ['95|FUNDS'] )
            ) 
        )
        or
        (
            'SECOND' in $filter
            AND (
                   prod.famprod =  'DT'
                   AND
                   prod.contractType  in ['95|FUNDS']
            )
    )

Im over using operators and parenthesis, but if it's a good way to start with conditionals before you feel comfortable enough to just avoid them.

H

francer92 · August 19, 2021, 7:00pm

Thank you @Bennu, tomorrow i will try.

Do you think that with your version of 'SECOND' i can show also what is <> famprod ['DT', 'AC ']? There are a lot of other 'famprod' that i must show, just as the original version of the code.

Thank you.

Bennu · August 19, 2021, 7:04pm

Sure,

But it's not exactly what you phrase:

For 'SECOND' i don't want to show the prod.famprod ['AC' ] and the prod.famprod ['DT'], but i need to show the prod.famprod ['DT'] just when exist a row with prod.contractType in ['95|FUNDS'].

Were you planing to say:

'I have to show every node without prod.famprod in ['AC', 'DT'], unless it's prod.famprod = 'DT' with prod.contractType in ['95|FUNDS']'??

H

francer92 · August 20, 2021, 10:13am

Hello @Bennu, I tried your advice:

'ALL' in $filter
        or
        (
            'FIRST' in $filter
             AND ( 
                  prod.famprod = 'AC'
                  OR
                  ( prod.famprod = 'DT' and not prod.contractType in ['95|FUNDS'] )
            ) 
        )
        or
        (
            'SECOND' in $filter
            AND (
                   prod.famprod =  'DT'
                   AND
                   prod.contractType  in ['95|FUNDS']
            )
    )

Now, running 'SECOND' it only extracts the "prod.famprod = 'DT' AND
prod.contractType in ['95|FUNDS']", not showing what is different from the "prod.famprod = 'AC'".

What can i do to keep this situation, adding the extraction of what is different from "prod.famprod = 'AC'" (like previous version of code)?

Thanks a lot.

Bennu · August 20, 2021, 10:48am

Hi

'ALL' in $filter
        or
        (
            'FIRST' in $filter
             AND ( 
                  prod.famprod = 'AC'
                  OR
                  ( prod.famprod = 'DT' and not prod.contractType in ['95|FUNDS'] )
            ) 
        )
        or
        (
            'SECOND' in $filter
            AND (
                   not prod.famprod in ['AC', 'DT']
                   OR (
                       prod.famprod =  'DT'
                       AND
                       prod.contractType  in ['95|FUNDS']
                  )
            )
    )

francer92 · August 20, 2021, 1:36pm

Hello @Bennu ,
I made another attempt with your advice and the result is the same as before. This fix they asked me is one of the craziest things I've ever seen...

'SECOND' shows, as before, only prod.famprod['DT'] with the prod.contractType ['95|FUNDS'].

There is another way I can try?

Thank you.

Topic		Replies	Views
Query not working Neo4j Graph Platform	11	1057	October 3, 2019
Different MATCH due to earlier results Cypher cypher	7	1008	November 8, 2018
Query returning results with nodes that should have been filtered out in WHERE NOT IN {collection} Cypher cypher	2	269	August 25, 2020
Filtering Cypher Results Neo4j Graph Platform cypher	11	680	December 11, 2019
Optional Matches and Regular Expressions Filtering Results Cypher cypher	2	2045	October 12, 2018

Problem with query filters

Related Topics