Forums

mysql error using: sortByFileSetDisplayOrder()

Permalink
Hi all,

Here's my code:

$fl = new FileList();
$fs = FileSet::getByName('Media Assets');
$fl->filterBySet($fs);
$fs = FileSet::getByName($collection[$i]);
$fl->filterBySet($fs);   
$fl->filterByExtension('pdf');
$fl->sortByFileSetDisplayOrder();
$pdfs = $fl->get();


It was working fine until I put in the $fl->sortByFileSetDisplayOrder();

Commenting out that line, it still works fine...

Here's the error I'm getting:
mysql error: [1052: Column 'fsDisplayOrder' in order clause is ambiguous] in EXECUTE("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 FileSetFiles fsf8 on fsf8.fID = f.fID left join FileSetFiles fsf4 on fsf4.fID = f.fID left join FileSearchIndexAttributes on (fv.fID = FileSearchIndexAttributes.fID) where 1=1 and fsf8.fsID = '8' and fsf4.fsID = '4' and fv.fvExtension = 'pdf' and fvIsApproved = '1' order by fsDisplayOrder asc, fID asc ")

Any thoughts?

Thanks...
-Bob
BigBobbyD
 
r1digital replied on at Permalink Reply
r1digital
This first line says that it's reading DisplayOrder as a column and not a function (I'm guessing your trying to use the function).

I'm not great with SQL but I know that in the pages module db.xml it has the sort info which you can copy out and use.

Hope this helps.
BigBobbyD replied on at Permalink Reply
BigBobbyD
The sortByFileSetDisplayOrder() is a method of the FileSet model, so it should work.

After testing a little more, it seems the ambiguity is coming from the fact that I'm filtering by fileset twice. If I remove one of these filters, the sort does work.

Any thoughts on how to get this to work with two filters? Or do I need to force them to use one fileset?
r1digital replied on at Permalink Reply
r1digital
I think if you add the filters in separately they should work, not sure if you can concate filters.

-----Original Message-----
From: Concrete5 Community [mailto:discussions@concretecms.com]
Sent: 19 April 2011 23:00
To: info@r1creative.co.uk
Subject: mysql error using: sortByFileSetDisplayOrder(): mysql error using: sortByFileSetDisplayOrder()
jordanlev replied on at Permalink Reply
jordanlev
I'm guessing that when you call the filters it's not actually doing the filtering then but rather just loading them up for when you "pull the trigger" with the call to $fl->get(). Because you're re-using the same variable in your code, when you make the second call to FileSet::GetByName(), you're actually retroactively changing the first file set that you passed into the prior filter (because those are complicated objects and not straightforward integer or string variables it is only passing references back and forth, not the data itself).

So... I think this should solve your problem:
$fl = new FileList();
$fs1 = FileSet::getByName('Media Assets');
$fl->filterBySet($fs1);
$fs2 = FileSet::getByName($collection[$i]);
$fl->filterBySet($fs2);   
$fl->filterByExtension('pdf');
$fl->sortByFileSetDisplayOrder();
$pdfs = $fl->get();
BigBobbyD replied on at Permalink Reply
BigBobbyD
Thanks, Jordan and R1.

Using separate variables for each FileSet variable throws the same error.

If I comment out the line that does the sorting, everything works as it should. The list gets filtered by one set, then the other, then by extension.

Also tried putting the sort above the filters with the same result.

I was hoping to be able to use two existing filesets to build the list, rather than add the creation of a new fileset to the workflow. In other words, the client is already flagging assets "Collection A", so if we have another fileset, "Media Assets" then we can show a subset of "Collection A".

But I suppose a simple solution would be to force the client to create a new FileSet, "Collection A Media" and just use the one flag.

This seems unnecessarily messy, however.

Any other ideas?
jordanlev replied on at Permalink Reply
jordanlev
Hmm... then I guess sorting a FileList when there's more than one fileset filter just doesn't work (you should file a bug about this).

You can specify a manual sort order, but you're going to have to choose one file set to come before the other:
$fl = new FileList();
$fs1 = FileSet::getByName('Media Assets');
$fl->filterBySet($fs1);
$fs2 = FileSet::getByName($collection[$i]);
$fl->filterBySet($fs2);   
$fl->filterByExtension('pdf');
$fs1Alias = 'fs'.$fs1->getFileSetID();
$fs2Alias = 'fs'.$fs2->getFileSetID();
$pl->sortByMultiple("{$fs1Alias}.fsDisplayOrder  ASC, {$fs2Alias}.fsDisplayOrder ASC");
$pdfs = $fl->get();


If you want to sort them all together you'd need to write your own UNION query (probably not worth the effort).

-Jordan
BigBobbyD replied on at Permalink Reply
BigBobbyD
Tried that approach, which is throwing a slightly different error:

"mysql error: [1054: Unknown column 'fs8.fsDisplayOrder' in 'order clause'] in EXECUTE("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 FileSetFiles fsf8 on fsf8.fID = f.fID left join FileSetFiles fsf4 on fsf4.fID = f.fID left join FileSearchIndexAttributes on (fv.fID = FileSearchIndexAttributes.fID) where 1=1 and fsf8.fsID = '8' and fsf4.fsID = '4' and fv.fvExtension = 'jpg' and fvIsApproved = '1' order by fs8.fsDisplayOrder ASC, fs4.fsDisplayOrder ASC ")"

I will file this as a bug, as you suggest, Jordan. In the meantime, I think I'm going to move on to brighter pastures and just use the single fileset, instead of the two.

Thanks for your generous help.
jordanlev replied on at Permalink Best Answer Reply
jordanlev
Oops, sorry there's a typo in my code. These 2 lines:
$fs1Alias = 'fs'.$fs1->getFileSetID();
$fs2Alias = 'fs'.$fs2->getFileSetID();

should be this instead:
$fs1Alias = 'fsf'.$fs1->getFileSetID();
$fs2Alias = 'fsf'.$fs2->getFileSetID();
BigBobbyD replied on at Permalink Reply
BigBobbyD
You the man!

Thanks.