Blame view
app/Queries/PreviewPostQuery.php
3.44 KB
e77200db5
Initial commit
|
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;
}
}
}
|