Admin Photos/day SQL - Githubissues

DevGareth commented 5 years ago

Need SQL that will return the photos/day regardless of user.

Will write up my best guess in a minute.

Someone might already be doing this, not sure.

DevGareth commented 5 years ago

SELECT DATEPART(YEAR, Timestamp) AS 'Year', DATEPART(MONTH, Timestamp) AS 'Month', DATEPART(DAY, Timestamp) AS 'Day', COUNT(*) AS 'Uploads' FROM Photo WHERE Timestamp > [startdate] AND Timestamp < [enddate] GROUP BY DATEPART(DAY, Timestamp), DATEPART(MONTH, Timestamp), DATEPART(YEAR, Timestamp) ORDER BY 'Year', 'Month', 'Day'

Again, ref: https://travishorn.com/sql-query-for-counting-records-per-day-3936728c8bd

startdate and enddate should be given by user.

rahul-kothari commented 5 years ago

You can return photos/tags etc. regardless of user Use the API AdminQuery and in the json body leave userID as blank userID: ""

Here is a use guide I made for Matt:

api.moodportfolio/AdminQuery USE GUIDE: GET REQUEST SHOULD INCLUDE:

    'startDate' : ..
    'endDate' : .. 
    'city' : ..
    'country' : ..
    'userID' : ..
    'tagName': ..       
    'tagID'  : ..
}

Leave any blank (eg: 'userID':"") to query all instances of it (in the example - to run the query across all users.)

So if start and endDate, and userID are blank and you write city=Liverpool, it doesSelect * from Photos where city=Liverpool (i.e. runs through ALL dates and users.

DevGareth commented 5 years ago

Oh, that's fine then; just thought it would be faster to do the counting in the SQL query and just return the counts for each day.

rahul-kothari commented 5 years ago

you mean count the number of photos a user has posted in a day? Will need to create a new API for that

DevGareth commented 5 years ago

The overall number that all users combined have posted per day I think. Right, @MJHarding ?

rahul-kothari commented 5 years ago

Nvm wrote the query for that :) its in AdminPage.js

rahul-kothari commented 5 years ago

Also, created a API where you can run whatever the fuck query you want:

api.moodportfolio/SplAdminQuery GET REQUEST

{
    'basedOn' : "#users" or "popularTag" or "any"
    'splSQLQuery' : "select * from ..... "
}

#users -> Number of users registered popularTag -> Most popular tag any-> write your own query in "splSQLQuery" (else leave it blank)

eg- json body can be:

basedOn: "any"
splSQLQuery: SELECT Date(timestamp), count(photoID) FROM Photo GROUP BY DATE(timestamp)"

robertszafa / moodportfolio

Admin Photos/day SQL #43