SqWA News

Post Header

Published:
2021-05-01 11:33:39 -0400
Tags:

Just so there's a level playing field for the monthly SquidgeWorld contest, here's the post that holds all of the details.

* Once a month on the first day of the month, the system will run an automatic job that queries the system for the previous month. (Queries are at the bottom of this post)
* The queries will pull three winners - One registered user that posted a story in the previous month, one commenter (registered or anonymous) that posted a comment on any story, and the person who wrote the story that was commented on.
* If the person drawn won the previous month's contest, they are not eligible. So if user 'GENSLASHFEM' won on February 1st for a comment they left on January 20th, when the contest is drawn on March 1st for February, user 'GENSLASHFEM' isn't eligible. Basically, you're only eligible every other month. Questions?
* Users must have a valid email address in order to get their prize.
* Prizes are a (US) $10 gift certificate to Amazon, Apple, or Google Play. Please note: If you are in the United States, the gift certificates are not an issue. However, if you are outside of the United States, sometimes I cannot purchase a gift card; for example, a gift card purchased on Amazon.com will notwork on Amazon.com.au for some reason. In this case, you can ask for the $10 be donated to the charity of your choice.
* When it comes to winners of the comments contest, users can be registered or unregistered (leave an anonymous comment); the system queries for both at the same time and randomizes the results.
* Winners have until the last day of the month they won to respond and claim their win!
* Fics must be added during the previous month and not be backdated beyond the first of that month in order to qualify. For example, a fic added on March 27th, 2021 and backdated to March 2nd, 2021 would qualify, as would a fic added on March 27th, 2021 and not backdated. But a fic added on March 27th, 2021 and backdated to January 20th, 2021 would not be eligible if drawn.

 

Geeky details
Query to get the random story:

select w.id, w.created_at, w.title, p.name, u.email
from works w, pseuds p, users u, creatorships cr
where w.created_at between date_format(now() - INTERVAL 1 MONTH, '%Y-%m-01 00:00:00')
and date_format(LAST_DAY(now() - INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59')
and w.id = cr.creation_id
and cr.creation_type = 'Work'
and cr.pseud_id = p.id
and p.user_id = u.id
order by rand()
limit 6;

Query to get the comment winner:

select cm.created_at, cm.pseud_id, psc.name, usc.email, cm.comment_content, cha.work_id, w.title, pwa.name, usa.email
from comments cm, pseuds psc, users usc, chapters cha, works w, creatorships cr,
pseuds pwa, users usa
where cm.created_at between date_format(now() - INTERVAL 1 MONTH, '%Y-%m-01 00:00:00')
and date_format(LAST_DAY(now() - INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59')
and cm.pseud_id is not NULL
and cm.pseud_id = psc.id
and psc.user_id = usc.id
and cm.parent_type = 'Chapter'
and cm.parent_id = cha.id
and cha.work_id = w.id
and w.id = cr.creation_id
and cr.creation_type = 'Work'
and cr.pseud_id = pwa.id
and pwa.user_id = usa.id
union
select cm.created_at, 0, cm.name, cm.email, cm.comment_content, cha.work_id, w.title, pwa.name, usa.email
from comments cm, chapters cha, works w, creatorships cr,
pseuds pwa, users usa
where cm.created_at between date_format(now() - INTERVAL 1 MONTH, '%Y-%m-01 00:00:00')
and date_format(LAST_DAY(now() - INTERVAL 1 MONTH), '%Y-%m-%d 23:59:59')
and cm.pseud_id is NULL
and cm.parent_type = 'Chapter'
and cm.parent_id = cha.id
and cha.work_id = w.id
and w.id = cr.creation_id
and cr.creation_type = 'Work'
and cr.pseud_id = pwa.id
and pwa.user_id = usa.id
order by rand()
limit 6;