Skip Navigation

[Resolved] order a view with the author last name

This is the technical support forum for Toolset - a suite of plugins for developing WordPress sites without writing PHP.

Everyone can read this forum, but only Toolset clients can post in it. Toolset support works 6 days per week, 19 hours per day.

This topic contains 15 replies, has 2 voices.

Last updated by Pat 12 months ago.

Assigned support staff: Jamal.

Author
Posts
#1788847

Pat

I would like to know how to sort a Views by author's meta (ie : lastname).
In my Views, here is the field I want to use for that : [wpv-post-author format="meta" meta="last_name"]

#1791281

Jamal
Supporter

Languages: English (English ) French (Français )

Timezone: Africa/Casablanca (GMT+01:00)

The author's lastname is saved as a user meta(or user custom field) and is saved on the wp_usermeta table. So, you will need to modify the SQL query of the view to be able to query the information from that table too.
Check this sample code:

add_action( 'pre_get_posts', function( $query ) {
	global $WP_Views;

	if($WP_Views->current_view == 7){
		add_filter( 'posts_clauses', 'ts1788847_add_lastname_orderby', 10, 2 );
	}
} );

function ts1788847_add_lastname_orderby( $clauses, $query ) {
	global $wpdb;
	global $WP_Views;

	if($WP_Views->current_view == 7){
		$clauses['join'] = 'LEFT JOIN ' . $wpdb->usermeta . ' AS author ON ( ' . $wpdb->posts . '.post_author = author.user_id and author.meta_key = "last_name" ) ';
		$clauses['orderby'] = 'author.meta_value ' . $query->get( 'order' );
	}

    return $clauses;
}

Notice how $WP_Views is used to check if the running query is the view's one. Modify "7" with the ID of your view.

Read more about these WordPress hooks here:
- https://developer.wordpress.org/reference/hooks/pre_get_posts/
- https://developer.wordpress.org/reference/hooks/posts_clauses/

#1791731

Pat

Hi Jamal,

Thanks for your proposal.
Unfortunately, the result of the Views shows no record (if I disable the hook, then I can see all the records !). I have placed the right Views ID (6808).
Another point is that I don't see how to order by ASC?
Any idea ?
regards
Pat

#1794207

Jamal
Supporter

Languages: English (English ) French (Français )

Timezone: Africa/Casablanca (GMT+01:00)

Please make sure to change the ID "7" with yours in both lines 4 and 13.

To sort the view by ASC, you can change line 15 to:

        $clauses['orderby'] = 'author.meta_value ASC';

If you are still not getting the expected results, allow me temporary access to check this further. Add the FTP credentials too. Your next reply will be private to let you share credentials safely. ** Make a database backup before sharing credentials. **

#1795093

Pat
Toolset1.jpg

Hi Jamal,

Just tested but the result is still 'No result" !
I'm sorry but I'm not able to give you access. I have attached a sceenshot of the Views (6808) and here is the loop code :

[wpv-layout-start]
	[wpv-items-found]
	<!-- wpv-loop-start -->
<table width="100%" style="background-color:blue;">
  <tr><td colspan="2" style="text-transform:uppercase;font-weight:bold;">[types field='nom-de-l-evenement'][/types]</td><td>[types field='niveau-du-cours'][/types]</td><td style="text-align:right;">Année : [annee_univers] / [annee_univers_plus1]</td></tr>

  <tr><td>[wpv-post-title item="@formateur-product.parent"] [types field='prenom-formateur' item='@formateur-product.parent'][/types]</td><td>[wpv-post-title item="@localisation-product.parent"]</td><td>[types field='jour'][/types] - [types field='heure'][/types]h[types field='minutes'][/types]</td><td>Nb d'inscrits : [wpv-found-count]</td></tr>
</table>
Nb de colonnes : [count_instances field="wpcf-date-des-seances"]
	<table width="100%" class="table-striped back presence">
		<thead>
			<tr>
				<th>Adhérent</th>
				
              [wpv-for-each field="wpcf-date-des-seances"]<th>[types field='date-des-seances' style='text' format='d' separator=''][/types]</br>[types field='date-des-seances' style='text' format='m' separator=''][/types]</br>[types field='date-des-seances' style='text' format='y' separator=''][/types]</th>[/wpv-for-each]

			</tr>
		</thead>
		<tbody class="wpv-loop js-wpv-loop">
		<wpv-loop>
			<tr>
				[wpv-post-body view_template="loop-item-in-backoffice-liste-de-presence"]
			</tr>
		</wpv-loop>
		</tbody>
	</table>
	<!-- wpv-loop-end -->
	[/wpv-items-found]
	[wpv-no-items-found]
		<strong>[wpml-string context="wpv-views"]Pas encore d'inscrits pour cet événement[/wpml-string]</strong>
	[/wpv-no-items-found]
[wpv-layout-end]

And the content template :

<td>[types usermeta='civilite' user_is_author='true'][/types] <span style="text-transform:uppercase;">[wpv-post-author format="meta" meta="last_name"]</span></br>[wpv-post-author format="meta" meta="first_name"]</br>[wpv-post-author format="meta" meta="user_email"]</td>

[wpv-view name="backoffice-table-td-liste-de-presence" ids="[wpv-post-id item="$current_page"]"]

Hope this help to better undestand the issue.
Thank for your help
Pät

#1795393

Jamal
Supporter

Languages: English (English ) French (Français )

Timezone: Africa/Casablanca (GMT+01:00)

Can you also share the custom code that you are using?

Can you activate views debugging, in Toolset->Settings->Front-end Content, rerun the view and share the SQL part of the debug popup?

#1795563

Pat

PYes of course. Here ir is :

add_action( 'pre_get_posts', function( $query ) {
    global $WP_Views;
 
    if($WP_Views->current_view == 6808){
        add_filter( 'posts_clauses', 'ts1788847_add_lastname_orderby', 10, 2 );
    }
} );
 
function ts1788847_add_lastname_orderby( $clauses, $query ) {
    global $wpdb;
    global $WP_Views;
 
    if($WP_Views->current_view == 6808){
        $clauses['join'] = 'LEFT JOIN ' . $wpdb->usermeta . ' AS author ON ( ' . $wpdb->posts . '.post_author = author.user_id and author.meta_key = "last_name" ) ';
        $clauses['orderby'] = 'author.meta_value ASC';
    }
 
    return $clauses;
}

Regards
Pat

#1795629

Jamal
Supporter

Languages: English (English ) French (Français )

Timezone: Africa/Casablanca (GMT+01:00)

Thank you Pät, but all this information was not enough for me to spot what the real issue is.

I'll suggest, that you turn on PHP debugging, visit the view again to check if any errors will be written to the debug.log file.
https://toolset.com/documentation/programmer-reference/debugging-sites-built-with-toolset/#php-debugging

If you can provide a duplicator copy of your website I'll give it a try locally. Your next reply will be private to let you share the download link safely. Please create an administrator user for me before creating the copy. You can delete it from your website after taking the copy.

#1795631

Pat

Hi Jamal,
Sorry, I just see the last part of your message. Here is the debug info :

Activer les sauts de ligne Activer la coloration synthaxique
Infos sur la page
Page actuelle hidden link
Totalité de la mémoire utilisée 5.2866MB
Durée de rendu 1,64
Total des requêtes MySQL 237
Utilisation CPU 1.07%
Infos sur les éléments

ID 94
Intitulé Intervenants d'un événement (Modifier)
Sorte d'élément Visualiser
Durée de rendu 0
Mémoire utilisée 0.0305MB
Résumé Formateurstrié par titre de la publication, par ordre décroissant
Arguments de requête
Requête MySQL
Éléments trouvés 1
Résultats de la requête
Filtres
Filter arguments before the query using wpv_filter_query
Filter the returned query using wpv_filter_query_post_process

Sorte d'élément Corps de la publication
Durée de rendu 0
Mémoire utilisée 0.0017MB

ID 98
Intitulé Salle d'un événement (Modifier)
Sorte d'élément Visualiser
Durée de rendu 0
Mémoire utilisée 0.0151MB
Résumé Localisationstrié par Date de publication, par ordre décroissant
Arguments de requête
Requête MySQL
Éléments trouvés 1
Résultats de la requête
Filtres
Filter arguments before the query using wpv_filter_query
Filter the returned query using wpv_filter_query_post_process

ID 6796
Intitulé Backoffice Liste des adhérents d'un événement (Modifier)
Sorte d'élément Visualiser
Durée de rendu 0
Mémoire utilisée 0.115MB
Résumé Ins enseignements, Ins conférences, Ins sortiestrié par Auteur de la publication, par ordre croissant
Arguments de requête
Requête MySQL
Éléments trouvés 16
Résultats de la requête
Filtres
Filter arguments before the query using wpv_filter_query
Filter the returned query using wpv_filter_query_post_process

ID 12345
Intitulé Backoffice Liste d'attente d'un enseignement (Modifier)
Sorte d'élément Visualiser
Durée de rendu 0
Mémoire utilisée 0.0033MB
Résumé Listes attentetrié par Date de publication, par ordre décroissant
Arguments de requête
Requête MySQL
Éléments trouvés 0
Résultats de la requête
Filtres
Filter arguments before the query using wpv_filter_query
Filter the returned query using wpv_filter_query_post_process

ID 6808
Intitulé Backoffice Liste de présence (Modifier)
Sorte d'élément Visualiser
Durée de rendu 0
Mémoire utilisée 0.0024MB
Résumé Ins enseignements, Ins conférences, Ins sortiestrié par Date de publication, par ordre décroissant
Arguments de requête
Requête MySQL
Éléments trouvés 0
Résultats de la requête
Filtres
Filter arguments before the query using wpv_filter_query
Filter the returned query using wpv_filter_query_post_process

#1795635

Pat

Here is the error log result :

[30-Sep-2020 12:54:49 UTC] Erreur de la base de données WordPress Unknown column 'wp_postmeta.meta_key' in 'where clause' pour la requête SELECT wp_posts.* FROM wp_posts LEFT JOIN wp_usermeta AS author ON ( wp_posts.post_author = author.user_id and author.meta_key = "last_name" ) WHERE 1=1 AND wp_posts.ID NOT IN (12258) AND (
( wp_postmeta.meta_key = 'wpcf-id-du-produit-achete' AND CAST(wp_postmeta.meta_value AS SIGNED) = '12258' )
AND
( mt1.meta_key = 'wpcf-annee-universitaire' AND CAST(mt1.meta_value AS SIGNED) = '2020' )
) AND wp_posts.post_type IN ('ins-enseignement', 'ins-conference', 'ins-sortie') AND ((wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')) GROUP BY wp_posts.ID ORDER BY author.meta_value ASC faite par require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), do_action('template_redirect'), WP_Hook->do_action, WP_Hook->apply_filters, Class_WooCommerce_Views->woocommerce_views_activate_template_redirect, include('/plugins/woocommerce-views/templates/single-product.php'), the_content, apply_filters('the_content'), WP_Hook->apply_filters, do_shortcode, preg_replace_callback, do_shortcode_tag, ET_Builder_Element->_render, ET_Builder_Section->render, do_shortcode, preg_replace_callback, do_shortcode_tag, ET_Builder_Element->_render, ET_Builder_Row->render, do_shortcode, preg_replace_callback, do_shortcode_tag, ET_Builder_Element->_render, ET_Builder_Column->render, do_shortcode, preg_replace_callback, do_shortcode_tag, ET_Builder_Element->_render, do_shortcode, preg_replace_callback, do_shortcode_tag, OTGS\Toolset\Access\Controllers\Shortcodes->create_shortcode_toolset_access, OTGS\Toolset\Access\Controllers\Shortcodes->wpcf_access_do_shortcode_content, do_shortcode, preg_replace_callback, do_shortcode_tag, WP_Views->short_tag_wpv_view, WP_Views->render_view_ex, WP_Views->render_view, wpv_filter_get_posts, WP_Query->__construct, WP_Query->query, WP_Query->get_posts

Regards
Pat

#1795643

Jamal
Supporter

Languages: English (English ) French (Français )

Timezone: Africa/Casablanca (GMT+01:00)

I think that the issue is caused by some wrong "JOIN" clause. I don't see why:
- Unknown column 'wp_postmeta.meta_key'
- Why there is no JOIN for "mt1" that is being used as "mt1.meta_key = 'wpcf-annee-universitaire'"

Can you open the SQL section on the debug popup and share the underlying SQL query? In your reply here #1795631, collapse the SQL section, and share the underlying SQL query, it should start with "SELECT".

#1795943

Pat

Here it is :

SELECT wp_posts.* FROM wp_posts LEFT JOIN wp_usermeta AS author ON ( wp_posts.post_author = author.user_id and author.meta_key = "last_name" ) WHERE 1=1 AND wp_posts.ID NOT IN (14) AND (
( wp_postmeta.meta_key = 'wpcf-id-du-produit-achete' AND CAST(wp_postmeta.meta_value AS SIGNED) = '14' )
AND
( mt1.meta_key = 'wpcf-annee-universitaire' AND CAST(mt1.meta_value AS SIGNED) = '2020' )
) AND wp_posts.post_type IN ('ins-enseignement', 'ins-conference', 'ins-sortie') AND ((wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')) GROUP BY wp_posts.ID ORDER BY author.meta_value ASC

#1795949

Jamal
Supporter

Languages: English (English ) French (Français )

Timezone: Africa/Casablanca (GMT+01:00)

Thank you!

Now, disable my custom code and run the view again, then share the SQL with me. I believe that my custom code alters the original SQL and it becomes wrong.

#1795991

Pat

SELECT wp_posts.* FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) WHERE 1=1 AND wp_posts.ID NOT IN (14) AND (
( wp_postmeta.meta_key = 'wpcf-id-du-produit-achete' AND CAST(wp_postmeta.meta_value AS SIGNED) = '14' )
AND
( mt1.meta_key = 'wpcf-annee-universitaire' AND CAST(mt1.meta_value AS SIGNED) = '2020' )
) AND wp_posts.post_type IN ('ins-enseignement', 'ins-conference', 'ins-sortie') AND ((wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')) GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC

#1796541

Jamal
Supporter

Languages: English (English ) French (Français )

Timezone: Africa/Casablanca (GMT+01:00)

Thank you. The issue with our code is that it was overriding the "JOIN" clause because it assumed that it was empty. Please try the following code and let me know if it works:

add_action( 'pre_get_posts', function( $query ) {
    global $WP_Views;
  
    if($WP_Views->current_view == 6808){
        add_filter( 'posts_clauses', 'ts1788847_add_lastname_orderby', 10, 2 );
    }
} );
  
function ts1788847_add_lastname_orderby( $clauses, $query ) {
    global $wpdb;
    global $WP_Views;
  
    if($WP_Views->current_view == 6808){
        $clauses['join'] .= ' LEFT JOIN ' . $wpdb->usermeta . ' AS author ON ( ' . $wpdb->posts . '.post_author = author.user_id and author.meta_key = "last_name" ) ';
        $clauses['orderby'] = 'author.meta_value ASC';
    }
  
    return $clauses;
}

Notice how the line 14 was updated(adding (.) before (=) and a space before LEFT).