Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at

  • Mahender Singh 33 posts 154 karma points
    18 days ago
    Mahender Singh

    How Umbraco Sql query create to delete node

    Hi Team,

    Can you please let me know, how or where Umbraco create sql query which is mention below: I am facing issue while executing this query for "order by alias"

    SELECT * FROM (SELECT poco_base.*, ROW_NUMBER() OVER (ORDER BY ([alias])) poco_rn 
    FROM ( 
    SELECT DISTINCT [pn].[id] as nodeId, [pn].[uniqueId] as nodeKey, [pn].[text] as nodeName, [pn].[nodeObjectType] as nodeObjectType, [ct].[icon] as contentTypeIcon, [ct].[alias] as contentTypeAlias, [ctn].[text] as contentTypeName, [umbracoRelationType].[alias] as relationTypeAlias, [umbracoRelationType].[name] as relationTypeName, [umbracoRelationType].[isDependency] as relationTypeIsDependency, [umbracoRelationType].[dual] as relationTypeIsBidirectional
    FROM [umbracoRelation] [r]
    INNER JOIN [umbracoRelationType] [umbracoRelationType]
    ON ([r].[relType] = [umbracoRelationType].[id])
    INNER JOIN [umbracoNode] [cn]
    ON (([umbracoRelationType].[dual] = 0 AND ([r].[parentId] = [cn].[id])) OR ([umbracoRelationType].[dual] = 1 AND (([r].[childId] = [cn].[id]) OR ([r].[parentId] = [cn].[id]))))
    INNER JOIN [umbracoNode] [pn]
    ON ((([pn].[id] = [r].[childId]) AND ([cn].[id] = [r].[parentId])) OR (([pn].[id] = [r].[parentId]) AND ([cn].[id] = [r].[childId])))
    LEFT JOIN [umbracoContent] [c]
    ON ([pn].[id] = [c].[nodeId])
    LEFT JOIN [cmsContentType] [ct]
    ON ([c].[contentTypeId] = [ct].[nodeId])
    LEFT JOIN [umbracoNode] [ctn]
    ON ([ct].[nodeId] = [ctn].[id])
    WHERE ([pn].[id] IN (SELECT [umbracoNode].[id] AS [NodeId]
    FROM [umbracoNode]
    WHERE ([umbracoNode].[path] LIKE (SELECT concat([node].[path],'','')
    FROM [umbracoNode] [node]
    WHERE (([node].[id] = 1997))))))
    AND ([umbracoRelationType].[isDependency] = 1)
    ) poco_base ) poco_paged 
    WHERE poco_rn > 0 AND poco_rn <= 10
    ORDER BY poco_rn    
  • Sam Beynon 8 posts 98 karma points
    18 days ago
    Sam Beynon

    Hi Mahender,

    I believe your issue stems from the fact that [alias] is not defined within the SELECT list of your derived table.

    You surface the following two alias values - enter image description here

    You would need to replace the [alias] in your row number order by with the correct one for your needs.

    However, there are a number of ways that you could probably achieve this using Umbraco services/cache, if you give some more information as to what you are trying to do, perhaps I could give some further advice on a different (C#) approach?

  • Mahender Singh 33 posts 154 karma points
    17 days ago
    Mahender Singh

    Thanks Sam for update.

    Acutally I am trying to delete content node in Umbraco editor and it always fails with this issue. This query is created by Umbraco c# code and i want to know how it creates so i can change it.

    I am able to create, rename content but not able to delete.

    I tried with new database creation with Umbraco helper files but still same issue.

  • Sam Beynon 8 posts 98 karma points
    16 days ago
    Sam Beynon

    Hi Mahender,

    There are a number of services exposed by Umbraco that will massively simplify CRUD operations on Umbraco owned objects.

    If you're trying to delete a content node, you can do it using the ContentService

    You can inject it into your class, or a controller as follows -

    public class MyClass
        private IContentService _contentService;
        public MyClass(IContentService contentService)
            _contentService = contentService;

    You can then delete the node by retrieving the IContent representation from the database and then passing it into the IContentService.Delete method.

    int nodeId = 100;
    var node = _contentService.GetById(nodeId); //Can accept a GUID or an int

    There are also methods within the IContentService to allow full CRUD on IContent objects.

    Does this give you what you need?

  • Sam Beynon 8 posts 98 karma points
    15 days ago
    Sam Beynon

    Hi Mahender,

    I've just re-read your response and realised that i misunderstood what you were saying.

    I believe you're having an issue in the actual Umbraco admin section when manually deleting a node?

    This is very strange, what version of Umbraco are you using?

  • Mahender Singh 33 posts 154 karma points
    1 week ago
    Mahender Singh

    Hi Sam,

    I am using version 9.5.0.

    And the most strang this is that, when i publish same code on my local IIS it works fine but when i publish same code on AWS VM window server with IIS it through this issue.

    I run the sql profiler as well and find that, on local IIS it create different sql query then AWS server. That's why i fails on AWS server.

    I don't know, what HTTP module or handler create this issue on AWS server.

Please Sign in or register to post replies

Write your reply to: