1

I have a problem that i cant solve with a SQL query for MySQL.

First table "content"

ID / Title / Author / Curated / Created

Next table "whitelist"

ID / Author / Weight

What i want the SQL to do is SELECT all content WHERE content.created >= UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 14 day)) Also i what to order the Author random of the weight so the author with a high weight will show up more than a lower. As for now i am using this function ORDER BY -LOG(RAND(1337)) / whitelist.weight ASC. And finally i want every other row to be curated and the next one not.

So the result would be something like this.

ID  Title       Author      Curated     Created
3   My Home1    Krister     1           2015-01-20
13  My Home14   Krister     0           2015-01-20
33  My Home8    Eva         1           2015-01-15
34  My Home11   Krister     0           2015-01-01
43  My Home18   Eva         1           2015-01-01

What i have tried...

SELECT *
FROM (
    SELECT `content`.*, IF(`content`.`curated`=0, @mr:=@mr+1, @fr:=@fr+1) AS cur
    FROM `content` INNER JOIN `whitelist` ON `content`.`author` = `whitelist`.`author` , (SELECT @mr:=0, @fr:=0) initvars
    WHERE
        content.is_deleted = 0 
        AND content.created >= UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 14 day))
    ) tmp
INNER JOIN whitelist ON tmp.author = whitelist.author
ORDER BY cur ASC LIMIT 5 OFFSET 0;
Krister Johansson
  • 691
  • 2
  • 10
  • 34
  • "...order the Author random of the weight so the author with a [high] weight will show up more then a lower." That doesn't sound random. – Uueerdo Jul 23 '15 at 16:27
  • can you publish findings of these two : `show create table whitelist` and `show create table content` – Drew Jul 23 '15 at 16:34
  • i would go about it similar to my answer here: http://stackoverflow.com/a/31372568/1816093 where there it was lottery/raffle ticket purchases, yours is weight. there is a lot of programmer discretion naturally so many of the answers are opinion based. and I would attempt a mysql-only solution without php so it is fast. good luck ! – Drew Jul 23 '15 at 16:47
  • Here is the structur of the database. http://sqlfiddle.com/#!9/fe1d44 – Krister Johansson Jul 23 '15 at 17:40

1 Answers1

1

Try this:

SELECT * FROM content
JOIN whitelist ON content.Author = whitelist.Author
WHERE content.created >= UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 14 day))
ORDER BY (RAND() * (SELECT MAX(Weight) FROM whitelist)) + Weight DESC

Now with curated:

SET @pos1=0;
SET @pos2=0;

SELECT * FROM 
(
    SELECT *, @pos1 := @pos1 + 1 pos FROM
    (
        SELECT Content.* FROM content
        JOIN whitelist ON content.Author = whitelist.Author
        WHERE curated = 0
        AND content.created >= UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 14 day))
        ORDER BY (RAND() * (SELECT MAX(Weight) FROM whitelist)) + Weight 
    ) s1
    UNION
    SELECT *, @pos2 := @pos2 + 1 pos FROM
    (
        SELECT Content.* FROM content
        JOIN whitelist ON content.Author = whitelist.Author
        WHERE curated = 1
        AND content.created >= UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 14 day))
        ORDER BY (RAND() * (SELECT MAX(Weight) FROM whitelist)) + Weight 
    ) s2
) t1
ORDER BY pos DESC, Curated
vinz
  • 566
  • 3
  • 11