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:
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