Table location of page URLs

Vote:
 

Hi there,

I am trying to find out which SQL table contains the page URL used for the View on Website link in the CMS. I am trying to export all URLs for a specific page type to another DB.

#317464
Feb 21, 2024 15:40
Vote:
 

Hi,

To the best of my knowledge, there's not a table or view which stores the resolved URLs in a ready-to-go format. There's a bit of logic required to construct the URL but, at its most basic, the URL for a given page is constructed by combining the URL segments of the page's ancestors. Depending on configuration, a language segment or host and protocol may need to be be prepended.

If you needed to do that in SQL you may be able to encapsulate the basic logic in a function but it probably wouldn't be that efficient if you're trying to get a load of page URLs all at once. I suspect your best bet would be to extract the urls separately using either a custom script calling the supported APIs (e.g. UrlResolver) or, if you have it installed, you could potentially use the content delivery API. If you've got an XML sitemap generator like Geta Optimizely Sitemaps, that may also be an option for you.

#317528
Feb 22, 2024 10:29
Vote:
 

Hi,

Touch the sql database and playing around with it is going to take a long time to get it right.

If you want some quick and easy then it would be better to use the API's in Optimizely, something like this could help:

public class OptiPageService
{
    private readonly IContentLoader _contentLoader;

    public OptiPageService()
    {
        _contentLoader = ServiceLocator.Current.GetInstance<IContentLoader>();
    }

    public IEnumerable<string> GetExternalUrlsOfPageType<T>() where T : PageData
    {
        var externalUrls = new List<string>();

        var descendents = _contentLoader.GetDescendents(ContentReference.RootPage);

        foreach (var descendent in descendents)
        {
            _contentLoader.TryGet<T>(descendent, out var page);

            if(page !=null)
            {
                var externalUrl = GetExternalUrl(page, true, "en");

                if (!string.IsNullOrEmpty(externalUrl))
                {
                    externalUrls.Add(externalUrl);
                }
            }
        }


        return externalUrls;
    }

    private string GetExternalUrl(PageData page, bool absoluteUrl, string languageBranch)
    {
        var result = ServiceLocator.Current.GetInstance<IUrlResolver>().GetUrl(page.ContentLink, languageBranch, new VirtualPathArguments
        {
            ContextMode = ContextMode.Default,
            ForceCanonical = absoluteUrl
        });

        if (absoluteUrl)
        {
            Uri relativeUri;

            if (Uri.TryCreate(result, UriKind.RelativeOrAbsolute, out relativeUri))
            {
                if (!relativeUri.IsAbsoluteUri)
                {
                    var siteDefinitionResolver = ServiceLocator.Current.GetInstance<ISiteDefinitionResolver>();
                    var siteDefinition = siteDefinitionResolver.GetByContent(page.ContentLink, true, true);
                    var hosts = siteDefinition.GetHosts(page.Language, true);
                    var host = hosts.FirstOrDefault(h => h.Type == HostDefinitionType.Primary) ?? hosts.FirstOrDefault(h => h.Type == HostDefinitionType.Undefined);

                    var basetUri = siteDefinition.SiteUrl;

                    if (host != null)
                    {
                        Uri.TryCreate(siteDefinition.SiteUrl.Scheme + "://" + host.Name, UriKind.Absolute, out basetUri);
                    }

                    var absoluteUri = new Uri(basetUri, relativeUri);

                    return absoluteUri.AbsoluteUri;
                }
            }
        }

        return result;
    }
}

Then use like this:

var service = new OptiPageService();
var urls = service.GetExternalUrlsOfPageType<StartPage>();

You can change the 'StartPage' to the specific page you need to export.

Then all you need to do is use something like CSVHelper, to out to a csv file.

Let me know if that helps

#317585
Feb 23, 2024 11:19
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.