PreviewPostQuery.php
3.44 KB
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
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
<?php
namespace FootyRoom\Queries;
use FootyRoom\Support\AutoMapper;
use Illuminate\Database\Connection;
use FootyRoom\Queries\Post\PreviewPost;
class PreviewPostQuery
{
/**
* @var \Illuminate\Database\Connection
*/
protected $mysql;
/**
* Constructor.
*
* @param \Illuminate\Database\Connection $mysql
*/
public function __construct(Connection $mysql)
{
$this->mysql = $mysql;
}
/**
* Common query wrapper for posts that takes subQuery for needed posts.
*
* @param string $subQuery
* @param array $params
*
* @return \FootyRoom\Queries\Post\PreviewPost[]
*/
protected function select($subQuery, $params = [])
{
$postDtos = $this->mysql
->select(
'SELECT
p.id,
p.match_id AS matchId,
DATE_FORMAT(p.post_date, "%Y-%m-%dT%TZ") AS date,
p.post_title as title,
p.post_name as slug,
p.comment_count as commentCount,
p.view_count as viewCount,
pm.meta_value AS thumbnailUrl,
t1.team_name as homeTeamName,
t2.team_name as awayTeamName
FROM ('.$subQuery.') as p
LEFT JOIN wp_postmeta AS pm ON pm.meta_key = "pft_widescreen" AND pm.post_id = p.id
JOIN teams AS t1 ON t1.team_id = p.home_team
JOIN teams AS t2 ON t2.team_id = p.away_team',
$params
);
if (!$postDtos) {
return [];
}
$posts = [];
foreach ($postDtos as $post) {
$posts[] = AutoMapper::map($post, PreviewPost::class);
}
return $posts;
}
/**
* Finds match posts by post slug.
*
* @param string $slug
*
* @return \FootyRoom\Queries\Post\PreviewPost|null
*/
public function findBySlug($slug)
{
$subQuery = $this->mysql
->table('wp_posts AS p')
->select('*')
->join('wp_postmeta AS pm', 'p.id', '=', 'pm.post_id')
->join('matches AS m', 'm.match_id', '=', 'pm.meta_value')
->where('p.post_status', '=', 'publish')
->where('p.post_type', '=', 'preview')
->where('pm.meta_key', '=', 'match_id_preview')
->where('p.post_name', '=', $slug);
$post = $this->select($subQuery->toSql(), $subQuery->getBindings());
if (isset($post[0])) {
return $post[0];
} else {
return null;
}
}
/**
* Finds match posts by post slug, year and month.
*
* @param string $slug
* @param string $year
* @param string $month
*
* @return \FootyRoom\Queries\Post\PreviewPost|null
*/
public function findBySlugYearMonth($slug, $year, $month)
{
$subQuery = $this->mysql
->table('wp_posts AS p')
->select('*')
->join('wp_postmeta AS pm', 'p.id', '=', 'pm.post_id')
->join('matches AS m', 'm.match_id', '=', 'pm.meta_value')
->where('p.post_status', '=', 'publish')
->where('pm.meta_key', '=', 'match_id_preview')
->where('p.post_name', '=', $slug)
->where($this->mysql->raw('YEAR(p.post_date)'), '=', $year)
->where($this->mysql->raw('MONTH(p.post_date)'), '=', $month);
$post = $this->select($subQuery->toSql(), $subQuery->getBindings());
if (isset($post[0])) {
return $post[0];
} else {
return null;
}
}
}