Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- class OfferWithStatsQueryBuilder
- {
- const DATE_FORMAT = 'Y-m-d';
- const COLUMN_OFFER_ID = 0;
- const COLUMN_KEYWORDS = 1;
- const COLUMN_STATS = 2;
- const COLUMN_ADURL_1 = 3;
- const COLUMN_GEO_1 = 4;
- const COLUMN_EPC = 5; // ?
- const COLUMN_ADURL_2 = 6;
- const COLUMN_GEO_2 = 7;
- private $offset;
- private $limit;
- private $keywords;
- private $dateFrom;
- private $dateTo;
- private $geos;
- /**
- * @var QueryBuilder
- */
- private $queryBuilder;
- /**
- * @var bool
- */
- private $isOrderable = false;
- /**
- * @var int @see self::COLUMN_* constants
- */
- private $orderColumn;
- /**
- * @var string 'desc' or 'asc'
- */
- private $orderDirection;
- /**
- * OfferWithStatsQueryBuilder constructor.
- * @param QueryBuilder $queryBuilder
- */
- public function __construct(QueryBuilder $queryBuilder)
- {
- $this->queryBuilder = $queryBuilder;
- }
- /**
- * @param mixed $offset
- */
- public function setOffset($offset)
- {
- $this->offset = $offset;
- }
- /**
- * @param mixed $limit
- */
- public function setLimit($limit)
- {
- $this->limit = $limit;
- }
- /**
- * @param array $keywords
- */
- public function setKeywords($keywords = null)
- {
- $this->keywords = null;
- if (!empty($keywords) && is_array($keywords)) {
- $this->keywords = array_map(function ($id) {
- return (int) $id;
- }, $keywords);
- }
- }
- /**
- * @param mixed $dateFrom
- */
- public function setDateFrom($dateFrom = null)
- {
- if (!empty($dateFrom)) {
- $dateFrom = new \DateTime($dateFrom);
- $dateFrom = $dateFrom->format(self::DATE_FORMAT);
- }
- $this->dateFrom = $dateFrom;
- }
- /**
- * @param mixed $dateTo
- */
- public function setDateTo($dateTo = null)
- {
- if (!empty($dateTo)) {
- $dateTo = new \DateTime($dateTo);
- $dateTo = $dateTo->format(self::DATE_FORMAT);
- }
- $this->dateTo = $dateTo;
- }
- /**
- * @param array $geos
- */
- public function setGeos($geos = null)
- {
- $this->geos = null;
- if (!empty($geos) && is_array($geos)) {
- $this->geos = array_map(function ($geo) {
- return (int) $geo;
- }, $geos);
- }
- }
- /**
- * @param mixed $order
- */
- public function setOrder($order = null)
- {
- $isOrderable = false;
- if (!empty($order) && is_array($order)) {
- $this->orderDirection = $order[0]['dir'] == 'desc' ? 'desc' : 'asc';
- $this->orderColumn = (int) $order[0]['column'];
- $isOrderable = true;
- }
- $this->isOrderable = $isOrderable;
- }
- public function buildQueryBuilder()
- {
- $select = 'lo.id AS offer, la.id AS adurl, la.url as url, ls.stats, lolkr.keywords, lacr.countries';
- $sql = $this->build($select);
- $this->appendSorting($sql);
- return $sql;
- }
- public function buildQueryBuilderForCount()
- {
- $select = 'COUNT(DISTINCT lo.id) as count';
- $notLimitQuery = false;
- $sql = $this->build($select, $notLimitQuery);
- return $sql;
- }
- private function toSubQuerySelect(QueryBuilder $query)
- {
- return '(' . $query->getSQL() . ')';
- }
- private function build($select = '*', $isLimited = true)
- {
- $main = clone $this->queryBuilder;
- $offerSubSelect = $this->getOfferSubQuery($isLimited);
- $statJoinSubSelect = $this->getStatJoinSubQuery();
- $keywordsSubSelect = $this->getKeywordsJoinSubQuery();
- $geoSubSelect = $this->getGeoJoinSubQuery();
- $main
- ->select($select)
- ->from($this->toSubQuerySelect($offerSubSelect), 'lo')
- ->innerJoin('lo', 'leadgen_adurls', 'la', 'lo.id = la.offer_id')
- ->leftJoin('lo', $this->toSubQuerySelect($statJoinSubSelect), 'ls', 'la.id = ls.url_id')
- ->leftJoin('lo', $this->toSubQuerySelect($keywordsSubSelect), 'lolkr', 'lo.id = lolkr.offer_id')
- ->leftJoin('lo', $this->toSubQuerySelect($geoSubSelect), 'lacr', 'la.id = lacr.leadgen_adurl_id');
- $this->appendConditions($main);
- // dd($main->getSQL());
- return $main;
- return '
- SELECT *
- FROM leadgen_offers lo
- LEFT JOIN leadgen_adurls la ON lo.id = la.offer_id
- -- STATS
- INNER JOIN (
- SELECT url_id, SUM(count) as stats
- FROM leadgen_stats
- GROUP BY url_id
- ) as ls ON la.id = ls.url_id
- -- KEYWORDS
- LEFT JOIN (
- SELECT offer_id, GROUP_CONCAT(keyword_id SEPARATOR \', \') as keywords
- FROM leadgen_offers_leadgen_keywords_rel
- GROUP BY offer_id
- ) as lolkr ON lo.id = lolkr.offer_id
- -- GEO
- LEFT JOIN (
- SELECT leadgen_adurl_id, GROUP_CONCAT(leadgen_country_id SEPARATOR \', \') as countries
- FROM leadgen_adurls_countries_rel
- GROUP BY leadgen_adurl_id
- ) as lacr ON la.id = lacr.leadgen_adurl_id
- GROUP BY lo.id, la.id
- ';
- }
- /**
- * @return QueryBuilder
- */
- private function getStatJoinSubQuery()
- {
- $statSubQuery = clone $this->queryBuilder;
- $statSubQuery
- ->select('url_id, SUM(count) AS stats')
- ->from('leadgen_stats')
- ->groupBy('url_id');
- if ($this->dateFrom) {
- // primary bind in STRING, when ::getSQL() doesNOT bind through ::setParameter()
- $statSubQuery->andWhere('date >= DATE(\'' . $this->dateFrom . '\')');
- }
- if ($this->dateTo) {
- $statSubQuery->andWhere('date <= DATE( \'' . $this->dateTo . '\')');
- }
- return $statSubQuery;
- }
- private function getKeywordsJoinSubQuery()
- {
- $keywordsSubQuery = clone $this->queryBuilder;
- $keywordsSubQuery
- ->select('offer_id, GROUP_CONCAT(keyword_id SEPARATOR \', \') as keywords')
- ->from('leadgen_offers_leadgen_keywords_rel')
- ->groupBy('offer_id');
- return $keywordsSubQuery;
- }
- private function getGeoJoinSubQuery()
- {
- $geoSubQuery = clone $this->queryBuilder;
- $geoSubQuery
- ->select('leadgen_adurl_id, GROUP_CONCAT(leadgen_country_id SEPARATOR \', \') as countries')
- ->from('leadgen_adurls_countries_rel')
- ->groupBy('leadgen_adurl_id');
- return $geoSubQuery;
- }
- /**
- * @param QueryBuilder $query
- * @return QueryBuilder
- */
- private function appendConditions(QueryBuilder $query)
- {
- if (!empty($this->geos)) {
- $joinTableAlias = 'geo_table_join';
- $query
- ->innerJoin('lo', 'leadgen_adurls_countries_rel', $joinTableAlias, 'la.id = ' . $joinTableAlias . '.leadgen_adurl_id');
- $query
- ->andWhere($joinTableAlias . '.leadgen_country_id IN (' . implode(', ', $this->geos) . ')');
- }
- if (!empty($this->keywords)) {
- $joinTableAlias = 'keywords_table_join';
- $query
- ->innerJoin('lo', 'leadgen_offers_leadgen_keywords_rel', $joinTableAlias, 'lo.id = ' . $joinTableAlias . '.lolkr.offer_id');
- $query
- ->andWhere(
- $joinTableAlias . '.keyword_id IN (' . implode(', ', $this->keywords) . ')'
- );
- }
- return $query;
- }
- /**
- * @param QueryBuilder $queryBuilder
- * @return QueryBuilder
- */
- private function appendSorting(QueryBuilder $queryBuilder)
- {
- if ($this->isOrderable) {
- switch ($this->orderColumn) {
- case self::COLUMN_KEYWORDS:
- $column = 'keywords';
- break;
- case self::COLUMN_ADURL_1:
- case self::COLUMN_ADURL_2:
- $column = 'url';
- break;
- case self::COLUMN_STATS:
- $column = 'stats';
- break;
- case self::COLUMN_GEO_1:
- case self::COLUMN_GEO_2:
- $column = 'stats';
- break;
- default: // self::COLUMN_OFFER_ID:
- $column = 'offer';
- break;
- }
- $queryBuilder->orderBy($column, $this->orderDirection);
- }
- return $queryBuilder;
- }
- /**
- * @param bool $isLimited
- * @return QueryBuilder
- */
- private function getOfferSubQuery(bool $isLimited)
- {
- $offerSubQuery = clone $this->queryBuilder;
- $offerSubQuery
- ->select('*')
- ->from('leadgen_offers');
- if ($isLimited) {
- if ($this->limit) {
- $offerSubQuery->setMaxResults((int) $this->limit);
- }
- if ($this->offset) {
- $offerSubQuery->setFirstResult((int) $this->offset);
- }
- }
- return $offerSubQuery;
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement