Custom sorting using a MySQL statement instead of simple field

Boussetta moez

1/10/24, 2:19 PM

I'm using a view to list products from db index. I want to implement a custom sorting (my goal is to sort by current taxonomy term page, products having a specific node field field_MY_FIELD_NAME = CURRENT_TID, showing up first)

First using hook_views_data_alter I override the sort of an indexed field that I have chosen as a sort criteria inside the view

function MODULE_views_data_alter(array &$data) {
  $data['search_api_index_products_index']['category_tid']['sort']['id'] = 'current_category_first';
}

Next I created the corresponding class to alter the query sorting

namespace Drupal\MODULE\Plugin\views\sort;

use Drupal\search_api\Plugin\views\sort\SearchApiSort;

/**
 * Basic sort handler for product listing.
 *
 * @ViewsSort("current_category_first")
 */
Class CurrentCategoryFirst extends SearchApiSort{
  public function query(){
    $this->ensureMyTable();

    $formula = '-1 * FIELD(' . $this->tableAlias . '.' . $this->field . ',56' . ')';
    $this->query->addOrderBy(NULL, $formula, 'ASC','current_category_first');

  }
}

this mysql $formula is inspired from the module views_list_sort witch is working fine with a simple view that do not retrieve from search API index (assuming that the term_id = 56, witch will be calculated later )

Unfotunately, This code do not list the view with the desired order, using ASC or DESC order change nothing (of course the tid=56 exists and is the current term page where i'm testing)

also using sort method instead of addOrderBy do not work, as it expect to have an existing field as a parameter and putting a formula throws "Unknown field" error

I tried also to separate the view into two views to use Union query but no luck as the query object of search api is not of type SelectInterface

0 + 2

views

cilefen

1/10/24, 2:55 PM

What is the question?

Boussetta moez

1/10/24, 3:27 PM

question is what's wrong with this code, why it does not sort the view? any other idea also is welcome to achieve my goal described in beginning of the question.

Elon Musk

I sit in a Tesla and translated this thread with Ai:

EN: Custom sorting using a MySQL statement instead of simple field

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.