In this Tutorial we’re going to build an interface that can be used as a tag list widget.
We’ll explore the use of Joins and Grouping in this project and should give you a better understanding of connecting data together.
This is going to be build live on this site, so you’ll be able to see the widget in this posts sidebar ->
The first step is to Create a New Applicaiton called “Widgets” give it the description “Widgets for my site”.
Create a New Interface in your widgets app and call “Tags” With the Description of “Tag view widget” Leave Navigation alone as its only a display interface for the front end.
Now on your new interface in the interface list, click on the edit link to edit it.
Now we’re going to setup the Table and Fields. So click on the “Fields Setup” tab.For the Table, select “wp_term_relationships” where wp_ is the prefix of your installation. You Should see the Fields of the selected table load below in the “Define Fieldtypes” section.
The Fields listed are:
- object_id
- term_taxonomy_id
- term_order
What we are needing is the tag name, which is found in the wp_terms table. but there is no direct relationship value in wp_term_relationships. But there is a relationship value in wp_term_taxonomy. So what we need to do is create a join from our primary table “wp_term_relationships” to “wp_term_taxonomy” and then join the value retrieved from “wp_term_taxonomy” to “wp_terms” which then can give us the tag name. Complicated sounding I know, but simple when you start doing it.
Lets jump back to setting up our fields. We wont be using the “object_id” field so leave that as it is.
Set the field “term_taxonomy_id” to “Join Table” and set it up like the following:
You may also see a “Sort By” option. you can leave that as it is.
So. the first part is done. We are returning the term_id from wp_term_taxonomy. We now need to take this value and join it to the wp_terms table so that we can return the tag name.
Now lets make a Virtual Field. Click the Virtual Field button is just below the “Define Fieldtypes” title if you don’t see the button, Click the Save button at the bottom of the interface and re-edit it. Virtual Fields are only apply-able to interfaces that have a saved table.
Once saved and opend again, head back to the Fields Setup tab and click the Virtual Field button.
A new field will appear below the button. Drag the field below the “term_taxonomy_if field we just worked on. the new field will be indented when placed below the field.
In your Virtual Field, Set the “Master Field” to “term_taxonomy_id” Set the field as visible by clicking the eye icon so it highlights green and then set its field type to Join Table and configure it like the following:
Be sure to click the advanced button to set the join type
So what we are now doing is taking the value from the first join and then joining that value to the wp_terms table to retrieve the actual tag name. What that does is in the list of the initial table wp_term_relationships entries, the the term_taxonomy_id is translated as a term_id which in tern is translated as a name. the name we’re going to show. You’ll aslo see that you have set the Join Type to Right Join. Let me try explain how the join types work
When you join a table the two tables are places side by side On to the left and One to the Right. The left table is the table you running your query on in this case its wp_term_taxonomy the table to the right is the joined table in this case wp_terms. Setting the join type is stating which table take priority.
Setting it to join left will be saying return all the results found in the left table. So if one of the values in the left doesnt have a value in the right you’ll get a blank entry because its returning the values from the left regardless of whether it gets a value fro the right or not.
Setting it to join right will be saying return all the results found in the right table. so if the left doesn’t have a value in the right, it wont return it as it will only return the rights value or found values
Setting it to just a join will give no preference to either so will return everything regardless.
We are setting it to right join because we only want tags . We set the term_taxonomy_id to join where taxonomy = post_tag so it wont find a connection for the wp_term_relationship on other terms meaning that there wont be a match from term_taxonomy_id to wp_terms so giving preference to the right, means we’ll only have tag names returned. Yikes that’s complicated!
Ok now We need to get the Tag Slug for the link. So Click on add Virtual Field and drag the field below the one you made above.
Click on the Little gear icon and change the title to Tag Slug.
Setup the field exactly like the one above, only where it says “Value Field” choose slug instead of name.
it should look like this.
OK so now Click the Save Button and then go view the interface by clicking the view link on the interface.
So now what you should see is essentially a list of all the tags in the system. But you’ll see duplicates of where tags have been used multiple times. so what we need to do is group them together so we only get 1 result for each tag.
So go edit the interface again and click on Fields Setup.
Add another Virtual Field and drag it below the first Virtual Field you created previously.
Click on the Little gear icon on the left corner to open the field properties and change the Title to “Tag Count”.
Then set its master as “term_taxonomy_id” an set the field to visible. Now set the fieldtype to Field Group.
In the fieldtype config panel that opens up, set the Grouping Action to Count and the Action Field to object_id. It should look like the image below.
What this is going to do is take the term_taxonomy_id field and where its repeated in the results, group them together and return them as a single entry. then for each of the items thats in the group its going to count the unique ones. we set the count to be object_id because this is a unique field so each one will be unique and therfore give us the exact number if entries per group. This virtual fields value will then be the number of items per tag or group.
So now Click Save and view your interface again. You’ll now no repeats with a column showing how many times the tag has been used. Awesome.
Now lets sort the tags by the number of times each is present. Edit the interface again and go to the Fields Setup. Scroll right down to Sort Field and select “Tag Count” or the title you gave your Field Group, as your sorting field and set it “Descending” and Click Save.
View your interface again and now you should see you most used Tags in DESCENDING ORDER
Great. We’re almost done. Now to give the interface a nice looking Template and we’re ready to add it to our side bar. Let get to styling.
Go edit the interface again.
Go to the General Setting tab.
Check the “Hide New Item Button” as we don’t want inserting ability.
In the “Items Per Page” you can set how many you want to show. If you want to show the Top 10 tags, enter 10. if you want to show all the tags enter 0. I’m going to enter 10 for this example.
scroll to Toolbar Setting and uncheck everything. Actually you can uncheck everything right down to the bottom.
Now go to the Permissions Tab. We want to have this interface visible to everyone, so set its permission to public. Its the very first one in the list.
Head on to the templates tab.
Click the Enable Checkbox to tell the interface to use the template. Set the Wrapper Element to div (just the word div not) and the Wrapper Class to “tagLinks”
Now in the Header Template box, we’ll define our css style. You can add this style to your template style.css to be cleaner if you want, but for this we’re adding it here.
Enter the following css code in your Header Template box. You can change it how you want your tags to look.
<style>
.tagLinks a {
background-color: #EFEFEF;
border: 1px solid #CCCCCC;
border-radius: 3px;
color: #333333 !important;
display: inline-block;
font-size: 11px;
margin: 3px;
padding: 2px 4px;
text-decoration: none;
}
</style>
Scroll down to the “Before” box and use the following Code for the widget title:
<h4 class=”widgetTitle”>Tags</h4>
You can call it what ever you want and use the style class that matches your them for better customisation.
Scroll down to the “Content” box. This is the loop area for each result. input the following code.
<a href=”/tag/{{__4f3b860ec42e1}}/”>{{__4f3b659c3be98}}</a>
Now depending on how you have set up your permalinks the href will change, set it how its appropriate to your config. I think this is most common.
The code {{_4f3b860ec42e1}} and {{__4f3b659c3be98}} are the generated ID of the Virtual Fields that joins to the tag name and tag slug.
The slug goes in the href and the name between the a tags.
It will be different for you. so you’ll need to click on the little blue ? icon to the top of the templates pane, you can see which code to use in that box. its the only one that doesn’t have a title. copy it and replace it in the example i gave above.I left of giving a title to the first virtual field so that you can see how they look untitled in the templates.
When done it should look like this:
Right, with that done,
Click on save and view your interface. You’re done!
Now you can go to your widgets page, drop in the DB-Toolkit widget, select your app “widgets” save, then select your interface and save again.
And that’s it. You should have a tags list like the one to the right in my side bar.