how umbraco sql query create to delete node

Press Ctrl / CMD + C to copy this to your clipboard.

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 39 posts 170 karma points

Mar 13, 2023 @ 07:04

Using Umbraco And Getting Started

Umbraco 9

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

Copy Link

Sam Beynon 8 posts 98 karma points

Mar 13, 2023 @ 07:56

0

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 -

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?

Copy Link
Mahender Singh 39 posts 170 karma points

Mar 14, 2023 @ 04:38

0

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.

Copy Link
Sam Beynon 8 posts 98 karma points

Mar 14, 2023 @ 10:41
0
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
_contentService.Delete(node);
```
There are also methods within the IContentService to allow full CRUD on IContent objects.

Does this give you what you need?
Copy Link
Sam Beynon 8 posts 98 karma points

Mar 15, 2023 @ 16:49

0

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?

Copy Link
Mahender Singh 39 posts 170 karma points

Mar 23, 2023 @ 08:16

0

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.

Copy Link
EwanMcP 1 post 21 karma points

Jul 12, 2023 @ 16:27

0

Same issue here after upgrading MSSQL from 2019 to 2022 (even if 2019 compatibility mode is enabled).

Umbraco version 9.5.4, .net 6. Reproduced in 2 separate environments, and the logs and a trace confirmed this exact issue (ambiguous "alias" reference in ORDER BY clause).

Any resolution on your side Mahender?

Copy Link
Mahender Singh 39 posts 170 karma points

Aug 08, 2023 @ 02:49

0

Hi EwanMcp,

Sorry but still not able to resolve this issue. these days busy with other stuff, so not able to answer.

Please let me know, if you find something.

Copy Link
Julian Gallo Londono 2 posts 71 karma points

Aug 23, 2023 @ 21:36

0

Hi EwanMcp

I am facing the same issue in version 9.5.4, could you fix the error somehow?

Thanks!

Copy Link
is working on a reply...

Please Sign in or register to post replies