Problem with query filters

[francer92]

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]

Hi!

I thinks the problem is here

francer92:

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

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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.