PreviewPostQuery.php 3.44 KB
<?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;
        }
    }
}