Have you ever felt the need to change the sorting order of a View?. The most common method to change the sorting order would be to edit the corresponding View template file. Now there is a more elegant solution for doing that instead of editing a view template file. Read on to know how to use Drupal hook_views_query_alter to change views sorting.
I had a view result which displays taxonomy terms and its content. But here terms are created in a hierarchical manner. There will be a parent and child terms. There are also some terms which doesn't have the child terms. Here the view was sorted by parent weight and then sorted by child term weight. My view result was showing all parent terms first and then child terms even after I apply relationship. My requirement was to show all child terms order by its weight, remove parent terms and show parent terms which doesn't have the child terms. So I started fixing this issue by a view table template file by hard coding the term name and unsettling it. But every this was fine until I realize that pagination was not proper for my view result. Because I was trying to unset some view result and applying pagination. So if I reduce number of items per page to 2 or 3, my first page was empty because same page contains the rows which were unset by template. Here the best method to manage this complex sorting was altering the view result itself.
Write your alter hook, hookviewsqueryalter as a next function. This is your main area as here you have to work a bit. Make sure that you are using correct machine name for the view. Function eclkccustomizationsviewsqueryalter (ViewExecutable $view, QueryPluginBase $query) // This alter view is required to filter to only 'reviewed' submissions - it was not // possible to save the view due to a bug. If ($view - id 'jobcenter').
In my view I was sorting the result by two columns first was taxonomy_term_data_taxonomy_term_hierarchy.weight and second one was taxonomy_term_data_node.weight. Here the first column was NULL for parent terms. Due to this parent will appears as the first in result set. We know that when we sort a column in which has NULL values in ASC order, it will show up first in result. Similarly if it's DESC order then NULL values will displays in the last.
This is the case where add_where and orderby with CASE come in handy. Here add_where adds a where clause for the view's SQL statement to unset the terms which we do not want to display and orderby with CASE will adds conditions to ORDER BY statement of SQL query.
For example, here I will use a sample code to show how where and orderby statements are used.
Here add_where statement will adds an additional where clause to current where conditions and orderby will alter the orderby statement to reorder the result.Also orderby will alter the first part and orderby will alter the second part of orderby clause. In first ordering if the value is NULL then use second column ordering else use first column ordering, also order by second column in ASC. It's clear that non-null values comes first and we get the desired sorted result. By using CASE statement on our view_query_alter we can have a full control on our result set. I hope this article was helpful to understand view_query_alter and using CASE in ordering. Thanks for reading.
Hookviewsqueryalter not working? Very new to web development and even newer to Drupal, and I have some questions that I would really appreciate if anyone could answer! Function hookviewsqueryalter (ViewExecutable $view, QueryPluginBase $query) // (Example assuming a view with an exposed filter on node title.).
To modify or alter a drupal view query, follow the below steps. Views are brilliant piece of functionality that can be added to drupal via a module.
Navigate to sites/all/modules/yourmodule and create your module.info file, to inform our module is ready to install on the system.
Declare hook_views_api, as this is required for your module to have its include files loaded.
Write your alter hook, hook_views_query_alter as a next function. This is your main area as here you have to work a bit. Make sure that you are using correct machine name for the view. There is a trick for this, just examine the URL of the view will something like this admin/structure/views/view/oa_fullcalendar/edit. So oa_fullcalendar is my view name.
Alter Query Sql
In this example, I’ve tried to change the order by clause i n the view query. You can use Devel module to debug further in Drupal