What is the project about?

Philippines Daily Express, is a newspaper styled web application built with node.js and express. Basically the web application looks like a newspaper but the content of it is Steemit posts for the #philippines tag on Steemit.

The Data comes from STEEMSQL, The application gets the top commented post, top upvoted post, top post with pending payout value within the past time of loading the page to 24 hours before. Mimicking a newspaper that adds news from the previous 24 hours the least.

The posts are displayed on each column with the title, author of the post, the featured image of the post, a small excerpt of the post, stats of the post(Created date of the post, number of comments, Total number of upvotes, and post pending rewards).

You can visit the live link of the web appication here:
https://philippines-daily-express.herokuapp.com

SQL queries done to retrieve the data

Top Commented - /queries/topCommented.sql

select top 1 
    *,
    JSON_QUERY(json_metadata, '$.tags')
from 
   Comments (NOLOCK)
where 
   title <> '' and
   parent_author = '' and
   category = 'philippines' and
   datediff(hour, created, GETDATE()) between 0 and 24
order by 
   children desc

This SQL query gets the top 1 commented post with the #philippines tag(first tag of the post) and gets the created time of the post then used to retrieve the posts of the time the app was loaded and 24 hours before.

Top Upvoted - /queries/topUpvoted.sql

select top 1 
    *,
    JSON_QUERY(json_metadata, '$.tags')
from 
   Comments (NOLOCK)
where 
   title <> '' and
   parent_author = '' and
   category = 'philippines' and
   datediff(hour, created, GETDATE()) between 0 and 24
order by 
   net_votes desc

This query returns the top 1 most upvoted post with the #philippines tag(first tag of the post) and gets the posts from 24 hours before.

Top Rewards - /queries/topRewards.sql

select top 1 
    *,
    JSON_QUERY(json_metadata, '$.tags')
from 
   Comments (NOLOCK)
where 
   title <> '' and
   parent_author = '' and
   category = 'philippines' and
   datediff(hour, created, GETDATE()) between 0 and 24
order by 
   pending_payout_value desc

This query returns the top 1 post with the most pending_payout_value post with the #philippines tag(first tag of the post) and gets the posts from 24 hours before.

The purpose of creating this web application

• Data gathering - it will be a great way for newbie steemians to see what makes a top upvoted, commented, and a post with the highest pending payout value.
• Increase engagement to the top posts.
• Post visibility, the top posts will get more visibility to people who are visiting the web application.
• Featured posts may get more followers
• Another unique front-end for Steemit Posts
• Might help in curation efforts by using the data on the Web Application

Technology Stack

• node.js
• express.js
• bootstrap
• handlebars
• seriate for queries on STEEMSQL
• STEEMSQL - A monthly subscription is now needed to access the database.

Roadmap

For the future plans of this project, here are the features I am thinking of:

• Design a logo and the frontend, as of now it looks plain
• Convert the Web Design to HTML and integrate it to the web application
• Add a lightbox to the post, when clicked opens up a lightbox that can display the whole post where they can read it on the web application and not go directly to steemit.
• Add more data and featured items on the newspaper eg. number of words on the post etc.

How to contribute?

On the road map you can contribute to the items that I have listed, so I am also encouraging all people to contribute:

• Design a Logo for the web application.
• Design a newspaper look for the front-end of the web application.
• Suggest ideas for new features of the web application

You can find me on Steemit and Discord with the username: @vallesleoruther

Links

Web App: https://philippines-daily-express.herokuapp.com/

Github Repository: https://github.com/renegade47/philippines-daily-express

Github Proof

---

Posted on Utopian.io - Rewarding Open Source Contributors

---