Question. Join a query

[tlogik]

This is going to a bit long because of the code examples since it is taken from my test application. Some parts are removed for readability.

Observe the code below

foreach (var fbPagePost in fbPage.Posts)
                        {
                            var pq = client.Cypher
                                .MergeEntity(fbPage, "page")
                                .MergeEntity(fbPagePost, "post")
                                .MergeEntity(fbPagePost.Language, "language")
                                .MergeEntity(fbPagePost.Author, "author")
                                .MergeRelationship(new OwnedByRelationship("post", "page"))
                                .MergeRelationship(new HasLanguageRelationship("post", "language"))
                                .MergeRelationship(new WroteRelationship("author", "post", fbPagePost.CreatedAt));
                            if (fbPagePost.Category != null)
                                pq.MergeEntity(fbPagePost.Category, "category")
                                    .MergeRelationship(new WithCategoryRelationship("post", "category"));

                            if (fbPagePost.Author.Country != null)
                                pq.MergeEntity(fbPagePost.Author.Country, "country")
                                    .MergeRelationship(new OriginatesRelationship("author", "country"));
                            if (fbPagePost.AttachedLink != null)
                                pq.MergeEntity(fbPagePost.AttachedLink, "link")
                                    .MergeRelationship(new LinksToRelationsship("post", "link"));
                            pq.ExecuteWithoutResults();

                        }

I am creating query pq. I have a couple of null checks and if the objects have value i add them to the pq query by means of pq.MergeEntity... Problem is that the actual CQL created does not contain any entry about the null checked values, even thou they had value and thus the code was hit.

It seems that the merging of country, link and category are not actually being added to the pq query.

To make i work i had to do the following that then produces quite a lot more roundtrips to the db

foreach (var fbPagePost in fbPage.Posts)
                    {
                        var pq = client.Cypher
                            .MergeEntity(fbPage, "page")
                            .MergeEntity(fbPagePost, "post")
                            .MergeEntity(fbPagePost.Language, "language")
                            .MergeEntity(fbPagePost.Author, "author")
                            .MergeRelationship(new OwnedByRelationship("post", "page"))
                            .MergeRelationship(new HasLanguageRelationship("post", "language"))
                            .MergeRelationship(new WroteRelationship("author", "post", fbPagePost.CreatedAt));
                        pq.ExecuteWithoutResults();
                        if (fbPagePost.Category != null)
                        {
                            var pqc = client.Cypher
                                .MergeEntity(fbPagePost, "post")
                                .MergeEntity(fbPagePost.Category, "category")
                                .MergeRelationship(new WithCategoryRelationship("post", "category"));
                            pqc.ExecuteWithoutResults();
                        }

                        if (fbPagePost.Author.Country != null)
                        {
                            var pqcountry = client.Cypher
                                .MergeEntity(fbPagePost.Author, "author")
                                .MergeEntity(fbPagePost.Author.Country, "country")
                                .MergeRelationship(new OriginatesRelationship("author", "country"));
                            pqcountry.ExecuteWithoutResults();
                        }

                        if (fbPagePost.AttachedLink != null)
                        {
                            var pql = client.Cypher
                                .MergeEntity(fbPagePost, "post")
                                .MergeEntity(fbPagePost.AttachedLink, "link")
                                .MergeRelationship(new LinksToRelationsship("post", "link"));
                            pql.ExecuteWithoutResults();
                        }

                        foreach (var subject in fbPagePost.Subjects)
                        {
                            var cq = client.Cypher
                                .MergeEntity(fbPagePost, "fbpost")
                                .MergeEntity(subject, "subject")
                                .MergeRelationship(new RelatesToRelationship("fbpost", "subject"));
                            cq.ExecuteWithoutResults();
                        }

 }

now i create separate queries for each null check. This is not very efficient but it does however work.

And now the question. Is it possible to append to a query, essentially like in the first approach where i just add more MergeEntity statements to the same query pq

Thanks Thomas

extra Example CQL produced from the first code where country and link checks were hit becuase they had value.

MERGE (page:FbPage {entityId:{
  "entityId": "450861208391974"
}.entityId})
ON MATCH
SET page.averageSharesPerPost = 0.0
ON MATCH
SET page.averageLikesPerPost = 0.0
ON MATCH
SET page.averageCommentsPerPost = 0.0
ON MATCH
SET page.pictureUrl = "https://graph.facebook.com/450861208391974/picture?type=large"
ON MATCH
SET page.likes = 0
ON MATCH
SET page.username = "haderslevgolfrestaurant"
ON MATCH
SET page.name = "Haderslev Golfrestaurant"
ON MATCH
SET page.updatedAt = "2017-02-05T18:04:18.0905356+01:00"
ON CREATE
SET page = {
  "averageSharesPerPost": 0.0,
  "averageLikesPerPost": 0.0,
  "averageCommentsPerPost": 0.0,
  "pictureUrl": "https://graph.facebook.com/450861208391974/picture?type=large",
  "likes": 0,
  "username": "haderslevgolfrestaurant",
  "name": "Haderslev Golfrestaurant",
  "updatedAt": "2017-02-05T18:04:18.0905356+01:00",
  "userType": "Page",
  "createdAt": "2017-02-05T18:04:18.0905356+01:00",
  "entityId": "450861208391974"
}
MERGE (post:FbPost {entityId:{
  "entityId": "1023068134504609"
}.entityId})
ON MATCH
SET post.hashtags = []
ON MATCH
SET post.message = "Der er igen idag frisk bagt rugbrød til kun kr. 28,00😊⛳"
ON MATCH
SET post.indexedAt = "2017-02-05T18:01:54+01:00"
ON MATCH
SET post.numberOfLikes = 23
ON MATCH
SET post.numberOfShares = 2
ON MATCH
SET post.numberOfComments = 5
ON MATCH
SET post.likesPeakViralTrendScore = 0.0
ON MATCH
SET post.likesAverageViralTrendScore = 0.0
ON MATCH
SET post.sharesPeakViralTrendScore = 0.0
ON MATCH
SET post.sharesAverageViralTrendScore = 0.0
ON MATCH
SET post.commentsPeakViralTrendScore = 0.0
ON MATCH
SET post.commentsAverageViralTrendScore = 0.0
ON MATCH
SET post.url = "http://www.facebook.com/haderslevgolfrestaurant/posts/1023068134504609"
ON MATCH
SET post.postType = "photo"
ON MATCH
SET post.permaLink = "http://www.facebook.com/permalink.php?story_fbid=1023068134504609&id=450861208391974"
ON MATCH
SET post.updatedAt = "2017-02-05T18:01:54+01:00"
ON CREATE
SET post = {
  "hashtags": [],
  "message": "Der er igen idag frisk bagt rugbrød til kun kr. 28,00😊⛳",
  "indexedAt": "2017-02-05T18:01:54+01:00",
  "numberOfLikes": 23,
  "numberOfShares": 2,
  "numberOfComments": 5,
  "likesPeakViralTrendScore": 0.0,
  "likesAverageViralTrendScore": 0.0,
  "sharesPeakViralTrendScore": 0.0,
  "sharesAverageViralTrendScore": 0.0,
  "commentsPeakViralTrendScore": 0.0,
  "commentsAverageViralTrendScore": 0.0,
  "url": "http://www.facebook.com/haderslevgolfrestaurant/posts/1023068134504609",
  "postType": "photo",
  "permaLink": "http://www.facebook.com/permalink.php?story_fbid=1023068134504609&id=450861208391974",
  "updatedAt": "2017-02-05T18:01:54+01:00",
  "createdAt": "0001-01-01T00:00:00+00:00",
  "entityId": "1023068134504609"
}
MERGE (language:Language)
ON CREATE
SET language = {
  "code": "da"
}
MERGE (author:FbUser {entityId:{
  "entityId": "450861208391974"
}.entityId})
ON MATCH
SET author.pictureUrl = "https://graph.facebook.com/450861208391974/picture?type=large"
ON MATCH
SET author.likes = 0
ON MATCH
SET author.username = null
ON MATCH
SET author.name = "Haderslev Golfrestaurant"
ON MATCH
SET author.updatedAt = "2017-02-05T18:04:18.0905356+01:00"
ON CREATE
SET author = {
  "pictureUrl": "https://graph.facebook.com/450861208391974/picture?type=large",
  "likes": 0,
  "username": null,
  "name": "Haderslev Golfrestaurant",
  "updatedAt": "2017-02-05T18:04:18.0905356+01:00",
  "userType": "Person",
  "createdAt": "2017-02-05T18:04:18.0905356+01:00",
  "entityId": "450861208391974"
}
MERGE (post)-[postpage:OWNED_BY]->(page)
MERGE (post)-[postlanguage:HAS_LANGUAGE]->(language)
MERGE (author)-[authorpost:WROTE]->(post)

[simonpinn]

Hi Thomas,

Just my first observation, without going into detail, the example above should be:

var pq = client.Cypher
                                .MergeEntity(fbPage, "page")
                                .MergeEntity(fbPagePost, "post")
                                .MergeEntity(fbPagePost.Language, "language")
                                .MergeEntity(fbPagePost.Author, "author")
                                .MergeRelationship(new OwnedByRelationship("post", "page"))
                                .MergeRelationship(new HasLanguageRelationship("post", "language"))
                                .MergeRelationship(new WroteRelationship("author", "post", fbPagePost.CreatedAt));
                            if (fbPagePost.Category != null)
                                pq = pq.MergeEntity(fbPagePost.Category, "category")
                                    .MergeRelationship(new WithCategoryRelationship("post", "category"));

                            if (fbPagePost.Author.Country != null)
                                pq = pq.MergeEntity(fbPagePost.Author.Country, "country")
                                    .MergeRelationship(new OriginatesRelationship("author", "country"));
                            if (fbPagePost.AttachedLink != null)
                                pq - pq.MergeEntity(fbPagePost.AttachedLink, "link")
                                    .MergeRelationship(new LinksToRelationsship("post", "link"));
                            pq.ExecuteWithoutResults();

The pq isn't stateful, it returns a query object from each fluent statement, so to conditionally add to the query you need to keep returning the value, ie pq = pq.MergeRelationship like string concatenation, instead you keep losing your changes to the query.

You will then be able to remove the additional executes.

I haven't gone into much detail as to what else is going on here, let me know if that helps you out.

cheers

Simon

[tlogik]

Worked like a charm. Thanks for clarifying. Appreciate your responses highly. Kindly Thomas