Acting on some good advice from this forum, I have used Ajax to allow visitors to create comments and like records against these articles in a SQL table (inside the Umbraco db but not tables Umbraco is aware of).
This works very nicely and keeps the number of documents in Umbraco itself more manageable and maintains performance.
Also, when someone views an article, I use Ajax to fetch a list of comments and the number of likes and display this with javascript.
Also, very good.
My problem is I want to display a list of most commented and most liked articles.
So far, I have listed documents within Partial Views using node.children() and filtering and sorting on this.
However, is there any way of joining to the external database in the Partial View so I can join articles to comments (for example) so I can sort in descending order based on the number of external comments records for each article.
I hope this makes sense - and is possible!
My only other idea is to use Ajax to fetch the list of nodeIds in descending order of number of comments and then list through this fetching each article in turn to get its title etc - but this wouldn't be efficient.
Hi Tim
I would create a stored procedure to return the count of comment and likes for each node id. I would then stored the results of this in the cache as it isn't large amounts of data just a dictionary or tuple of numbers. Read more about this sort of thing in my blog post.
Just to summarise - I create a class in cs file to load the data from a stored proc which I can then access in a view?
Example : my stored proc returns the ids of the 3 highest scoring documents in a dictionary, then I use those in the partial view
.children().Where.....
etc
?
It does mean two separate database calls (the one returning the 3 highest hits ids, plus the partial view node listing - although I am beginning to suspect the latter doesn't refer to the back end db, but Umbraco's own cache?
using System;
using System.Collections.Generic;
using Umbraco.Web;
using System.Runtime.Caching;
namespace CodeShare.Example
{
public static class CacheExample
{
public static Dictionary
Joining to external tables within Partial View
I have created a site with news articles.
Acting on some good advice from this forum, I have used Ajax to allow visitors to create comments and like records against these articles in a SQL table (inside the Umbraco db but not tables Umbraco is aware of).
This works very nicely and keeps the number of documents in Umbraco itself more manageable and maintains performance.
Also, when someone views an article, I use Ajax to fetch a list of comments and the number of likes and display this with javascript.
Also, very good.
My problem is I want to display a list of most commented and most liked articles.
So far, I have listed documents within Partial Views using node.children() and filtering and sorting on this.
However, is there any way of joining to the external database in the Partial View so I can join articles to comments (for example) so I can sort in descending order based on the number of external comments records for each article.
I hope this makes sense - and is possible!
My only other idea is to use Ajax to fetch the list of nodeIds in descending order of number of comments and then list through this fetching each article in turn to get its title etc - but this wouldn't be efficient.
Any ideas?
Hi Tim I would create a stored procedure to return the count of comment and likes for each node id. I would then stored the results of this in the cache as it isn't large amounts of data just a dictionary or tuple of numbers. Read more about this sort of thing in my blog post.
http://www.codeshare.co.uk/blog/how-to-create-a-page-view-hit-counter-in-umbraco/
Paul
I had a look at our post - very interesting.
Just to summarise - I create a class in cs file to load the data from a stored proc which I can then access in a view?
Example : my stored proc returns the ids of the 3 highest scoring documents in a dictionary, then I use those in the partial view
.children().Where.....
etc
?
It does mean two separate database calls (the one returning the 3 highest hits ids, plus the partial view node listing - although I am beginning to suspect the latter doesn't refer to the back end db, but Umbraco's own cache?
Hi Tim I had something like this in mind:
using System; using System.Collections.Generic; using Umbraco.Web; using System.Runtime.Caching; namespace CodeShare.Example { public static class CacheExample { public static Dictionaryis working on a reply...