Sort by value in FieldtypeOptions field

[DYI]

Hi. I have a repeater field which contains FieldtypeOptions field with 4 options. How can I sort results in find() by presence of the values?

# help_roles field options:
1=artist
2=leader
3=volunteer
4=sponsor

Lets assume I want to sort results by artists. Tried this one but it only filters records by value 1:

wire('pages')->find('template=repeater_requests, start=1, limit=20, sort=(help_roles=1)');

Any ideas?

[DYI]

I've analyzed queries made by framework and it looks impossible. Sad.

SELECT
  pages.id, pages.parent_id, pages.templates_id FROM `pages` WHERE (pages.templates_id = 100) AND (pages.status < 1024) AND (
     pages.id IN (
      SELECT pages.id FROM `pages` JOIN field_help_roles AS field_help_roles
      ON field_help_roles.pages_id = pages.id
      AND (((field_help_roles.data = '1'))) 
    )  OR pages.id IN (
      SELECT pages.id FROM `pages` JOIN field_help_roles AS field_help_roles
      ON field_help_roles.pages_id = pages.id
      AND (((field_help_roles.data = '2'))) 
    )  OR pages.id IN (
      SELECT pages.id FROM `pages` JOIN field_help_roles AS field_help_roles
      ON field_help_roles.pages_id = pages.id
      AND (((field_help_roles.data = '3'))) 
    )  OR pages.id IN (
      SELECT pages.id FROM `pages` JOIN field_help_roles AS field_help_roles
      ON field_help_roles.pages_id = pages.id
      AND (((field_help_roles.data = '4'))) 
    ) 
  ) GROUP BY pages.id ORDER BY pages.name;

 

[Robin S]

When you sort by "something" this means that "something" is the property you are sorting on, and the sorting will occur either ascending or descending by the value of the property. In cases where your sorting involves a database query such as $page->find() then the property you are sorting on needs to be a column in the database that the ORDER BY can apply to.

An example: if you have a field "fruit" then you can sort on it because it has an ascending or descending value in its database column: "apple, banana, cherry" or "wineberry, tangelo, strawberry". But you can't sort by "fruit=banana" because that isn't something that has a range of ascending or descending values and doesn't correspond to any single column in the database.

I understand what you're trying to do but this isn't something that can be accomplished by a single sort property - you have to do more of the work yourself and use more than one selector, merging the results together. The Find Merge module might allow you to do this in a simple way:

// Define selectors to merge results of
// You could also define any secondary sorting for each selector as needed, e.g. sort=title
$selectors = [
	'template=repeater_requests, help_roles=1', // These results will come first
	'template=repeater_requests, help_roles!=1', // These will be added after to make up the limit if needed
];
// Get results
// You can use "start" in the options argument if needed (see module readme)
$results = $pages->findMerge($selectors, 20);

 

[DYI]

35 minutes ago, Robin S said:

An example: if you have a field "fruit" then you can sort on it because it has an ascending or descending value in its database column: "apple, banana, cherry" or "wineberry, tangelo, strawberry". But you can't sort by because that isn't something that has a range of ascending or descending values and doesn't correspond to any single column in the database.

I'm sorry but you're wrong. I do can sort by "fruit=banana" in MySQL. "fruit=banana" gives you 1 or 0. Sorting by these two values works. I can rewrite query from the first post like this:

SELECT
  pages.id, pages.parent_id, pages.templates_id FROM `pages`
    LEFT JOIN field_help_roles AS field_help_roles1
      ON field_help_roles1.pages_id = pages.id
      AND field_help_roles1.data = '1'
 
   LEFT JOIN field_help_roles AS field_help_roles2
      ON field_help_roles2.pages_id = pages.id
      AND field_help_roles2.data = '2' 
      
  LEFT JOIN field_help_roles AS field_help_roles3
      ON field_help_roles3.pages_id = pages.id
      AND field_help_roles3.data = '3'
 
   LEFT JOIN field_help_roles AS field_help_roles4
      ON field_help_roles4.pages_id = pages.id
      AND field_help_roles4.data = '4'      
  WHERE (pages.templates_id = 100) AND (pages.status < 1024) 
			AND (NOT ISNULL(field_help_roles1.data) OR NOT ISNULL(field_help_roles2.data) OR NOT ISNULL(field_help_roles3.data) OR NOT ISNULL(field_help_roles4.data)) 
  GROUP BY pages.id ORDER BY field_help_roles2.data = '2';

The problem is PW makes queries via subselects.

[Robin S]

18 minutes ago, DYI said:

I do can sort by "fruit=banana" in MySQL.

Well if you are talking about writing your own SQL query that's fine and you're totally free to do that in PW. But your question related to the use of PageFinder selector strings, and I'm just trying to help you by explaining what values are valid for the "sort" keyword.

[gebeer]

On 11/19/2022 at 12:34 AM, DYI said:

Lets assume I want to sort results by artists. Tried this one but it only filters records by value 1:

wire('pages')->find('template=repeater_requests, start=1, limit=20, sort=(help_roles=1)');

Any ideas?

You are writing that you want to sort results by artist. But in your sort selector you try to sort by help_roles. Is it help_roles, you want to sort by? Or is it that you want to filter results by help_role "artist"? Please clarify.