Displaying Only Users with Posts in a Listing

Question

How can I display only users with posts in the Listing Grid?

Tutorial requirements:

• JetEngine plugin;
• Elementor or WordPress (block) editor;
• Several users that created posts;
• Users meta boxes (optional).

Answer

Create the SQL Query

Navigate to WordPress Dashboard > JetEngine > Query Builder and press the “Add New” button.

Type the query Name, and choose the “SQL Query” Query Type.

Enable the Advanced Mode toggle and enter the following into the SQL Query field:

SELECT * FROM (SELECT {prefix}users.ID, display_name, user_login, user_nicename, count({prefix}posts.ID) AS n, substring_index(display_name,' ',-1) AS lastname FROM {prefix}users JOIN wp_posts ON wp_posts.post_author = {prefix}users.ID WHERE {prefix}posts.post_type IN ("post") GROUP BY {prefix}posts.post_author ORDER BY n DESC LIMIT 10 ) AS SortedByCount ORDER BY lastname

After that, hit the “Add/Update Query” button.

Add the code

Before completing this step, ensure to make a backup. To learn more, proceed to the Backup Your WordPress Site ASAP article.

Enter such a code into the functions.php folder of the child theme or the Code Snippets plugin:

//add Get properties from query macro
add_filter( 'jet-engine/listings/macros-list', 'register_query_items_macro' );

        function register_query_items_macro( $macros_list ) {

                $macros_list['get_props_from_query'] = array(
                        'label' => 'Get properties from query',
                        'cb'    => 'get_props_from_query_macro',
                        'args'  =>  array( 
											'query_id' => array(
											'label'   => 'Query ID',
											'type'    => 'select',
											'options' => Jet_Engine\Query_Builder\Manager::instance()->get_queries_for_options(),
                                		),
											'property' => array(
											'label'   => 'Property',
											'type'    => 'text',
											'default' => '',
                                		),
									),
                        );
                
                return $macros_list;

        }

function get_props_from_query_macro( $field_value = null, $args = null ) {
	$args = explode( '|', $args );
	$query_id = $args[0];
	$property = $args[1];
	
	if ( ! $query_id || ! $property ) {
		return;
	}
	
	$query = Jet_Engine\Query_Builder\Manager::instance()->get_query_by_id( $query_id );

	$result = 'not-found';
	
	if ( $query ) {
		$query_items = $query->get_items();
		if ( ! empty( $query_items ) ) {
			$properties_array = array_column( $query_items, $property );
			$result = array_unique( $properties_array );
			$result = implode( ',', $result );
		}
	}
	
	return $result;
}

After entering, don’t forget to save the changes.

Create the Users Query

Go to WordPress Dashboard > JetEngine > Query Builder and press the “Add New” button.

Type the query Name, and choose the “Users Query” Query Type.

Open the Include/Exclude tab and hit the macros trigger near the Include field. Select the “Get properties from query” macro from the list. In the Query ID field, select the name of the recently created SQL query, and enter “ID” in the Property field. Finally, click the “Apply” and “Add/Update Query” buttons.

Create the Users Listing template

Go to WordPress Dashboard > JetEngine > Listings and hit the “Add New” button.

Pick the “Users” Listing source, enter the Listing item name, and select the “Elementor” or “Blocks (Gutenberg)” Listing view. Then, click the “Create Listing Item” button.

With the Dynamic Field, Dynamic Image, and other dynamic widgets or blocks, you can display user information, like nicknames, images, and biographical info. When you finish customizing the listing item, push the “Update” button.

Add the Listing Grid to a page

Move to the page where you want to display users with posts and click to edit it in the Gutenberg or Elementor editor. Drag-n-drop the Listing Grid widget or block there, and select the needed Listing from the drop-down menu.

Navigate to the Custom Query tab, enable the toggle and select the users query from the drop-down list. Now, the Listing Grid shows only users with posts. Hit the “Publish/Update” button to save changes.