Admin panel SQL Query: how to retrieve users using DocumentId

[MarGraz]

Hi,

from the admin panel, in the "Queries" section, I wrote this query SELECT DocumentId FROM UserIndex to retrieve the users Document Id. I read the documentation, but I don't know which type I need to use in the schema to show the result in the "GraphQL" section.

Can somebody help me?

Thank you

[MarGraz]

The idea is to use the user query in the "Workflows" section, any ideas? 🤔 Or I need to implement a custom controller?

[MarGraz]

@Piedone sorry for the direct mention, but I have been stuck on this issue for several days. Do you have any ideas that might help? Thank you

[Piedone]

You can use this for the schema:

{
    "type": "object",
    "properties": { 
        "UserId" : {
            "type": "string"
        },
        "NormalizedUserName" : {
            "type": "string"
        },
        "NormalizedEmail" : {
            "type": "string"
        }
    }
}

With the SQL:

SELECT UserId, NormalizedUserName, NormalizedEmail FROM UserIndex

And then you can test it in the GraphiQL browser. See it in action:

[MarGraz]

Thank you, it works 🥳
The checkbox "Return Documents" in the UI was confusing me. I initially thought that I needed to retrieve the "Document Id" first in order to get the user. 🫠

This schema allows me to query any table in the SQL database, correct? Would it be useful to include this example in the documentation here?

Thank you

[MarGraz]

@Piedone I'm attempting to retrieve user roles as well, but when querying, if a user belongs to multiple roles, I receive multiple objects. My query:

SELECT 
    UI.DocumentId, 
    UI.UserId, 
    UI.NormalizedUserName, 
    UI.NormalizedEmail, 
    UBRNI.RoleName,
    UBRNID.UserByRoleNameIndexId AS RoleId
FROM 
    UserIndex UI
INNER JOIN 
    UserByRoleNameIndex_Document UBRNID ON UI.DocumentId = UBRNID.DocumentId
INNER JOIN 
    UserByRoleNameIndex UBRNI ON UBRNID.UserByRoleNameIndexId = UBRNI.Id

Query result:

[
    {
        "DocumentId": 20,
        "UserId": "4hsvxrb9ejy1g1nh4n04cevw5y",
        "NormalizedUserName": "MARCO",
        "NormalizedEmail": "[email protected]",
        "RoleName": "ADMINISTRATOR",
        "RoleId": 1
    },
    {
        "DocumentId": 20,
        "UserId": "4hsvxrb9ejy1g1nh4n04cevw5y",
        "NormalizedUserName": "MARCO",
        "NormalizedEmail": "[email protected]",
        "RoleName": "MODERATOR",
        "RoleId": 2
    }
]

To address this, I tried using the STRING_AGG function, but the Orchard Core SQL editor returned a syntax error with my query:

SELECT 
    UI.DocumentId, 
    UI.UserId, 
    UI.NormalizedUserName, 
    UI.NormalizedEmail, 
    STRING_AGG(UBRNI.RoleName, ',') AS RoleNames,
    STRING_AGG(CAST(UBRNID.UserByRoleNameIndexId AS VARCHAR), ',') AS RoleIds
FROM 
    UserIndex UI
JOIN 
    UserByRoleNameIndex_Document UBRNID ON UI.DocumentId = UBRNID.DocumentId
JOIN 
    UserByRoleNameIndex UBRNI ON UBRNID.UserByRoleNameIndexId = UBRNI.Id
GROUP BY 
    UI.DocumentId, 
    UI.UserId, 
    UI.NormalizedUserName, 
    UI.NormalizedEmail

The error returned:

Syntax error, expected: ., (, +, -, *, /, %, &, |, ^, =, >, <, >=, <=, <>, !=, !<, !>, AND, OR, LIKE, NOT LIKE, NOT, BETWEEN, IN, ,, ), > GROUP, HAVING, ORDER, LIMIT, OFFSET, UNION, ;, WITH, SELECT, INNER, LEFT, RIGHT, JOIN, WHERE at line:6, col:49

It seems that the STRING_AGG function isn't supported by the Orchard Core query editor, instead it works in SQL Server Management Studio. This limitation makes sense as aggregating data is generally recommended in the business logic rather than directly in the query.

So, how can I retrieve the roles information in my query without receiving multiple objects for each user? I want to use it in a Workflow.

Thank you

[Piedone]

The object schema lets you query any table, yes. This is documented though, and actually where I got it too :). https://docs.orchardcore.net/en/main/docs/reference/modules/Queries/#graphql:~:text=Here%20is%20how%20you%20would%20define%20a%20Schema%20for%20this%20Query%20to%20add%20it%20to%20the%20GraphQL%20endpoint

OC parses the query with a safe custom query parser, it's not directly sent to the underlying DB engine. Engine-specific features are not supported, since this should be DB-agnostic.

Please open a new discussion for new questions.

[MarGraz]

The object schema lets you query any table, yes. This is documented though, and actually where I got it too :). https://docs.orchardcore.net/en/main/docs/reference/modules/Queries/#graphql:~:text=Here%20is%20how%20you%20would%20define%20a%20Schema%20for%20this%20Query%20to%20add%20it%20to%20the%20GraphQL%20endpoint

Thank you. I think the checkbox "Return Documents" in the UI was confusing me, because users are also contained in the Document DB table, so I assumed to retrieve the "Document Id" first, in order to get the user. Maybe I could suggest some updates to the documentation.

OC parses the query with a safe custom query parser, it's not directly sent to the underlying DB engine. Engine-specific features are not > supported, since this should be DB-agnostic.

Since I'm using a SQL DB, I was able to execute the query as suggested here, using the STRING_AGG function. I agree, a DB-agnostic solution is better.

Please open a new discussion for new questions.

I'm sorry, I thought it would be more appropriate to continue the discussion here since it was related to the same topic. However, I understand the thread has become too long.

Thank you

[lampersky]

@MarGraz, as a workaround you can use JSON_QUERY, and query Document directly:

SELECT 
    UI.DocumentId, 
    UI.UserId, 
    UI.NormalizedUserName, 
    JSON_QUERY(d.Content, '$.RoleNames') AS RoleNames
FROM 
    UserIndex UI
INNER JOIN 
	Document d ON d.Id = UI.DocumentId;

Please note, RoleNames it is a stringified array. So, you need to parse it again in your workflow by using JSON.parse().

[MarGraz]

I prefer your second approach ☺️

[lampersky]

@MarGraz

I've just checked again, because I remember that STRING_AGG function should work.
The problem is with casting using CAST(UBRNID.UserByRoleNameIndexId AS VARCHAR), you can get rid of it and then parse string in your workflow.

[MarGraz]

@lampersky thank you for your reply. I was casting because I thought it was necessary, given that UserByRoleNameIndex.Id is a bigint. That's good to know 😊

[Skrypt]

You guys should simply create a custom GraphQL query by using already usefull user services. Just saying... that would save you time and you will learn how to do it by code.

[MarGraz]

@Skrypt you're right.

I chose this approach because I learned that saved queries can be used into "Workflows", so I decided to use them.
What approach do you typically use? If you could describe it, maybe I could adopt it too.

Of course, if there's an option to use saved queries, especially for straightforward tasks in workflows, I'm happy to use it. Because this functionality is available, I'd be also happy to apply it in certain situations.

Thank you

[Skrypt]

You would then need to create a custom Workflow Activity that calls the services that you require.

[Piedone]

Tutorial for extending GraphQL: https://github.com/Lombiq/Orchard-Training-Demo-Module/blob/dev/Lombiq.TrainingDemo/GraphQL/Startup.cs