https://discord.umbraco.com logo
Join Discord
Powered by
How many times was used by a multinode tree picker...
# help-with-umbraco
p
is there a possibility to filter the values by how many times they were used by a multinode tree picker?
a
Something like this? - Page A: selected 5 times - Page C: selected 3 times - Page B: selected 1 time
p
Yes exactly
a
I'd say there isn't a straight forward way to do this, but you can probably do this by using Examine. If this is only for a single property, or all MNTP properties, could also affect how complex this is.
p
Thanks for your help Anders. I think it'll youse to much ressources. I think on doing it just different 🙂
a
Not that I have any number for backing this up, but I would assume using Examine and 
ISearchResult
would be more performant than using the content cache and 
IPublishedContent
.
Is it a requirement that it's only MNTP properties, or is any reference in general okay?
The reason that I'm asking is because when you select some items in an MNTP or another picker, the relations/references between the "parent" and the selected "children" are added to the database. So if it doesn't matter whether the pages were picked by a MNTP, a URL picker or some other picker, you can use the 
IRelationsService
to fetch relations. But this also hits the database, and might be a bit expensive. Possibly you can query the underlying database on your own with a more optimized query.
p
Actually it doesnt really matter by which picker it is picked because in this case we only use the MNTP. Hmm it could work out with the RelationService. Ill give it a try 🙂
a
If you're calling the database directly, I suppose your SQL query could look something like this:
Copy code
sql
SELECT [ChildId], COUNT(*) AS [Count]
FROM [umbracoRelation]
WHERE [relType] = 4
GROUP BY [ChildId]
ORDER BY [Count] DESC;
IRelationService
and the underlying database table use numeric IDs, so you can grab the 
ChildId
and look up the pages in the content cache. You can use Umbraco to access the database through 
IScopeProvider
, use it to create a new scope, and then IIRC that scope instance has a database property, which again has a 
Fetch
method. From memory, this would look something like:
Copy code
csharp
using IScope scope = _scopeProvider.CreateScope();

var rows = scope.Database.Fetch<YourCustomDto>(yourCustomSql);
Although this hits the database, and not one of Umbraco's caches, it might be the most performant and easiet approach to implement.
p
var dossiersWithMostArticles = allDossiers.ToList(); int[] dossierIds = dossiersWithMostArticles.Select(x => x.Id).ToArray(); var allrelations = _relationService.GetAllRelations(dossierIds);
This would be the one with the IRelationService?
a
Each relation in the database also has a numeric ID, so I think the 
GetAllRelations
method will look for those rather than the page IDs.
p
Hmm i see thats why i get no data...
a
There is a 
GetByChildId
method, which you may use, but then you have to call this for each page in 
allDossiers
, and then group and sort them by their count afterwards.
If you know the IDs of the pages you wish to look for, you can still use the SQL approach:
Copy code
sql
SELECT [ChildId], COUNT(*) AS [Count]
FROM [umbracoRelation]
WHERE [relType] = 4 AND [ChildId] IN (1138, 1158, 1256)
GROUP BY [ChildId]
ORDER BY [Count] DESC;
p
I have to be honest with you, i've actually never worked with a sql in Umbraco 😮 But its theres always a first time for something 🙂
Then i have to create a DTO for the Dossier right?
a
It's not a full model of the dossier - this should probably suffice:
Copy code
csharp
public class RelationDto {

    public int ChildId { get; set; }

    public int Count{ get; set; }

}
It it helps, here is all the parts summarized in a Razor example:
Copy code
csharp
@using Umbraco.Cms.Infrastructure.Scoping
@using Newtonsoft.Json.Linq
@inherits UmbracoViewPage
@inject IScopeProvider ScopeProvider

@functions {

    public class RelationDto {

        public int ChildId { get; set; }

        public int Count { get; set; }

    }

}

@{


    string sql = "SELECT [ChildId], COUNT(*) AS [Count] FROM [umbracoRelation] WHERE [relType] = 4 AND [ChildId] IN (1138, 1158, 1256) GROUP BY [ChildId] ORDER BY [Count] DESC;";

    using IScope scope = ScopeProvider.CreateScope();

    List<RelationDto> rows = scope.Database.Fetch<RelationDto>(sql);

    <pre>@JToken.FromObject(rows)</pre>


}
p
My Code currently looks like this: public IEnumerable GetDossiersWithMostArticles() { var allDossiers = GetAllDossiers().ToList(); int[] dossierIds = allDossiers.Select(x => x.Id).ToArray(); string sql = $"SELECT [ChildId], COUNT(*) AS [Count] FROM [umbracoRelation] WHERE [relType] = 4 AND [ChildId] IN ({string.Join(",",dossierIds)}) GROUP BY [ChildId] ORDER BY [Count] DESC;"; using IScope scope = _scopeProvider.CreateScope(); List rows = scope.Database.Fetch(sql); return allDossiers; } }
Thats the sql: SELECT [ChildId], COUNT(*) AS [Count] FROM [umbracoRelation] WHERE [relType] = 4 AND [ChildId] IN (1104,1105,1106,1107) GROUP BY [ChildId] ORDER BY [Count] DESC;
but still the Rows look empty
a
I think perhaps in your case the dossier is the parent in the relation, and the articles are the children
Does this change anything?
Copy code
sql
SELECT [ChildId], COUNT(*) AS [Count] FROM [umbracoRelation] WHERE [relType] = 4 AND [ParentId] IN (1104,1105,1106,1107) GROUP BY [ChildId] ORDER BY [Count] DESC;
p
Removing the RelType did the trick
a
Ahh. My bad - 
4
is media relations, whereas 
5
is document/content relations. Al least in my case. I just saw that I got results back, and though it was for content 😮 So might be good to either remove that part of the WHERE clause, or use 
IRelationService
to look up the relation type with the alias 
umbDocument
.
p
Seems like it works really well like this:
Copy code
C#
    public IEnumerable<Dossier> GetDossiersWithMostArticles()
    {
        var allDossiers = GetAllDossiers().ToList();
        int[] dossierIds = allDossiers.Select(x => x.Id).ToArray();

        
        string sql = $"SELECT [ChildId], COUNT(*) AS [Count] FROM [umbracoRelation] WHERE  [ChildId] IN ({string.Join(",",dossierIds)}) GROUP BY [ChildId] ORDER BY [Count] DESC;";

        using IScope scope = _scopeProvider.CreateScope();

        List<RelationDto> rows = scope.Database.Fetch<RelationDto>(sql);
        List<Dossier> sortedDossiers;
        foreach (var row in rows)
        {
            if (_umbracoContextAccessor.TryGetUmbracoContext(out var umbracoContext))
            {
                var content = umbracoContext.Content?.GetById(row.ChildId);
                if(content is Dossier dossier)
                {
                    yield return dossier;
                }
            }
        }
        
    }
what would you change if you were me?
a
eg:
Copy code
csharp
public IEnumerable<Dossier> GetDossiersWithMostArticles()
    {
        var allDossiers = GetAllDossiers().ToList();
        int[] dossierIds = allDossiers.Select(x => x.Id).ToArray();


        string sql = $"SELECT [ChildId], COUNT(*) AS [Count] FROM [umbracoRelation] WHERE  [ChildId] IN ({string.Join(",",dossierIds)}) GROUP BY [ChildId] ORDER BY [Count] DESC;";

        using IScope scope = _scopeProvider.CreateScope();

        List<RelationDto> rows = scope.Database.Fetch<RelationDto>(sql);
        List<Dossier> sortedDossiers;
        foreach (var row in rows)
        {
            if (_umbracoContextAccessor.TryGetUmbracoContext(out var umbracoContext))
            {
                var content = umbracoContext.Content?.GetById(row.ChildId);
                if(content is Dossier dossier)
                {
                    yield return dossier;
                }
            }
        }

    }
As for the code, I would change the following: - Your current getting the current Umbraco context for each row. Better move that to the start of the method, as there isn't any point in querying the database if you can't look up the IDs in the content cache anyways - 
.ToList()
and 
.ToArray()
calls are redundant, as you can pass 
IEnumerable<id>
on to the 
string.Join()
method. - 
sortedDossiers
variable doesn't appear to be used
I think I would write the method as:
Copy code
csharp
public IEnumerable<Dossier> GetDossiersWithMostArticles() {

    // Try to get a reference to the current Umbraco context
    if (!_umbracoContextAccessor.TryGetUmbracoContext(out IUmbracoContext? umbracoContext)) return Enumerable.Empty<Dossier>();

    // According to code analysis, the "Content" property may be null, so we'd better add a null check
    if (umbracoContext.Content is null) return Enumerable.Empty<Dossier>();

    // Get the IDs for the dossiers
    IEnumerable<int> dossierIds = GetAllDossiers().Select(x => x.Id);

    // TODO: Somehow add check for whether "dossierIds" is empty as the SQL query otherwise will fail

    // Declare the SQL for the call to the database
    string sql = $"SELECT [ChildId], COUNT(*) AS [Count] FROM [umbracoRelation] WHERE [ChildId] IN ({string.Join(",", dossierIds)}) GROUP BY [ChildId] ORDER BY [Count] DESC;";

    // Create a new scope
    using IScope scope = _scopeProvider.CreateScope();

    // Make the call to the database
    return scope.Database
        .Fetch<RelationDto>(sql)
        .Select(x => umbracoContext.Content!.GetById(x.ChildId))
        .OfType<Dossier>();

}
p
Good morning @Anders Bjerner , thanks for your reply and help! I'll refactor my code. Your suggestions make absolute sense for me! 🙂
7 Views
PreviousNext
Powered by