[Resolved] order a view with the author last name

[Author]

Posts

[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"]

[Jamal]

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/

[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

[Jamal]

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. **

[Pat]

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

[Jamal]

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?

[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

[Jamal]

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.

[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

[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

[Jamal]

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".

[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

[Jamal]

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.

[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

[Jamal]

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).