Turn rows of join in columns?

[BairDev]

Background

I want to create a set (rows) of single users with columns from values from child nodes. But I am not able to understand how to aggregate this (with subqueries?).

Data

                 - (:UsedMeasure)
               /               
(:User)    --  (:UsedMeasure)
           | \
           |   - (:UsedMeasure)
           |
           -   (:UsedMeasure)

So it is user(1)-measure(n).

We have 19 types of measure and we want to know, which measure has been chosen by a user and which not.

No I want to create a set of rows with exactly 1 row per user, something like (we can ignore the (:Address) part here):

MATCH (u:User {isBetUser: true})--(a:Address) WHERE (u)-->(:UsedMeasure) WITH u, a CALL { WITH u MATCH (u)-->(m:UsedMeasure) WITH COLLECT(m.name) AS chosen RETURN {LabelAChosen: ('LabelA' IN chosen)} AS map} RETURN ID(u), a.addressCityCode, map.LabelAChosen;
+----------------------------------------------+
| ID(u) | a.addressCityCode | map.LabelAChosen |
+----------------------------------------------+
| 107   | "12345"           | FALSE            |
| 161   | "32523"           | TRUE             |
| 402   | "12345"           | FALSE            |
| 438   | "34567"           | FALSE            |
| 443   | "48593"           | FALSE            |
| 493   | "12455"           | TRUE             |
| 509   | "24211"           | FALSE            |
| 516   | "45667"           | FALSE            |
| 528   | "34737"           | TRUE             |
| 535   | "12345"           | FALSE            |
+----------------------------------------------+

This would work for all 19 types of these children.

But one more thing makes it complicated:

measure (:UsedMeasure {name: 'LabelA', confirmed ?: 'percent50', confirmExplain ?: 'something'}) where ?: means optional property, can be NULL.

Now I am trying to do something like this (playing with just 1 out of 19 types of measure😞

MATCH (u:User {isBetUser: true})--(a:Address) WHERE (u)-->(:UsedMeasure)
    WITH u, a CALL {
        WITH u MATCH (u)-->(m:UsedMeasure) // multiple rows for the same user, but I want just 1
        WITH COLLECT({n: m.name, c: m.confirmed, ex: m.confirmExplain}) AS maps
        RETURN {
            LabelAChosen: ('LabelA' IN maps) // here don't know, how to filter in collection of maps / nodes
            LabelAConfirmed: // ??
        } AS map}
    RETURN ID(u), a.addressCityCode, map.LabelAChosen;

Is there any way (some apoc magic) to filter a collected list of maps or the nodes (m:UsedMeasure) for getting the child measure of a certain type (name) and putting something like COALESCE(m.confirmed, '') as {name}Confirmed to the user row?

[giuseppe_villan]

@BairDev

If I understood correctly,
you can get rid of CALL subquery.
You could execute something like this :

MATCH (u:User {isBetUser: true})--(a:Address) 
MATCH (u)-->(um:UsedMeasure) // match children
WITH u, a, COLLECT(um.name) as names, COLLECT(um.confirmed) as confirmed, COLLECT(um.confirmExplain) as explains // collect required property
RETURN id(u), a.addressCityCode, "LabelA" in names as labelAChosen, "percent50" in confirmed as percentChosen, "something" in explains as somethingChosen // check if elements are into the lists

So, I receive this result with the below dataset:

id(u)	| a.addressCityCode	| labelAChosen | percentChosen | somethingChosen
1162	| "12345"           |	true	   | true	       | true
1170	| "5555"            |	true	   | false         | true
1178	| "9999"            |	false      | true          | false

Dataset with 3 (:User) nodes:

CREATE (u:User {isBetUser: true})-[:REL]->(a:Address {addressCityCode: "12345" }) 
WITH u
CREATE (u)-[:REL]->(:UsedMeasure {name: 'LabelA'}),
	(u)-[:REL]->(:UsedMeasure {name: 'LabelA', confirmed: 'percent50'}),
	(u)-[:REL]->(:UsedMeasure {name: 'LabelA'}),
	(u)-[:REL]->(:UsedMeasure {name: 'LabelA', confirmExplain: 'something'}),
	(u)-[:REL]->(:UsedMeasure {name: 'LabelA'}),
 	(u)-[:REL]->(:UsedMeasure {name: 'LabelA', confirmExplain: 'something'});

CREATE (u:User {isBetUser: true})-[:REL]->(a:Address  {addressCityCode: "5555" }) 
WITH u
CREATE (u)-[:REL]->(:UsedMeasure {name: 'LabelA'}),
	(u)-[:REL]->(:UsedMeasure {name: 'LabelA', confirmed: 'anotherPercent'}),
	(u)-[:REL]->(:UsedMeasure {name: 'LabelA'}),
	(u)-[:REL]->(:UsedMeasure {name: 'LabelA', confirmExplain: 'something'}),
	(u)-[:REL]->(:UsedMeasure {name: 'LabelA'}),
	(u)-[:REL]->(:UsedMeasure {name: 'LabelA', confirmExplain: 'something'});

CREATE (u:User {isBetUser: true})-[:REL]->(a:Address  {addressCityCode: "9999" }) 
WITH u 
CREATE (u)-[:REL]->(:UsedMeasure {name: 'LabelNotA', confirmed: 'percent50'});

[BairDev]

Thanks for your commitment!

Your solution would work perfectly, if we would know the values of our properties confirmed and confirmExplain.

The values for confirmed are from a set of strings, the values of confirmExplain are completely free user input. Both are optional, they might be null (which is covered by your solution I think).

And we don't want to say true/false here, but get the values in the results (which will be a CSV file).

Sorry for the late clarification.

No I have found a solution for avoiding a query like MATCH (u)--(m:UsedMeasure) for all u:User, but it is horrible performancewise and it is not maintainable at all. The query now runs for 6 hours, we have ~5000 users and ~31000 measures.

I am creating the 19*3 variables step by step and carry them through the query. Again, the result is OK (tested with a small dev DB), but the solution is horrible.

CALL apoc.export.csv.query("MATCH (u:User {isBetUser: true}) WHERE (u)-->(:UsedMeasure) AND (u)-->(:UserInfoBet) " +
        "CALL { WITH u OPTIONAL MATCH (u)--(m:UsedMeasure {name: 'LabelA'}) RETURN CASE WHEN m IS NULL THEN false ELSE true END AS labelAc } " +
    "WITH u, labelAc " +
        "CALL { WITH u OPTIONAL MATCH (u)--(m:UsedMeasure {name: 'LabelA'}) RETURN CASE WHEN m IS NULL THEN '' ELSE COALESCE(m.confirmed, '') END AS labelACon } " +
    "WITH u, labelAc, labelACon " +
        "CALL { WITH u OPTIONAL MATCH (u)--(m:UsedMeasure {name: 'LabelA'}) RETURN CASE WHEN m IS NULL THEN '' ELSE COALESCE(m.confirmExplain, '') END AS labelAex } " +
    "WITH u, labelAc, labelACon, labelAex " +
        "CALL { WITH u OPTIONAL MATCH (u)--(m:UsedMeasure {name: 'Bicycle'}) RETURN CASE WHEN m IS NULL THEN false ELSE true END AS bicyclec } " +
    "WITH u, labelAc, labelACon, labelAex, bicyclec " +
        "CALL { WITH u OPTIONAL MATCH (u)--(m:UsedMeasure {name: 'Bicycle'}) RETURN CASE WHEN m IS NULL THEN '' ELSE COALESCE(m.confirmed, '') END AS bicycleCon } " +
    "WITH u, labelAc, labelACon, labelAex, bicyclec, bicycleCon " +
        "CALL { WITH u OPTIONAL MATCH (u)--(m:UsedMeasure {name: 'Bicycle'}) RETURN CASE WHEN m IS NULL THEN '' ELSE COALESCE(m.confirmExplain, '') END AS bicycleex } " +
    "WITH u, labelAc, labelACon, labelAex, bicyclec, bicycleCon, bicycleex " +
        "CALL { WITH u OPTIONAL MATCH (u)--(m:UsedMeasure {name: 'Biogas'}) RETURN CASE WHEN m IS NULL THEN false ELSE true END AS biogasc } " +
    "WITH u, labelAc, labelACon, labelAex, bicyclec, bicycleCon, bicycleex, biogasc " +
        "CALL { WITH u OPTIONAL MATCH (u)--(m:UsedMeasure {name: 'Biogas'}) RETURN CASE WHEN m IS NULL THEN '' ELSE COALESCE(m.confirmed, '') END AS biogasCon } " +
    "WITH u, labelAc, labelACon, labelAex, bicyclec, bicycleCon, bicycleex, biogasc, biogasCon " +
        "CALL { WITH u OPTIONAL MATCH (u)--(m:UsedMeasure {name: 'Biogas'}) RETURN CASE WHEN m IS NULL THEN '' ELSE COALESCE(m.confirmExplain, '') END AS biogasex } " +
    // [...] for all types of measure

I can match (:Address) and (:UserInfoBet) at the end and return everything in 5+(19*3) columns.