1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
|
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
class CreateViewPopularArticles extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
DB::statement("
CREATE VIEW view_popular AS
SELECT
article_id as id,
article_id,
a.created_at,
a.updated_at,
a.url,
a.title,
a.excerpt_html,
sum(upvotes)+sum(comments) AS impact,
sum(upvotes) AS upvotes,
sum(comments) as comments,
count(article_id) AS repost
FROM
discussion as d
JOIN
article AS a ON d.article_id = a.id
GROUP BY
article_id,
a.created_at,
a.updated_at,
a.url,
a.title,
a.excerpt_html
HAVING (impact > 300) OR (count(article_id) > 2)
ORDER BY ((sum(upvotes)+sum(comments))*count(article_id)) desc, repost desc, impact desc"
);
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
DB::statement("DROP VIEW view_popular;");
}
}
|
