Sign in Register
Go to solution
Copied to clipboard

Flag this post as spam?

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


  • Laurence Gillian 600 posts 1219 karma points
    Jun 23, 2016 @ 16:23
    Laurence Gillian
    0

    Effective Database Querying (Custom Tables, Umbraco Migrations, PetaPoco)

    Hello!

    After a great CodeGarden, I thought I'd try out some of what I have learnt. I wanted to play with using Umbraco Migrations and moving something that shouldn't be content managed away from Umbraco.

    To do this, I have written a basic service for handling content likes. The service (below) is used directly in my Likes partial and the 'Add' part is exposed to a SurfaceController that allows visitors to 'Like' content via a rad AJAX request! Wahoo!

    Each time a website visitor likes a piece of content, I add a row to a custom DB table containing the 'NodeId' and a 'GUID' that is unique to their session & membership.

    This is partly based on Sebastian's great tutorial here.

    I do have a couple of questions! Which might be fun someone to answer, hopefully maybe!!

    1. new Sql() gives a warning of "PetaPecoSqlExtensions are now obsolete", I've had a good dig around, but couldn't find an obvious way to replace this.

    2. GetNodeLikes, in this method, I filter the table by the UserId, and then the NodeId which is passed in from the page it is called from. This is based on the assumption, that there will be more UserId's than pieces of Content. At CG, performance of both in memory and DB queries did come up quite a few times, so I'd like to ask - Is this ugly from a performance point of view?

    3. Any other ideas! I'd love to hear!

    (I am planning to cache to result in MemoryCache)

    My code!

    using System;
using System.Linq;
using Umbraco.Core;
using Umbraco.Core.Persistence;

namespace AnitaRani.Blog.Logic.Services
{
    using Common;
    using Model.CustomTables;

    public class LikesService
    {

        public bool AddNodeLike (int nodeId)
        {
            Guid userId = SessionWrapper.CurrentGuid; // (magic)
            var database = ApplicationContext.Current.DatabaseContext.Database;
            database.Insert(new Likes { NodeId = nodeId, UserId = userId });
            return true;
        }

        public int GetNodeLikes(int nodeId, bool disableCount = false)
        {
            Guid userId = SessionWrapper.CurrentGuid;
            var database = ApplicationContext.Current.DatabaseContext.Database;
            var query = new Sql().Select("*").From<Likes>().Where<Likes>(x => x.UserId == userId);

            var result = database.Fetch<Likes>(query).DistinctBy(x => x.NodeId == nodeId);

            if (disableCount)
            {
                return (result.Any()) ? 1 : 0;
            }

            return result.Count();
        }

    }
}

    Thanks! Laurie

    Copy Link
  • Stephen 767 posts 2273 karma points c-trib
    Jun 23, 2016 @ 16:49
    Stephen
    101

    1. what exactly in Sql() is reported as obsolete?

    2. database.ExecuteScalar(query) where query is new Sql().Select("COUNT(DISTINCT NodeId)") would be more efficient.

    Copy Link
  • Laurence Gillian 600 posts 1219 karma points
    Jun 23, 2016 @ 17:43
    Laurence Gillian
    0

    Hi Stephen!

    Thank-you for your reply. A couple of light bulb moments there!

    It's looking a bit sleeker now:

    var query = new Sql().Select("COUNT(DISTINCT NodeId) FROM LIKES")
                     .Where<Likes>(x => x.UserId == userId & x.NodeId == nodeId);

var result = database.ExecuteScalar<int>(query);

return result;

    Re: Obsolete!

    Was coming from the .From part in PetaPocoSqlExtensions. The message is 'Use the overload specifying ISqlSyntaxProvider instead'. I guess this isn't suggesting to write it as plain SQL as I have above, but some strongly typed way I don't know about!

    (big thumbs up emoji) Laurie

    Copy Link
  • Stephen 767 posts 2273 karma points c-trib
    Jun 23, 2016 @ 17:48
    Stephen
    1

    The DatabaseContext also references an SqlSyntax.

    Most of these obsolete methods want you to pass that syntax.

    Eg. From<Foo>() becomes From<Foo>(sqlSyntax).

    Making sense?

    Copy Link
  • Laurence Gillian 600 posts 1219 karma points
    Jun 23, 2016 @ 18:08
    Laurence Gillian
    0

    Ahha got it! I've re-posted fully tweaked below, as the original code above didn't work properly.

        using System;
    using System.Linq;
    using Umbraco.Core;
    using Umbraco.Core.Persistence;

    namespace AnitaRani.Blog.Logic.Services
    {
        using Common;
        using Model.CustomTables;

        public class LikesService
        {
            DatabaseContext dc = ApplicationContext.Current.DatabaseContext;

            public bool AddNodeLike (int nodeId)
            {
                Guid userId = SessionWrapper.CurrentGuid; // Custom Session Wrapper (not Umbraco)
                dc.Database.Insert(new Likes { NodeId = nodeId, UserId = userId });
                return true;
            }

            public int GetNodeLikes(int nodeId, bool disableCount = false)
            {
                Guid userId = SessionWrapper.CurrentGuid;
                var query = new Sql().Select("COUNT(DISTINCT NodeId)")
                                     .From<Likes>(dc.SqlSyntax)
                                     .Where<Likes>(x => x.UserId == userId & x.NodeId == nodeId);
                var result = dc.Database.ExecuteScalar<int>(query);

                return result;
            }

        }
    }
    Copy Link
Please Sign in or register to post replies

Write your reply to:

Draft