src/Model/Media/Content.php
public function getMediaObjectsList($objectType = 'media', $objectURI = NULL, $objectId = NULL) {
        //Join Query
        //$objectType = 'media';

        $query = "SELECT o.object_id, o.object_uri, o.object_type, o.object_created_on, o.object_updated_on, o.object_status";
        //If we are querying for attributes
        $_properties = $this->getPropertyModel();
        $properties = array_keys((array) $_properties);

        $count = count($properties);
        if (!empty($properties) || $count < 1):
            //Loop through the attributes you need
            $i = 0;
            $query .= ",";
            foreach ($properties as $alias => $attribute):
                $alias = (is_int($alias)) ? $attribute : $alias;
                $query .= "\nMAX(IF(p.property_name = '{$attribute}', v.value_data, null)) AS {$alias}";
                if ($i + 1 < $count):
                    $query .= ",";
                    $i++;
                endif;
            endforeach;

            //Join the UserObjects Properties
            $_actorProperties = $this->user->getPropertyModel();
            $actorProperties = array_diff(array_keys($_actorProperties), array("user_password", "user_api_key", "user_email"));
            $count = count($actorProperties);
            if (!empty($actorProperties) || $count < 1):
                $query .= ","; //after the last media property   
                $i = 0;
                foreach ($actorProperties as $alias => $attribute):
                    $alias = (is_int($alias)) ? $attribute : $alias;
                    $query .= "\nMAX(IF(l.property_name = '{$attribute}', u.value_data, null)) AS {$alias}";
                    if ($i + 1 < $count):
                        $query .= ",";
                        $i++;
                    endif;
                endforeach;
            endif;

            //Get subitems count correlated subquery;
            $query .= ",\n(SELECT SUM(total) FROM"
                    . "\n\t(SELECT COUNT(DISTINCT so.object_id) as total,"
                    . "\n\tMAX(IF(sp.property_name = 'media_target', sv.value_data, null)) AS media_target"
                    . "\n\tFROM ?{$this->valueGroup}property_values sv"
                    . "\n\tLEFT JOIN ?properties sp ON sp.property_id = sv.property_id"
                    . "\n\tLEFT JOIN ?objects so ON so.object_id=sv.object_id"
                    . "\n\tWHERE  so.object_type='media'"
                    . "\n\tGROUP BY so.object_id) AS target_counter"
                    . "\nWHERE media_target=o.object_uri) AS media_target_count";

            //The data Joins
            $query .= "\nFROM ?{$this->valueGroup}property_values v"
                    . "\nLEFT JOIN ?properties p ON p.property_id = v.property_id"
                    . "\nLEFT JOIN ?objects o ON o.object_id=v.object_id"
                    //Join the OwnerObjects Properties tables on userid=actorid
                    . "\nLEFT JOIN ?objects q ON q.object_uri=v.value_data AND p.property_name ='media_owner'"
                    . "\nLEFT JOIN ?user_property_values u ON u.object_id=q.object_id"
                    . "\nLEFT JOIN ?properties l ON l.property_id = u.property_id"
            ;

        else:
            $query .="\nFROM ?objects";
        endif;

        $withConditions = false;

        if (!empty($objectId) || !empty($objectURI) || !empty($objectType)):
            $query .="\nWHERE";
            if (!empty($objectType)):
                $query .= "\to.object_type='{$objectType}'";
                $withConditions = TRUE;
            endif;
            if (!empty($objectURI)):
                $query .= ($withConditions) ? "\t AND" : "";
                $query .= "\to.object_uri='{$objectURI}'";
                $withConditions = TRUE;
            endif;
            if (!empty($objectId)):
                $query .= ($withConditions) ? "\t AND \t" : "";
                $query .= "\to.object_id='{$objectId}'";
                $withConditions = TRUE;
            endif;
        endif;

        $query .="\nGROUP BY o.object_id";
        $query .= $this->getListLookUpConditionsClause();
        $query .= $this->getListOrderByStatement();
        $query .= $this->getLimitClause();

        $total = $this->getObjectsListCount($objectType, $properties, $objectURI, $objectId); //Count first
        $results = $this->database->prepare($query)->execute();

        //ALWAYS RESET;
        $this->resetListLookUpConditions();
        $this->setListTotal($total);

        return $results;
    }
public function getMediaObjectsList($objectType = 'media', $objectURI = NULL, $objectId = NULL) {
        //Join Query
        //$objectType = 'media';

        $query = "SELECT o.object_id, o.object_uri, o.object_type, o.object_created_on, o.object_updated_on, o.object_status";
        //If we are querying for attributes
        $_properties = $this->getPropertyModel();
        $properties = array_keys((array) $_properties);

        $count = count($properties);
        if (!empty($properties) || $count < 1):
            //Loop through the attributes you need
            $i = 0;
            $query .= ",";
            foreach ($properties as $alias => $attribute):
                $alias = (is_int($alias)) ? $attribute : $alias;
                $query .= "\nMAX(IF(p.property_name = '{$attribute}', v.value_data, null)) AS {$alias}";
                if ($i + 1 < $count):
                    $query .= ",";
                    $i++;
                endif;
            endforeach;

            //Join the UserObjects Properties
            $_actorProperties = $this->user->getPropertyModel();
            $actorProperties = array_diff(array_keys($_actorProperties), array("user_password", "user_api_key", "user_email"));
            $count = count($actorProperties);
            if (!empty($actorProperties) || $count < 1):
                $query .= ","; //after the last media property   
                $i = 0;
                foreach ($actorProperties as $alias => $attribute):
                    $alias = (is_int($alias)) ? $attribute : $alias;
                    $query .= "\nMAX(IF(l.property_name = '{$attribute}', u.value_data, null)) AS {$alias}";
                    if ($i + 1 < $count):
                        $query .= ",";
                        $i++;
                    endif;
                endforeach;
            endif;

            //Get subitems count correlated subquery;
            $query .= ",\n(SELECT SUM(total) FROM"
                    . "\n\t(SELECT COUNT(DISTINCT so.object_id) as total,"
                    . "\n\tMAX(IF(sp.property_name = 'media_target', sv.value_data, null)) AS media_target"
                    . "\n\tFROM ?{$this->valueGroup}property_values sv"
                    . "\n\tLEFT JOIN ?properties sp ON sp.property_id = sv.property_id"
                    . "\n\tLEFT JOIN ?objects so ON so.object_id=sv.object_id"
                    . "\n\tWHERE  so.object_type='media'"
                    . "\n\tGROUP BY so.object_id) AS target_counter"
                    . "\nWHERE media_target=o.object_uri) AS media_target_count";

            //The data Joins
            $query .= "\nFROM ?{$this->valueGroup}property_values v"
                    . "\nLEFT JOIN ?properties p ON p.property_id = v.property_id"
                    . "\nLEFT JOIN ?objects o ON o.object_id=v.object_id"
                    //Join the OwnerObjects Properties tables on userid=actorid
                    . "\nLEFT JOIN ?objects q ON q.object_uri=v.value_data AND p.property_name ='media_owner'"
                    . "\nLEFT JOIN ?user_property_values u ON u.object_id=q.object_id"
                    . "\nLEFT JOIN ?properties l ON l.property_id = u.property_id"
            ;

        else:
            $query .="\nFROM ?objects";
        endif;

        $withConditions = false;

        if (!empty($objectId) || !empty($objectURI) || !empty($objectType)):
            $query .="\nWHERE";
            if (!empty($objectType)):
                $query .= "\to.object_type='{$objectType}'";
                $withConditions = TRUE;
            endif;
            if (!empty($objectURI)):
                $query .= ($withConditions) ? "\t AND" : "";
                $query .= "\to.object_uri='{$objectURI}'";
                $withConditions = TRUE;
            endif;
            if (!empty($objectId)):
                $query .= ($withConditions) ? "\t AND \t" : "";
                $query .= "\to.object_id='{$objectId}'";
                $withConditions = TRUE;
            endif;
        endif;

        $query .="\nGROUP BY o.object_id";
        $query .= $this->getListLookUpConditionsClause();
        $query .= $this->getListOrderByStatement();
        $query .= $this->getLimitClause();

        $total = $this->getObjectsListCount($objectType, $properties, $objectURI, $objectId); //Count first
        $results = $this->database->prepare($query)->execute();

        //ALWAYS RESET;
        $this->resetListLookUpConditions();
        $this->setListTotal($total);

        return $results;
    }
public function getMediaObjectsList ( $ objectType = 'media' , $ objectURI = NULL , $ objectId = NULL ) { //Join Query //$objectType = 'media'; $ query = "SELECT o.object_id, o.object_uri, o.object_type, o.object_created_on, o.object_updated_on, o.object_status" ; //If we are querying for attributes $ _properties = $ this -> getPropertyModel ( ) ; $ properties = array_keys ( ( array ) $ _properties ) ; $ count = count ( $ properties ) ; if ( ! empty ( $ properties ) || $ count < 1 ) : //Loop through the attributes you need $ i = 0 ; $ query .= "," ; foreach ( $ properties as $ alias => $ attribute ) : $ alias = ( is_int ( $ alias ) ) ? $ attribute : $ alias ; $ query .= "\nMAX(IF(p.property_name = '{$attribute}', v.value_data, null)) AS {$alias}" ; if ( $ i + 1 < $ count ) : $ query .= "," ; $ i ++ ; endif ; endforeach ; //Join the UserObjects Properties $ _actorProperties = $ this -> user -> getPropertyModel ( ) ; $ actorProperties = array_diff ( array_keys ( $ _actorProperties ) , array ( "user_password" , "user_api_key" , "user_email" ) ) ; $ count = count ( $ actorProperties ) ; if ( ! empty ( $ actorProperties ) || $ count < 1 ) : $ query .= "," ; //after the last media property    $ i = 0 ; foreach ( $ actorProperties as $ alias => $ attribute ) : $ alias = ( is_int ( $ alias ) ) ? $ attribute : $ alias ; $ query .= "\nMAX(IF(l.property_name = '{$attribute}', u.value_data, null)) AS {$alias}" ; if ( $ i + 1 < $ count ) : $ query .= "," ; $ i ++ ; endif ; endforeach ; endif ; //Get subitems count correlated subquery; $ query .= ",\n(SELECT SUM(total) FROM" . "\n\t(SELECT COUNT(DISTINCT so.object_id) as total," . "\n\tMAX(IF(sp.property_name = 'media_target', sv.value_data, null)) AS media_target" . "\n\tFROM ?{$this->valueGroup}property_values sv" . "\n\tLEFT JOIN ?properties sp ON sp.property_id = sv.property_id" . "\n\tLEFT JOIN ?objects so ON so.object_id=sv.object_id" . "\n\tWHERE  so.object_type='media'" . "\n\tGROUP BY so.object_id) AS target_counter" . "\nWHERE media_target=o.object_uri) AS media_target_count" ; //The data Joins $ query .= "\nFROM ?{$this->valueGroup}property_values v" . "\nLEFT JOIN ?properties p ON p.property_id = v.property_id" . "\nLEFT JOIN ?objects o ON o.object_id=v.object_id" //Join the OwnerObjects Properties tables on userid=actorid . "\nLEFT JOIN ?objects q ON q.object_uri=v.value_data AND p.property_name ='media_owner'" . "\nLEFT JOIN ?user_property_values u ON u.object_id=q.object_id" . "\nLEFT JOIN ?properties l ON l.property_id = u.property_id" ; else : $ query .= "\nFROM ?objects" ; endif ; $ withConditions = false ; if ( ! empty ( $ objectId ) || ! empty ( $ objectURI ) || ! empty ( $ objectType ) ) : $ query .= "\nWHERE" ; if ( ! empty ( $ objectType ) ) : $ query .= "\to.object_type='{$objectType}'" ; $ withConditions = TRUE ; endif ; if ( ! empty ( $ objectURI ) ) : $ query .= ( $ withConditions ) ? "\t AND" : "" ; $ query .= "\to.object_uri='{$objectURI}'" ; $ withConditions = TRUE ; endif ; if ( ! empty ( $ objectId ) ) : $ query .= ( $ withConditions ) ? "\t AND \t" : "" ; $ query .= "\to.object_id='{$objectId}'" ; $ withConditions = TRUE ; endif ; endif ; $ query .= "\nGROUP BY o.object_id" ; $ query .= $ this -> getListLookUpConditionsClause ( ) ; $ query .= $ this -> getListOrderByStatement ( ) ; $ query .= $ this -> getLimitClause ( ) ; $ total = $ this -> getObjectsListCount ( $ objectType , $ properties , $ objectURI , $ objectId ) ; //Count first $ results = $ this -> database -> prepare ( $ query ) -> execute ( ) ; //ALWAYS RESET; $ this -> resetListLookUpConditions ( ) ; $ this -> setListTotal ( $ total ) ; return $ results ; }
Prepares and executes a database query for fetching media objects
@param interger $objectId
@param string $objectURI
@return object Database resultset
Prepares and executes a database query for fetching media objects