src/libraries/adapters/DatabaseMySql.php
/**
    * Build parts of the photos select query
    *
    * @param array $filters filters used to perform searches
    * @param int $limit number of records to have returned
    * @param offset $offset starting point for records
    * @return array
    */
Build parts of the photos select query
@param array $filters filters used to perform searches
@param int $limit number of records to have returned
@param offset $offset starting point for records
@return array
Build parts of the photos select query @param array $filters filters used to perform searches @param int $limit number of records to have returned @param offset $offset starting point for records @return array
private function buildQuery($filters, $limit, $offset, $table)
  {
    $from = "FROM `{$this->mySqlTablePrefix}{$table}` ";
    $where = "WHERE `{$this->mySqlTablePrefix}{$table}`.`owner`='{$this->owner}'";
    $groupBy = '';
    $sortBy = 'ORDER BY dateSortByDay DESC';
    if(!empty($filters) && is_array($filters))
    {
      foreach($filters as $name => $value)
      {
        switch($name)
        {
          case 'album':
            $subquery = sprintf("`id` IN (SELECT element FROM `{$this->mySqlTablePrefix}elementAlbum` WHERE `{$this->mySqlTablePrefix}elementAlbum`.`owner`='%s' AND `type`='%s' AND `album`='%s')",
              $this->_($this->owner), 'photo', $value);
            $where = $this->buildWhere($where, $subquery);
            break;
          case 'hash':
            $hash = $this->_($value);
            $where = $this->buildWhere($where, "hash='{$hash}'");
            break;
          case 'ids':
            $ids = (array)explode(',', $value);
            foreach($ids as $k => $v)
              $ids[$k] = $this->_($v);
            $where = $this->buildWhere($where, sprintf("`id` IN ('%s')", implode("','", $ids)));
            break;
          case 'groups':
            if(!is_array($value))
              $value = (array)explode(',', $value);
            foreach($value as $k => $v)
              $value[$k] = $this->_($v);
            $subquery = sprintf("(`id` IN (SELECT element FROM `{$this->mySqlTablePrefix}elementGroup` WHERE `{$this->mySqlTablePrefix}elementGroup`.`owner`='%s' AND `type`='%s' AND `group` IN('%s')) OR permission='1')",
              $this->_($this->owner), 'photo', implode("','", $value));
            $where = $this->buildWhere($where, $subquery);
            break;
          case 'page':
            if($value > 1)
              $offset = intval(($limit * $value) - $limit);
            break;
          case 'permission':
            $where = $this->buildWhere($where, "`permission`='1'");
            break;
          case 'sortBy':
            if($value === 'dateTaken,desc')
              $sortBy = 'ORDER BY dateSortByDay DESC';
            elseif($value === 'dateTaken,asc')
              $sortBy = 'ORDER BY dateSortByDay ASC';
            elseif($value === 'dateUploaded,desc')
              $sortBy = 'ORDER BY dateSortByDay DESC, dateUploaded ASC';
            elseif($value === 'dateUploaded,asc')
              $sortBy = 'ORDER BY dateSortByDay ASC, dateUploaded ASC';
            else
              $sortBy = 'ORDER BY ' . $this->_(str_replace(',', ' ', $value));
            $field = $this->_(substr($value, 0, strpos($value, ',')));
            $where = $this->buildWhere($where, "{$field} is not null");
            break;
          case 'tags':
            if(!is_array($value))
              $value = (array)explode(',', $value);
            $tagCount = count($value);
            if($tagCount == 0)
              break;
            $ids = array();
            foreach($value as $k => $v)
            {
              $v = $value[$k] = $this->_($v);
              $thisRes = $this->db->all(sprintf("SELECT `element`, `tag` FROM `%selementTag` WHERE `owner`='%s' AND `type`='photo' AND `tag`='%s'", $this->mySqlTablePrefix, $this->owner, $v));
              foreach($thisRes as $t)
              {
                if(isset($ids[$t['element']]))
                  $ids[$t['element']]++;
                else
                  $ids[$t['element']] = 1;
              }
            }
            foreach($ids as $k => $cnt)
            {
              if($cnt < $tagCount)
                unset($ids[$k]);
            }
            $where = $this->buildWhere($where, sprintf("`%sphoto`.`id` IN('%s')", $this->mySqlTablePrefix, implode("','", array_keys($ids))));
            break;
          case 'type': 
            $value = $this->_($value);
            $where = $this->buildWhere($where, "`type`='{$value}'");
            break;
        }
      }
    }
    $limit_sql = '';
    if($limit)
      $limit_sql = "LIMIT {$limit}";
    $offset_sql = '';
    if($offset)
      $offset_sql = "OFFSET {$offset}";
    $ret = array('from' => $from, 'where' => $where, 'groupBy' => $groupBy, 'sortBy' => $sortBy, 'limit' => $limit_sql, 'offset' => $offset_sql);
    return $ret;
  }
private function buildQuery ( $ filters , $ limit , $ offset , $ table ) { $ from = " FROM ` { $ this -> mySqlTablePrefix } { $ table } `  " ; $ where = " WHERE ` { $ this -> mySqlTablePrefix } { $ table } `.`owner`=' { $ this -> owner } ' " ; $ groupBy = '' ; $ sortBy = 'ORDER BY dateSortByDay DESC' ; if ( ! empty ( $ filters ) && is_array ( $ filters ) ) { foreach ( $ filters as $ name => $ value ) { switch ( $ name ) { case 'album' : $ subquery = sprintf ( " `id` IN (SELECT element FROM ` { $ this -> mySqlTablePrefix } elementAlbum` WHERE ` { $ this -> mySqlTablePrefix } elementAlbum`.`owner`='%s' AND `type`='%s' AND `album`='%s') " , $ this -> _ ( $ this -> owner ) , 'photo' , $ value ) ; $ where = $ this -> buildWhere ( $ where , $ subquery ) ; break ; case 'hash' : $ hash = $ this -> _ ( $ value ) ; $ where = $ this -> buildWhere ( $ where , " hash=' { $ hash } ' " ) ; break ; case 'ids' : $ ids = ( array ) explode ( ',' , $ value ) ; foreach ( $ ids as $ k => $ v ) $ ids [ $ k ] = $ this -> _ ( $ v ) ; $ where = $ this -> buildWhere ( $ where , sprintf ( " `id` IN ('%s') " , implode ( " ',' " , $ ids ) ) ) ; break ; case 'groups' : if ( ! is_array ( $ value ) ) $ value = ( array ) explode ( ',' , $ value ) ; foreach ( $ value as $ k => $ v ) $ value [ $ k ] = $ this -> _ ( $ v ) ; $ subquery = sprintf ( " (`id` IN (SELECT element FROM ` { $ this -> mySqlTablePrefix } elementGroup` WHERE ` { $ this -> mySqlTablePrefix } elementGroup`.`owner`='%s' AND `type`='%s' AND `group` IN('%s')) OR permission='1') " , $ this -> _ ( $ this -> owner ) , 'photo' , implode ( " ',' " , $ value ) ) ; $ where = $ this -> buildWhere ( $ where , $ subquery ) ; break ; case 'page' : if ( $ value > 1 ) $ offset = intval ( ( $ limit * $ value ) - $ limit ) ; break ; case 'permission' : $ where = $ this -> buildWhere ( $ where , " `permission`='1' " ) ; break ; case 'sortBy' : if ( $ value === 'dateTaken,desc' ) $ sortBy = 'ORDER BY dateSortByDay DESC' ; elseif ( $ value === 'dateTaken,asc' ) $ sortBy = 'ORDER BY dateSortByDay ASC' ; elseif ( $ value === 'dateUploaded,desc' ) $ sortBy = 'ORDER BY dateSortByDay DESC, dateUploaded ASC' ; elseif ( $ value === 'dateUploaded,asc' ) $ sortBy = 'ORDER BY dateSortByDay ASC, dateUploaded ASC' ; else $ sortBy = 'ORDER BY ' . $ this -> _ ( str_replace ( ',' , ' ' , $ value ) ) ; $ field = $ this -> _ ( substr ( $ value , 0 , strpos ( $ value , ',' ) ) ) ; $ where = $ this -> buildWhere ( $ where , " { $ field }  is not null " ) ; break ; case 'tags' : if ( ! is_array ( $ value ) ) $ value = ( array ) explode ( ',' , $ value ) ; $ tagCount = count ( $ value ) ; if ( $ tagCount == 0 ) break ; $ ids = array ( ) ; foreach ( $ value as $ k => $ v ) { $ v = $ value [ $ k ] = $ this -> _ ( $ v ) ; $ thisRes = $ this -> db -> all ( sprintf ( " SELECT `element`, `tag` FROM `%selementTag` WHERE `owner`='%s' AND `type`='photo' AND `tag`='%s' " , $ this -> mySqlTablePrefix , $ this -> owner , $ v ) ) ; foreach ( $ thisRes as $ t ) { if ( isset ( $ ids [ $ t [ 'element' ] ] ) ) $ ids [ $ t [ 'element' ] ] ++ ; else $ ids [ $ t [ 'element' ] ] = 1 ; } } foreach ( $ ids as $ k => $ cnt ) { if ( $ cnt < $ tagCount ) unset ( $ ids [ $ k ] ) ; } $ where = $ this -> buildWhere ( $ where , sprintf ( " `%sphoto`.`id` IN('%s') " , $ this -> mySqlTablePrefix , implode ( " ',' " , array_keys ( $ ids ) ) ) ) ; break ; case 'type' : $ value = $ this -> _ ( $ value ) ; $ where = $ this -> buildWhere ( $ where , " `type`=' { $ value } ' " ) ; break ; } } } $ limit_sql = '' ; if ( $ limit ) $ limit_sql = " LIMIT  { $ limit } " ; $ offset_sql = '' ; if ( $ offset ) $ offset_sql = " OFFSET  { $ offset } " ; $ ret = array ( 'from' => $ from , 'where' => $ where , 'groupBy' => $ groupBy , 'sortBy' => $ sortBy , 'limit' => $ limit_sql , 'offset' => $ offset_sql ) ; return $ ret ; }
Build parts of the photos select query
 @param array $filters filters used to perform searches
 @param int $limit number of records to have returned
 @param offset $offset starting point for records
 @return array
Build parts of the photos select query @param array $filters filters used to perform searches @param int $limit number of records to have returned @param offset $offset starting point for records @return array
// TODO: support logic for multiple conditions // type for activity