Jump to content
Mark
 Share


4.0 - Tables

There's a table in the Admin CP of the IPS Social Suite that I really like - the members table. It has some really cool options - you can reorder the data just by clicking on a column head; you can quickly search for a member by typing a name into a search box at the top; there's some filter options to quickly show banned, locked, spam and validating members; and there's an advanced search form to search for members based on practically any criteria.

It would be great if these features were available elsewhere. So much like we did for forms, we decided to create a central helper class for building tables.

To demonstrate how it works, I'm going to go through, step by step, how I recreated the Admin CP members table in IPS 4.


It starts with one line to create the table, and another to pass it to the output class:

		/* Create the table */
		$table = new IPSHelpersTableDb( 'core_members', 'app=core&module=members&section=members' );
		
		/* Display */
		IPSOutput::i()->output	= IPSOutput::i()->getTemplate( 'global' )->block( 'members', $table );




With just those two lines, you'll see this:


Some things to note:

  • We're calling IPSHelpersTableDb - the "Db" part indicates that the source of data for our table is a database table. There are other classes to use, for example, a JSON document as the data source.
  • We pass it the name of our database table (or for the other classes, whatever the data source is) and the query string part of the URL where we're going to be displaying this (which we need to build the links and AJAX calls).
  • I'm passing it to the output through a template called "block" which simply adds the dark-blue bar at the top, which isn't actually part of the table itself, and some padding. The "members" parameter is the key for the langauge string to use in that dark-blue bar.
  • I'm passing $table directly to the template - the helper class has a __toString method which renders the table, so the output class thinks it's been given a normal string.




The first obvious thing is that we're showing all the columns in the database table, which obviously we don't want. So let's add another line to specify which columns we want:

$table->include = array( 'name', 'email', 'joined', 'member_group_id', 'ip_address' );




In this example, I'm giving the helper class a list of columns to include - I could alternatively pass a list of columns to exclude, if that would be more appropriate.

The output is now this:


Some things to note:

  • It's worked out pagination itself. When you click a pagination link, the contents of the table will update with AJAX, including changing your browser's URL (unless you have JavaScript disabled of course, in which case it will work like a normal link). Pagination defaults to 25 results per page, but you can change that just by changing a property in the class.
  • All the columns are clickable, which will resort the results. You can sort any column ascending or descending. Resorting will also update with AJAX (including changing your browser's URL), unless JavaScript is disabled.




I want the headers to display something more meaningful than the column name. The system will automatically look for language strings which match the column name - you can also optionally specify a prefix, and it'll look for langauge strings which match that followed by the column name.

Let's specify a prefix:

$table->langPrefix = 'members_';



And I'll then create some language strings that match that (so "members_name", "members_email", etc.).

The output is now this:





Next - we need to change how we display some of those values. The joined date and the group are displaying the raw values from the database, but we want something more meaningful than that.

To format the values, we simply create an array of lambda functions - one for each we want to format:

		$table->parsers = array(
			'joined'			=> function( $val, $row )
			{
				return IPSDateTime::ts( $val )->localeDate();
			},
			'member_group_id'	=> function( $val, $row )
			{
				return IPSMemberGroup::load( $val )->formattedName();
			}
		);




I'm also going to add one additional line to specify the "main" column, which applies some additional styles:

$table->mainColumn = 'name';




The output is now this:


Some things to note:

  • I'm using the IPSDateTime class to format the joined date. The ts method in this is a factory method which takes a UNIX timestamp and returns an object of IPSDateTime. IPSDateTime extends DateTime, so all the features of that class are available to us. The localeDate method returns a string with the date formatted appropriately according to user's locale.
  • The IPSMemberGroup::load call being executed for each result may look like it might be resource intensive, but it caches objects it creates, so it's only actually "loading" each group once.





Now I want to add a column with the user's photo. There isn't a single "photo" column in the database we can use for this (since the photo could be one they uploaded, a photo from their Facebook account if they're using Facebook Connect, a Gravatar image, or some other things), we need to use a method in the IPSMember class.

This isn't a problem. I can simply add an element to our list of fields to include and add that into the parsers.

$table->include = array( 'photo', 'name', 'email', 'joined', 'member_group_id', 'ip_address' );

		$table->parsers = array(
			'photo'				=> function( $val, $row )
			{
				return IPSMember::constructFromData( $row )->photo('mini');
			},



I'll also want to specify that we cannot use the photo column for sorting:

$table->noSort	= array( 'photo' );




The output is now this:



Some things to note:

  • Since this isn't a value which exists in the database, the value of $val in the lambda function will be NULL, however, $row has all the data for that record.
  • We're not using IPSMember::load to get the member object, since that would execute an additional query for every result, which would be resource intensive, and unnecessary since we already have that data. Instead, we use the constructFromData method and pass it the row from the database.




Next, I want to specify the default sorting. This is done with just two lines of code:

		$table->sortBy = $table->sortBy ?: 'joined';
		$table->sortDirection = $table->sortDirection ?: 'desc';




The output is now this:





Now, I want to add a quick search box. All we need to do is specify which column the quick search should look at:

$table->quickSearch = 'name';




The output is now this:


Some things to note:

  • As you type, results are obtained with AJAX.
  • You can page through your results (the number of pages will update automatically) and reorder your results by clicking the headers without loosing your search.




I also want to allow more advanced search options - like to search by email address, or joined date. To do this, I create a new array:

		$table->advancedSearch = array(
			'member_id'			=> IPSHelpersTableSEARCH_CONTAINS_TEXT,
			'email'				=> IPSHelpersTableSEARCH_CONTAINS_TEXT,
			'ip_address'		=> IPSHelpersTableSEARCH_CONTAINS_TEXT,
			'member_group_id'	=> array( IPSHelpersTableSEARCH_SELECT, array( 'options' => $groups ), function( $val )
			{
				return array( 'member_group_id=? OR ? IN(mgroup_others)', $val, $val );
			} ),
			'joined'			=> IPSHelpersTableSEARCH_DATE_RANGE,
			);




To explain what's going on here:

  • The keys are the columns we're letting the user search on.
  • The values are usually a constant indicating the type of search that is appropriate for that column.
  • The member_group_id element is a bit more complicated. It has to specify an array of options (I've omitted the code to generate $groups in this snippet, but it'll be at the end of this blog entry), and, because we need to search both primary and secondary groups based on the value, there's a lambda function to get the proper WHERE clause for the query.


Now, next to the quick search box there's a button which will bring up a modal popup (or just take you to a new page if JavaScript is disabled) which looks like this:


Some things to note:
  • The date entry boxes use the HTML5 date input type:

    If your browser doesn't support that, there's a JavaScript fallback:

    And if you're really awkward and are using a browser that doesn't support the HTML5 date input type and have JavaScript disabled, you'll see a regular text box where you can enter a date in practically any format, and it'll work it out.
  • After performing the search, you can reorder your results by clicking the headers without loosing your search.





Now, I want to add some filters so you can quickly see banned, spam, locked and validating members. To do this, you create an array simply specifying the WHERE clause to use in the query for each filter:

		/* Filters */
		$table->filters = array(
			'members_filter_banned'		=> 'member_banned=1',
			'members_filter_locked'		=> 'failed_login_count>=' . (int) IPSSettings::i()->ipb_bruteforce_attempts,
			'members_filter_spam'		=> '(members_bitoptions & ' . IPSMember::$bitOptions['bw_is_spammer'] . ') != 0',
			'members_filter_validating'	=> 'v.lost_pass=0 AND v.vid IS NOT NULL'
		);



For this though, I'll also need to join the core_validating database table, so we add one more line for that:

$table->joins = array( array( 'from' => array( 'core_validating' => 'v' ), 'where' => 'v.member_id=_0.member_id' ) );




The output is now this:


Some things to note:

  • The helper class will add the "All" filter automatically.
  • It's getting the word to use for the filter by looking for a language string with the same key as the key in the array passed.
  • Like everything else, clicking a filter updates the results with AJAX and the filter is retained in searches.





Finally, the last thing I need to do is add a column with some buttons. You can specify a normal array for buttons to show in the header, and a lambda functions to return an array for buttons to show for each row:

		$table->rootButtons = array(
			'add'	=> array(
				'icon'		=> array( 'icons/add.png', 'core' ),
				'title'		=> 'members_add',
				'link'		=> 'app=members&module=members&section=members&do=add',
			)
		);
		$table->rowButtons = function( $row )
		{
			return array(
				'edit'	=> array(
					'icon'		=> array( 'icons/edit.png', 'core' ),
					'title'		=> 'edit',
					'link'		=> 'app=members&module=members&section=members&do=edit&id=' . $row['member_id'],
				),
				'delete'	=> array(
					'icon'		=> array( 'icons/delete.png', 'core' ),
					'title'		=> 'delete',
					'link'		=> 'app=members&module=members&section=members&do=delete&id=' . $row['member_id'],
					'class'		=> 'delete',
				),
			);
		};






Our finished table looks like this:


And behaves like this:
http://screencast.com/t/KMFq8zCE



To recap, here's the code, in it's entirety to generate that table:

		/* Create the table */
		$table = new IPSHelpersTableDb( 'core_members', 'app=core&module=members&section=members' );
		$table->langPrefix = 'members_';
				
		/* Columns we need */
		$table->include = array( 'photo', 'name', 'email', 'joined', 'member_group_id', 'ip_address' );
		$table->mainColumn = 'name';
		$table->noSort	= array( 'photo' );
		
		/* Default sort options */
		$table->sortBy = $table->sortBy ?: 'joined';
		$table->sortDirection = $table->sortDirection ?: 'desc';
		
		/* Filters */
		$table->joins = array( array( 'from' => array( 'core_validating' => 'v' ), 'where' => 'v.member_id=_0.member_id' ) );
		$table->filters = array(
			'members_filter_banned'		=> 'member_banned=1',
			'members_filter_locked'		=> 'failed_login_count>=' . (int) IPSSettings::i()->ipb_bruteforce_attempts, /*@todo*/
			'members_filter_spam'		=> '(members_bitoptions & ' . IPSMember::$bitOptions['bw_is_spammer'] . ') != 0',
			'members_filter_validating'	=> 'v.lost_pass=0 AND v.vid IS NOT NULL'
		);
		
		/* Groups for advanced filter (need to do it this way because array_merge renumbers the result */
		$groups = array( '' => 'any_group' );
		foreach ( IPSMemberGroup::groups() as $k => $v )
		{
			$groups[ $k ] = $v;
		}
		
		/* Search */
		$table->quickSearch = 'name';
		$table->advancedSearch = array(
			'member_id'			=> IPSHelpersTableSEARCH_CONTAINS_TEXT,
			'email'				=> IPSHelpersTableSEARCH_CONTAINS_TEXT,
			'ip_address'		=> IPSHelpersTableSEARCH_CONTAINS_TEXT,
			'member_group_id'	=> array( IPSHelpersTableSEARCH_SELECT, array( 'options' => $groups ), function( $val )
			{
				return array( 'member_group_id=? OR ? IN(mgroup_others)', $val, $val );
			} ),
			'joined'			=> IPSHelpersTableSEARCH_DATE_RANGE,
			);
		
		/* Custom parsers */
		$table->parsers = array(
			'photo'				=> function( $val, $row )
			{
				return IPSMember::constructFromData( $row )->photo('mini');
			},
			'joined'			=> function( $val, $row )
			{
				return IPSDateTime::ts( $val )->localeDate();
			},
			'member_group_id'	=> function( $val, $row )
			{
				return IPSMemberGroup::load( $val )->formattedName();
			}
		);
		
		/* Specify the buttons */
		$table->rootButtons = array(
			'add'	=> array(
				'icon'		=> array( 'icons/add.png', 'core' ),
				'title'		=> 'members_add',
				'link'		=> 'app=members&module=members&section=members&do=add',
			)
		);
		$table->rowButtons = function( $row )
		{
			return array(
				'edit'	=> array(
					'icon'		=> array( 'icons/edit.png', 'core' ),
					'title'		=> 'edit',
					'link'		=> 'app=members&module=members&section=members&do=edit&id=' . $row['member_id'],
				),
				'delete'	=> array(
					'icon'		=> array( 'icons/delete.png', 'core' ),
					'title'		=> 'delete',
					'link'		=> 'app=members&module=members&section=members&do=delete&id=' . $row['member_id'],
					'class'		=> 'delete',
				),
			);
		};
		
		/* Display */
		IPSOutput::i()->output	= IPSOutput::i()->getTemplate( 'global' )->block( 'members', $table );



 Share

Comments

Recommended Comments



Cool. I can't wait to see these features integrated into the front end, e.g. Members, Downloads, custom database listing templates in Content, the forthcoming (?) agenda view Calendar. I hope that's part of the master plan.

Link to comment
Share on other sites

Everything looks extremely good but here's a couple of questions:

  • Is there any way to specify ONLY the columns we want to enable for sorting? Excluding the photo column is easy enough but what if I have a table with 20+ columns and I want to be able to sort it only by 2 columns? Do I need to add 18+ values in "noSort"? For situations like this something like "onlySort" would surely help.
  • The join being added is fine and all, and you're even using the "v." prefix in the queries, however, what about the other fields from the members table? Shouldn't there be a prefix there too like "m."?

 
That's all (for now at least :tongue:)

 

 

EDIT

 

About #2.. looking again at the code I see you are actually using a "_0." prefix in the join code:

$table->joins = array( array( 'from' => array( 'core_validating' => 'v' ), 'where' => 'v.member_id=_0.member_id' ) );

Can you explain?

Link to comment
Share on other sites

Something else I noticed from the video is that it would help having the pagination at the top of the table too and not only at the bottom. Having to always scroll down to the bottom to change page is clearly not user-friendly.

Link to comment
Share on other sites

I would suggest adding the ability to combine the results with those of the pfields_content table. This then allows you to check things like interests, location and other user supplied data. I do this with a custom query:

 

SELECT name, field_32, field_10, field_11, email, ip_address 
FROM ipb_pfields_content, ipb_members
where ipb_members.member_id = ipb_pfields_content.member_id order by ipb_members.joined DESC;

This gives me a table of results:

 

Member name

Member real name (Custom profile field)

Location

Email address

Interests

IP Address

 

This makes checking new account registrations a lot quicker than manually going through every new member.

Link to comment
Share on other sites

Everything looks extremely good but here's a couple of questions:

  • Is there any way to specify ONLY the columns we want to enable for sorting? Excluding the photo column is easy enough but what if I have a table with 20+ columns and I want to be able to sort it only by 2 columns? Do I need to add 18+ values in "noSort"? For situations like this something like "onlySort" would surely help.
  • The join being added is fine and all, and you're even using the "v." prefix in the queries, however, what about the other fields from the members table? Shouldn't there be a prefix there too like "m."?

 
That's all (for now at least :tongue:)

 

 

EDIT

 

About #2.. looking again at the code I see you are actually using a "_0." prefix in the join code:

$table->joins = array( array( 'from' => array( 'core_validating' => 'v' ), 'where' => 'v.member_id=_0.member_id' ) );

Can you explain?

 

 

1. Yes and no. Though there is no $onlySort property, however a simple array_diff call (since it will know what columns it's including by that point) can allow you to specify a list to exclude.

In practice of course, you wouldn't have a table with 20 columns - it'd be too wide (though it will use an overflow with a horizontal scroll, it wouldn't be very user friendly).

 

2. It automatically will use the "_0" alias (there's no special reason why I chose that other than it's something one would be unlikely to use as a join alias).

 

 

Something else I noticed from the video is that it would help having the pagination at the top of the table too and not only at the bottom. Having to always scroll down to the bottom to change page is clearly not user-friendly.

 

 

Someone with a much better design sense than me will make it look pretty :smile:

 

 

I would suggest adding the ability to combine the results with those of the pfields_content table. This then allows you to check things like interests, location and other user supplied data. I do this with a custom query:

 

SELECT name, field_32, field_10, field_11, email, ip_address 
FROM ipb_pfields_content, ipb_members
where ipb_members.member_id = ipb_pfields_content.member_id order by ipb_members.joined DESC;

This gives me a table of results:

 

Member name

Member real name (Custom profile field)

Location

Email address

Interests

IP Address

 

This makes checking new account registrations a lot quicker than manually going through every new member.

 

Yeah, the members table will have custom profile field support - this blog entry was more about demonstrating the table helper class.

Link to comment
Share on other sites

We've supported IPv6 for a while already :smile:

Might have been supported, but currently IPv4 is shown and not IPv6.  Though having both available (or getting to choose which is shown) would be nice.

Link to comment
Share on other sites

Might have been supported, but currently IPv4 is shown and not IPv6.  Though having both available (or getting to choose which is shown) would be nice.

It's determined by the server. The IP Addresses you see typically come from either $_SERVER['REMOTE_ADDR'] or $_SERVER['HTTP_X_FORWARDED_FOR'].

 

We don't control which one is displayed.

Link to comment
Share on other sites

Might have been supported, but currently IPv4 is shown and not IPv6.  Though having both available (or getting to choose which is shown) would be nice.

 
 

It's determined by the server. The IP Addresses you see typically come from either $_SERVER['REMOTE_ADDR'] or $_SERVER['HTTP_X_FORWARDED_FOR'].
 
We don't control which one is displayed.

And the reason you see it in the screencast is because Mark is using a webserver on a Mac, which is IPv6 native. :)
Link to comment
Share on other sites

Awesome. I love the compactness and the consequent readability of the code, very impressive. Can't wait to get a chance to dig around under the hood of 4.0, it's looking like a MAJOR improvement at the platform level, good for 3rd party devs, great for admins that have visions for how they want their site to function.

 

James

Link to comment
Share on other sites

[quote name="SECTalk.com" timestamp="1364317297"]We need a way to do this on the frontend with tabular data. 

Though my screenshots are of the acp (since we're not ready to show the front-end yet) - this can be done on the front-end too. Exact same code :)

Link to comment
Share on other sites

1. Yes and no. Though there is no $onlySort property, however a simple array_diff call (since it will know what columns it's including by that point) can allow you to specify a list to exclude.
In practice of course, you wouldn't have a table with 20 columns - it'd be too wide (though it will use an overflow with a horizontal scroll, it wouldn't be very user friendly).
 
2. It automatically will use the "_0" alias (there's no special reason why I chose that other than it's something one would be unlikely to use as a join alias).

 
 
1) Since the columns part supports both include & exclude options it would be nice to have it for sorting too for consistency.

 

2) Maybe you could add something like "$table->mainJoinPrefix", "$table->membersPrefix" or something like that? It is surely easier to remember than "_0."

Link to comment
Share on other sites




Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Add a comment...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...