Forum Api Project: Setup #7 View Models

  • This script creates 2 procedures which return view models in JSON format.  The view models return the posts for a thread in either recent view (plain old normal view) or threaded view (similar to old Drupal with nesting expanded).  Both implement offset paging.

    set nocount off;

    /* determine if schema 'frm' already exits. If it does not, then throw an exception. */
    if not exists (select 1 from sys.schemas where name = 'frm')
    throw 50000, 'The frm schema does not exist. Please run ForumApiSQLServerDDLSetup1.sql.', 1;

    /* drop all objects */
    drop proc if exists frm.api_posts_recent_view_get;
    drop proc if exists frm.api_posts_threaded_view_get;
    go

    --drop proc frm.api_posts_recent_view_get;
    create or alter proc frm.api_posts_recent_view_get
    @t_id int,
    @n int,
    @s int
    as
    set nocount on;
    with
    thread_cte as (
    select
    t.t_id,
    t.title thread_title,
    t.body thread_body
    from
    frm.threads t
    where
    t.t_id=@t_id),
    posts_cte as (
    select
    p.p_id,
    p.orig_p_id,
    p.reply_p_id,
    p.reply_depth_count,
    p.title post_title,
    p.body post_body,
    p.created_dt post_created_dt,
    p.edited_dt post_edited_dt,
    p.up_count,
    p.down_count,
    u.id u_id,
    u.username,
    u.created_dt user_created_dt,
    row_number() over(order by p.created_dt desc) ranking
    from
    frm.posts p
    join
    frm.users u on p.u_id=u.id
    where
    p.t_id=@t_id
    and p.banned_flag<>1
    order by
    p.created_dt desc
    offset
    (@n-1)*@s rows
    fetch next
    @s rows only)
    select
    tc.*,
    (select * from posts_cte order by ranking asc for json path) posts
    from
    thread_cte tc
    for json path, without_array_wrapper;
    set nocount off
    go

    --drop proc api_posts_threaded_view_get;
    create or alter proc frm.api_posts_threaded_view_get
    @t_id int,
    @n int,
    @s int
    as
    set nocount on;
    with
    thread_cte as (
    select
    t.t_id,
    t.title thread_title,
    t.body thread_body
    from
    frm.threads t
    where
    t.t_id=@t_id),
    posts_cte as (
    select
    p.p_id,
    p.orig_p_id,
    p.reply_p_id,
    p.reply_depth_count,
    p.title post_title,
    p.body post_body,
    p.created_dt post_created_dt,
    p.edited_dt post_edited_dt,
    p.up_count,
    p.down_count,
    u.id u_id,
    u.username,
    u.created_dt user_created_dt,
    row_number() over(order by p.orig_p_id desc, p.tree_path asc) ranking
    from
    frm.posts p
    join
    frm.users u on p.u_id=u.id
    where
    p.t_id=@t_id
    and p.banned_flag<>1
    order by
    p.orig_p_id desc,
    p.tree_path asc
    offset
    (@n-1)*@s rows
    fetch next
    @s rows only)
    select
    tc.*,
    (select * from posts_cte order by ranking asc for json path) posts
    from
    thread_cte tc
    for json path, without_array_wrapper;
    set nocount off
    go

    --select * from frm.threads
    --select * from frm.posts

    exec frm.api_posts_recent_view_get 2, 1, 50;
    exec frm.api_posts_threaded_view_get 2, 1, 50;

     

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply