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;
    }
Prepares and executes a database query for fetching media objects
@param interger $objectId
@param string $objectURI
@return object Database resultset