Fileset ordering progress: success so far (Concrete 5.5.2.1)
Permalink
Good day, developers.
I hadn't found a solution yet so here's a lead if anyone finds it useful or can point out some flaws. There must be a neater solution out there!
File set ordering wasn't working accurately. It would pull the correct images, and the table had the correct ordering for each fsfID, but the query was not accurate. Following is the query that the file_list model creates (for fsID 16):
After closer scrutiny, it turns out the last bit:
is only accurate if the file isn't already in another set. If it is, the record that is pulled MAY be the row from FileSetFiles for that file from another file set, depending on the ordering.
I couldn't figure out any elegant SQL to solve the issue. Instead, I added a field to the query to expand the results and used some iteration in the code later on to select only the file sets that are being filtered.
In /concrete/models/file_list.php:
STEP 1
About line 187: add the field fsID (as long as file sets are being filtered) to the base query results. This may increase (and duplicate) the fID rows that are returned.
STEP 2
At about line 261: use the following modified function to only return files that meet the file set criteria. If you don't use this bit, you may get duplicate files being returned.
Feedback is welcome!
I hadn't found a solution yet so here's a lead if anyone finds it useful or can point out some flaws. There must be a neater solution out there!
File set ordering wasn't working accurately. It would pull the correct images, and the table had the correct ordering for each fsfID, but the query was not accurate. Following is the query that the file_list model creates (for fsID 16):
SELECT DISTINCT f.fID, u.uName as fvAuthorName FROM Files f INNER JOIN FileVersions fv ON f.fID = fv.fID LEFT JOIN Users u on u.uID = fv.fvAuthorUID left join FileSearchIndexAttributes on (fv.fID = FileSearchIndexAttributes.fID) left join FileSetFiles fsfl on fsfl.fID = f.fID where 1=1 and fvIsApproved = '1' and f.fID IN (SELECT DISTINCT fID FROM FileSetFiles WHERE fsID = '16') order by fsDisplayOrder asc, f.fID asc
After closer scrutiny, it turns out the last bit:
FileSetFiles fsfl on fsfl.fID = f.fID where 1=1 and fvIsApproved = '1' and f.fID IN (SELECT DISTINCT fID FROM FileSetFiles WHERE fsID = '16')
is only accurate if the file isn't already in another set. If it is, the record that is pulled MAY be the row from FileSetFiles for that file from another file set, depending on the ordering.
I couldn't figure out any elegant SQL to solve the issue. Instead, I added a field to the query to expand the results and used some iteration in the code later on to select only the file sets that are being filtered.
In /concrete/models/file_list.php:
STEP 1
About line 187: add the field fsID (as long as file sets are being filtered) to the base query results. This may increase (and duplicate) the fID rows that are returned.
protected function setBaseQuery() { $this->setQuery('SELECT DISTINCT f.fID, u.uName as fvAuthorName'.(count($this->filteredFileSetIDs)?', fsID':'').' FROM Files f INNER JOIN FileVersions fv ON f.fID = fv.fID LEFT JOIN Users u on u.uID = fv.fvAuthorUID '); }
STEP 2
At about line 261: use the following modified function to only return files that meet the file set criteria. If you don't use this bit, you may get duplicate files being returned.
public function get($itemsToGet = 0, $offset = 0) { $files = array(); Loader::model('file'); $this->createQuery(); $r = parent::get($itemsToGet, $offset); $fsFilterList=$this->filteredFileSetIDs; $returnedFiles=array(); foreach($r as $row) { $f = File::getByID($row['fID']); if(count($fsFilterList)>0){ foreach($fsFilterList as $fsFilter){ if($row['fsID']==$fsFilter){ if(in_array($row['fID'],$returnedFiles)){ continue; } else {
Viewing 15 lines of 26 lines. View entire code block.
Feedback is welcome!
I checked this out and you are correct, the current version does not correctly keep track of the file set ordering.
Can you put this up as a fork on github, or a pull request if you feel confident in the changes?