Powerful and easy to use cms to build websites, blogs or ecommerce stores.
HTML backendblogblog-engineblog-platformbloggingcmscontent-managementcontent-management-systemecommerceecommerce-platformno-codepage-builderphpphp-cmsphp7php8self-hostedshopping-cartweb-applicationwebsite-builder
-- Comments
-- get all comment
CREATE PROCEDURE getAll(
-- variables
IN language_id INT,
IN post_id INT,
IN user_id INT,
IN admin_id INT,
IN status INT,
IN post_title INT,
-- pagination
IN start INT,
IN limit INT,
IN cursor INT, -- current cursor (comment_id) for cursor pagination
IN order CHAR,
-- return
OUT fetch_all, -- orders
OUT fetch_one -- count
)
BEGIN
SELECT "user".username, "user".email, "user".first_name, "user".last_name, "user".display_name, "user".avatar, "user".bio, "user".subscribe, comment.*, comment_id as array_key
@IF isset(:post_title) AND :post_title
THEN
,post_content.name, post_content.slug
END @IF
FROM comment
LEFT JOIN "user" ON ("user".user_id = comment.user_id)
@IF isset(:admin_id) AND :admin_id
THEN
LEFT JOIN post ON (post.post_id = comment.post_id)
END @IF
@IF isset(:post_title) AND :post_title
THEN
LEFT JOIN post_content ON (post_content.post_id = comment.post_id)
END @IF
WHERE 1 = 1
-- post
@IF isset(:post_id)
THEN
AND comment.post_id = :post_id
END @IF
-- post slug
@IF isset(:slug)
THEN
AND comment.post_id = (SELECT post_id FROM post_content WHERE slug = :slug LIMIT 1)
END @IF
-- user
@IF isset(:user_id)
THEN
AND comment.user_id = :user_id
END @IF
-- admin
@IF isset(:admin_id)
THEN
AND post.admin_id = :admin_id
END @IF
-- user
@IF isset(:language_id) AND isset(:post_title)
THEN
AND post_content.language_id = :language_id
END @IF
-- status
@IF isset(:status)
THEN
AND comment.status = :status
END @IF
-- cursor pagination
@IF isset(:cursor)
THEN
AND comment.comment_id > :cursor
END @IF
ORDER BY parent_id, comment_id
@IF isset(:order) AND :order == "desc"
THEN
DESC
END @IF
LIMIT :limit OFFSET :start;
SELECT count(*) FROM (
@SQL_COUNT(comment_id, comment) -- this takes previous query removes limit and replaces select columns with parameter comment_id
) as count;
END
-- get one comment
CREATE PROCEDURE get(
IN comment_id INT,
IN admin_id INT,
OUT fetch_row
)
BEGIN
SELECT *
FROM comment AS _
LEFT JOIN "user" ON "user".user_id = _.user_id
@IF isset(:admin_id)
THEN
INNER JOIN post ON post.post_id = _.post_id AND post.admin_id = :admin_id
END @IF
WHERE 1 = 1
@IF isset(:comment_id)
THEN
AND _.comment_id = :comment_id
END @IF
LIMIT 1;
-- SELECT "key" as array_key,"value" as array_value FROM comment_meta as _
-- WHERE _.comment_id = @result.comment_id
END
-- Add new comment
CREATE PROCEDURE add(
IN comment ROW,
OUT fetch_one
)
BEGIN
-- allow only table fields and set defaults for missing values
@FILTER(:comment, comment)
INSERT INTO comment
( @KEYS(:comment) )
VALUES ( :comment ) RETURNING comment_id;
END
-- Edit comment
CREATE PROCEDURE edit(
IN comment ROW,
IN comment_id INT,
IN admin_id INT,
OUT affected_rows
)
BEGIN
-- allow only table fields and set defaults for missing values
@FILTER(:comment, comment)
UPDATE comment
SET @LIST(:comment)
WHERE comment_id = :comment_id
@IF isset(:admin_id)
THEN
AND comment.post_id IN (SELECT post_id FROM post WHERE post.admin_id = :admin_id)
END @IF;
END
-- Delete comment
CREATE PROCEDURE delete(
IN comment_id ARRAY,
IN admin_id INT,
OUT affected_rows
)
BEGIN
DELETE FROM comment
@IF isset(:admin_id)
THEN
USING post
END @IF
WHERE 1 = 1
@IF isset(:admin_id)
THEN
AND post.post_id = comment.post_id AND post.admin_id = :admin_id
END @IF
AND comment.comment_id IN (:comment_id)
END