Jump to content
Mark

By 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

Invision Community © 2024 IPS, Inc.
×
×
  • Create New...