Hello,
i’m trying to get next issue with a pdoPage call:
First all entries in the district “center” are shown and sorted by distance. Then all other entries which are not in the district “center” should be shown and sorted by distance.
With sql it would look something like this (i think): ORDER BY district = 'center' ASC, distance ASC
My try hasn’t worked yet:
[[!pdoPage?
&parents=`1`
&sortby=`{"district='center'":"ASC","distance":"ASC"}`
&tpl=`mytpl`
]]
Any idea?
Welcome to the Community! 
pdoPage is used to list resources (meaning actual pages under the resources tree). You can only use &sortby to sort by the respective resource fields of those resources, for example by pagetitle.
To sort your resources by something like a Distance, you would probably need to create a Template Variable for those resources and enter the distance value for each resource in there. Then you can add &includeTVs to your pdoPage call and reference your TV to be able to sort by it.
For further reference take a look at the pdoResources Doc, as most (if not all) properties apply to pdoPage as well. There you’ll also find more details on the &sortby and the &includeTVs property.
Thanks a lot!
I just see that my code is not complete, thanks for the hint. The TV has already been created and will be integrated as follows:
[[!pdoPage?
&parents=`1`
&includeTVs=`district,distance`
&sortby=`{"district='center'":"ASC","distance":"ASC"}`
&tpl=`mytpl`
]]
The simple distance-sorting also works perfectly. I just don’t know how to sort “center” by distance first and then the other entries.
I know the pdoResources documentation very well, but there is no answer to my question.
How many other districts than “center” do you have? If that’s an option for you, you could nest your resources like this:
center
off-center
And then simply call two instances of pdoPage, one referring to “center” and one to the “other” (off-center) resource.
Also I’m not sure if "district='center'":"ASC" is correct syntax. You might only want to use name : sorting
It’s about 100 resources and 20 districts. And the structure of the resources is already fixed and must not be changed.
I hoped that there would be a way to transfer the “ORDER BY” from SQL in a different spelling to “&sortby”.
I dont get an error with district='center'":"ASC" but it doesn’t work.
What does happen with your call? I’m guessing you get all 100 resources sorted by distance?
Some ideas – hopefully I won’t cause more harm than good:
I have never done this, but possibly changing the syntax to what is used by the filterby property might work:
"district==center":"ASC","distance":"ASC"
If that doesn’t work:
"district":"ASC","distance":"ASC"
should sort everything into groups by district first and then sort by distance within each district. You would only end up with the center district as the first grouping if it happens to be alphabetically first.
What kind of TV is district? Select list? Possibly you could assign a numerical value and sort on that rather than the spelling, and that way you could assign 1 to the center district.
If I were doing this with pdoresources I might just use 2 separate calls with filters. So the first call filters by equal to center, and the second call filters by NOT equal to center… But not sure how to wrap 2 pdoresources calls in one pdopage call. I bet it can be done though…
For what it’s worth, if each district is a separate checkbox TV, each with only a single checkbox, then you can just include that TV in the sortby (in this example “center” is a checkbox TV) :
"center":ASC,"distance":ASC
All resources with the “center” checkbox checked will display first. Probably not an efficient way to build the district TVs though.
Wow thank you! That seems to be working! But only with AND:
$sortby=‘{“district==center”:“ASC”,“AND:distance”:“ASC”}’
EDIT:
Oh no, you can’t do that. It was just by chance the right sorting 
I’ll test the other suggestions tomorrow.
I agree with @lucy about the two calls making more sense…but how to wrap them up into one…
And for some reason I see it working better with the outer ones established first, FWTW
The TV of the districts always has only one name “district” with the respective value “center”, “wandsbek” and so on.
With district=‘center’": “ASC” I get no values at all.
I tried the idea with the two call already, the problem is the pagination. The pagination cannot affect two calls.
@nuan88 what do you mean with FWTWW?
Try using the where parameter:
&where=`ORDER BY district = 'center' ASC, distance ASC`
or possibly
&where=`ORDER BY district = 'center', distance ASC`
Also if you surround code in your posts here with three backticks on separate lines above and below your code it will display the code properly. Easier for us to see what you are actually writing.
I may be confused here, but I think &sortby is looking for one or more simple field names, in which case asking to sort by district=‘center’ doesn’t make sense. You could sort by district if that’s what you want.
If you only want records where district=‘center’, that belongs in the &where clause, not &sortby.
I think they want two sortby rules, first using the district TV to display the resources with value of center first, then using the distance TV to sort everything else by distance ASC.
Right, but that’s not going to work unless ‘center’ comes first or last alphabetically, because &sortby will only sort numerically or alphabetically. One solution would be to change the district name to aCenter or 1Center. Another would be to have multiple queries, one for each district, and run them in the order you want. A third might be to use a snippet to get everything sorted by distance, then reorder the records using the district field to get things in the order you want.
Was hoping using an Ordered By clause in the where parameter would work…
I don’t think Ordered By will work in a &where property. I could be wrong. But even if it does, it works the same way &sortby does. It’s either a alphabetical or numeric sort on a single field.
Without a custom snippet, I think what is needed is multiple calls, one for each district. I do this at Bob’s Guides for the megamenu – a separate Wayfinder call for each main heading. I was unable to make it work any other way.
@bobray But the problem with separate calls is that the pagination [[!+page.nav]] does not work correctly. Or am I mistaken?
I think sortby also sorts on a boolean which is why you can use the value of a checkbox in the sort?
Using ORDERED BY in a mysql query you can specify a field value as one of the sorts, so this will work: ORDER BY district = 'center', distance ASC in mysql. I was hoping it would also work in a WHERE parameter but am just grasping.
Thanks anyway for your support. Then I have to think about another structure.
You can’t add an ORDER BY into a WHERE; the query builder doesn’t allow that.
I think this should work:
&sortby=`{"FIELD(district, \"center\")":"DESC", "distance":"ASC"}`
The sortby json syntax requires “name-of-field”:“direction” for each element.
(Related raw SQL explanation: https://stackoverflow.com/questions/14104055/ordering-by-specific-field-value-first)
I almost gave up hope, but markh saved my day!
Thats the way it works. Thank you so much!