Skip to main content

Drupal 7 Views : Making a block of links to related pages

Views and SQL

Using Drupal views basically is trying to figure out how to trick the machine into making the SQL query I would have written in five minutes. As a matter of fact, I don’t think I had a chance of getting this right, hadn’t I known SQL pretty well.
Or, as one of the help pages was kind enough to clarify (the left side is merely examples):
SELECT n.title, u.name <–> fields
FROM {node} n base table <–> view type
INNER JOIN {users} u ON n.uid = u.uid <–> relationship
WHERE n.status = 1 <–> filter
AND u.uid = arg(1) <–> argument
ORDER BY n.changed DESC <–> sort
and I’ll add:
WHERE n.nid = p.nid <–> Contextual filter
GROUP BY <–> Aggregation
Strategy

The purpose: Making a “relevant pages” block, based upon common tags of the pages. Don’t tell me Drupal has something doing that, because I know. It’s just that I’ve learned that these easy solutions always end up with more work. Besides, I have a special thing I want.
I have to admit I was very tempted to just hack something up in PHP, but that’s also one of those shortcuts one pays for down the road.
The setting is as follows: I have a special taxonomy named “relevance terms”. Pages with common terms in this taxonomy are considered to have similar content. I didn’t use the original tags, because I may want to use them for something representative.
Also, each content page has an extra field “LinkText”, which contains the text to appear on links to the page. For example, the download page’s title is simply “Download” but the link to this page should say something more imperative.
The immediate (and wrong) way to go is to make a view of content titles. Without any filtering, you get all content pages. So use the context filter to get only the current page, and use relations to list all taxonomy terms. Now another relation filter to expand this to pages for each taxonomy term? But the context filter kills any other page than the currently displayed. It’s a dead end.
So the right way is to make a view of the page’s taxonomy terms. For each term, list the pages using it, and then squash duplicates. And then make the output nice. Easier said than done.
First steps

Add a new View, showing Taxonomy Terms of type Relevance Taxonomy. Don’t create a page, but a block. Display as an HTML list of fields. Save & Exit, and insert the block somewhere in the page, so it can be tested. Previews won’t work here, because it runs on Taxonomy nodes, not pages. Set title and such.
The number of items should be limited, and I don’t use a pager.
Contextual Filter & Relationship

This is done first, so we don’t mess with aggregation, which is going to be set up pretty soon. Under Advanced, add a contextual filter on “Taxonomy Term ID”. The following window will complain that there’s no source for contextual filter, so a default must be supplied. This is because we’re running in block context. The source is taken from the page.
We want the node ID to be compared with the current page, so pick “provide default value” and “Taxonomy term ID from URL”. Uncheck “Load default filter from term page” but check “Load default page from node page” and also “Limit terms by vocabulary” and pick the Relevance Taxonomy as the chosen vocabulary. Under “More” check “Allow multiple values” This is necessary, since we don’t want just the first term to be used. I’m not sure if this item appears without setting up relationships. So if it’s missing, set up a relationship and come back to add this.
That’s it. Save and check up. We should now have a simple list of relevance terms in the view.
Next we add a relationship with the pages having the terms: Check “Taxonomy term: Content using relevance” (note that “relevance” is the name of the vocabulary here), check “Require this relationship” on the next screen (I suppose this makes an INNER JOIN as opposed to a LEFT JOIN), and save this.
Checking where we stand, we have each taxonomy term appearing a number of times. This is the natural behaviour of an inner join: Each combinations of terms and pages using them creates a line. Since the pages aren’t listed, we just see each term repeated.
And since we’re at it, let’s eliminate the shown page’s entry in the related pages’ list. We need negative contextual filtering here: So add a new contextual filter, check “Content: Nid” (it wasn’t there until we added the relationship). Provide default value as “Content ID of URL”, and under “More” check “Exclude”. So if the current page matches the shown page, it’s not shown.
Save and verify than one or a few items have disappeared from the list.
Aggregation

Aggregation is “GROUP BY” in SQL, meaning that several rows with the same value in one of the fields are turned into a single row. Instead of the field’s value we have the count of rows grouped together or the maximum, minimum, average value or whatever the database allows. Aggregation is needed to eliminate the duplicate rows created by the relationship (that is, the inner join).
True, there is a “Distinct” checkbox under “Query settings” but it’s ineffective, since each of these duplicate rows are indeed distinct when the database answers the query. What makes them duplicate is the fact that the taxonomy term is dropped in the display. “Distinct” just adds the DISTINCT word to the SQL query.
So at this point, change “Use aggregation” to Yes. Things will get slightly messier from this point on.
Adding fields

Rule #1 for fields: The order they appear matters. In particular when using rewrite rules: Inserting data from other fields in substitution patterns works only for fields above the displayed one (those declared before).
Remember that the goal is to show the Linktext field as a link to the page, and not just the title.
So the first field to add is the Node’s path (aliased link). We will use it later on. In the list, check “Content: Path”. Under “Aggregation type” pick “Group results together” which is what we pick all the time if not for any special reason. This choice wouldn’t appear without enabling aggregation, of course. On the third and last window, check “Exclude from display” unless you want to see it for debugging.
The second field to add is the link text. In the list, check “Content: LinkText”. Under “Aggregation type” pick “Group results together” and pick the “Entity ID” as group column, and no additional ones.
On the third page uncheck “Create a label” (no “LinkText:” shown to user). Under “Rewrite results” check “Output this field as a link”. Write [path] in the Link path text box. This string can be found in the Replacement Pattern list just below. The path was there because it was defined before the current field.
Check “Use absolute path” or the links start with a double-slash and render useless.
At this point I’ll mention that it’s possible to insert arbitrary HTML with replacement patterns. So it’s really useful.
Squashing duplicates

At this point it’s pretty evident that we have double entries, and these taxonomy terms should be removed.
So it’s time to edit the first field: The “Taxonomy Term: Name” and check “Exclude from Display”. But even more important, enter “Aggregation settings” and change “Aggregation type” to “Count”. The magic is that instead of a row for each taxonomy term, we get a single row with the number of them, ending up with a single row for each link.
Filter out inaccessible items

As is, users will see links to items the user can’t access. So let’s add a simple filter (“Filter Criteria”). Pick “Content: Published or admin” and “Group results together” in the two following menus. And then just apply on the next menu. Done.
Note that unpublished items will still appear for admins, since the criterion is access. Pick “Content: Published” and choose “Yes” to remove unpublished items for anyone.
Sorting

I have to admit that I failed on this one. My intention was to sort the output depending on the number of mutual tags. That would be easy in SQL, since the COUNT() can be applied a label with the AS keyword. I found nothing to assist this in the Views menus.
Then I thought about using the taxonomies’ weight as a sorting criterion. The problem was that if I inserted Weights as another field, I got multiple rows again (this is not expected SQL behaviour). Grouping it by looking for average doesn’t help, because there is no access to the average in the sort subsystem. Finally, using aggregation in sorting resulted in the sort announcing a broken/missing handler and doing no sort at all.
Searching for solutions on the web, it looks like this requires some PHP hacking. Not yet.
So no sorting for me.

Comments

Popular posts from this blog

Shell Script to check tomcat status and restart

The below script checks the status of a particular tomcat status and restarts it if the tomcat does not respond....TOMCAT_HOME=/usr/local/tomcat-folder/

is_Running ()
{

        wget -O - http://yourserver.com/ >& /dev/null
 if( test $? -eq 0 ) then
  return 0
 else
  return 1
 fi
}


kill_Hanged_Processes ()
{
 echo "killing hanged processes......"
 javaProcs=`ps -efl| grep -v grep | grep java`
 if(test ! -z "$javaProcs") then
  echo "nonzero"
  processId=`echo $javaProcs | awk '{ print $2} '`
  echo "$processId"
  kill -9 $processId
 fi
}
stop_Tomcat ()
{
 echo "shutting down......"
 $TOMCAT_HOME/bin/shutdown.sh
}

start_Tomcat ()
{
 echo "starting......"
 $TOMCAT_HOME/bin/startup.sh
}

restart ()
{
 stop_Tomcat
 sleep 10
 kill_Hanged_Processes
 start_Tomcat
 sleep 60
}

send_Mail ()
{
#!/bin/bash
# script to send simple email
# email subject
SUBJECT="Telegraphindia.com went down"
# Email To ?
EMAIL="mailid1@gmail.com,mailid2@gmail.com"
# Em…

Getting access to menu from portlet in liferay

The below code describes the way of accessing menu items from the portlet. Here i have called it from jsp. The best thing about this is you can use the beautiful permission management of menu items of liferay as well without bothering about that. Writing services for fetching menu names from the layout tables will need more codes for permission managements......

Hope this helps.........


<%--
/**
* Copyright (c) 2000-2010 Liferay, Inc. All rights reserved.
*
* This library is free software; you can redistribute it and/or modify it under
* the terms of the GNU Lesser General Public License as published by the Free
* Software Foundation; either version 2.1 of the License, or (at your option)
* any later version.
*
* This library is distributed in the hope that it will be useful, but WITHOUT
* ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
* FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more
* details.
*/
--%>
<%@ page import="com.li…

Virus f Opyum Team

Symptoms :: 
Several processes running named f or i. 
Network choked
Apache Dead
Unknown entry  * * * * * root f Opyum Team in crontab

Investigate ::
Try to kill the f process and even root will be unable to kill it. Check file named f and i in /etc or /bin or /usr/sbin directory. Try to delete, the file f  will not delete.  Some unknown .jpg files will be there in the same folder. 

Solve :: The root is unable to delete the file f because of the immutable bit in f
To remove it use ses or chattr
Follow the below steps to remove it completely.....



remove the unknown .jpg files in system folders

#top
(kill process f) option k

#ses -i /bin/f
#rm /bin/f
#ses -i /etc/crontab
#vi /etc/crontab (and delete last line)
#reboot


Precautions ::  In my case the server was hacked bu Brute-force attack
So set a good password with a combination of upper,lower case letters and special characters.

Post your feedback or any issues you may face removing the virus.  Very less number of good threads are there explaining …