DB Query for Pages that use a Certain Fieldable Panel Pane (Drupal 7)

In Drupal 7, the DB structure of the panel-related tables has been hard to grasp (I am glad to see Drupal 8 cleaned up a lot of clutters and simplified its structure). If you look into it, it makes sense but it can be sketchy to figure something out. One of the things I looked into recently was to find node pages that use certain fieldable panel panes. Since a particular FFP creates some layout issues so that how many pages, I needed to search how many pages use it by querying DB directly. I’m on Pantheon; I can apply this query through a terminus + drush command.

$ terminus drush site.env -- sql-query "SELECT n.nid, ua.alias, sp.*, pp.pid, pp.did
FROM panelizer_entity pe
JOIN node n ON pe.entity_id = n.nid AND pe.entity_type = 'node' 
  AND n.vid = pe.revision_id
JOIN panels_pane pp ON pe.did = pp.did
JOIN (
  SELECT CONCAT('vuuid:', fv.vuuid) subtype, ff.fpid 
  FROM fieldable_panels_panes ff
  JOIN fieldable_panels_panes_revision fv 
    ON ff.vid = fv.vid AND ff.bundle = 'ffp name goes here..') sp 
  ON pp.subtype = sp.subtype
JOIN (
  SELECT alias, SUBSTRING(source, LOCATE('/', source)+1) entity_id 
  FROM url_alias WHERE source LIKE 'node%') ua
  ON n.nid = ua.entity_id
WHERE n.status = 1 
GROUP BY n.nid, pp.pid"

Drupal 8 search with Elasticsearch

• Install module
• Create an index on Elasticsearch engine
• Create a view
• Attach facet filters

The Search API module needs Elasticsearch PHP library which provides the abstract layer of Elasticsearch Connector module in Drupal. This can be installed through composer.

$ composer require nodespark/des-connector:5.x-dev

$ composer update

Add Elasticsearch

Go to Configuration > Search and metadata > Elasticsearch Connector.

Click “Add Cluster” and configure the Server.

 

Go to Configuration > Search and metadata > Elasticsearch Connector. Click “Add Cluster” and configure the server.

As default, it is “elasticsearch.” If you want to edit the cluster/node information, edit elasticsearch.yml file.

 

Go to  Configuration > Search and metadata > Search API.

Click “Add Index”

 

 

Selecting the “Content” data source, options are presented to select which bundles are to be indexed

 

Before search can be performed, select all the fields that should be available to search. That is configured in the “Fields” tab.

 

 

Last step is to add additional

’processors’.

This includes items such as:

• Content access
• Ignore case (case-insensitive search)
• Tokenizer (split into individual words)

 

Once fields and processors are set up, go back to

the ”View” tab. It will show the status of the index, and at this point, the content is ready to be indexed if not already set to index immediately when the index is created.

Indexing of content is done via cron and any new

content will get indexed then.

 

1. Go to Structure > Add view
2. Provide a view name and select your index name as the view source
3. Under Format > Show, select “Rendered Entity”

Or, you can select “Fields” and add each field you would like to display  in the Fields section.

4. Under Filter Criteria, add “Fulltext search” field and expose the field for filtering
5. Add Sort Criteria: The best one to use is “Relevance (desc)”

With the search page setup now, we want to add facets to let users filter down content. Navigate to Configuration > Search and metadata > Facets then click “Add facet”

 

Last step, place the newly created Facet blocks on the Block Layout page

 

 

• The Elastic Stack (Elasticsearch, Logstash, and Kibana) can interactively search, discover, and analyze to gain insights that improve the analysis of time-series data.
• No need for upfront schema definition. Schema can be defined per type for customization of indexing process.
• Has an edge in the cloud environment – this is depend upon SolrCloud advancement.
• Has advantages of search for enterprise or higher-ed level where analytics plays a bigger role.

Walking around Drupal 7 Views RSS Feeds channel image restrictions

I have been playing with Views RSS module (7.x). I found this is really robust and versatile to create a RSS feed easily from Drupal contents. I never worked with RSS before and it is good to pick up the W3 specifications and all.

One of the kinks (well, it is not really) I had was to follow the channel image specification: https://validator.w3.org/feed/docs/rss2.html#ltimagegtSubelementOfLtchannelgt

In there, it only allows:

• Maximum value for width is 144, default value is 88.
• Maximum value for height is 400, default value is 31.

However, I had to increase the image size for some reason. The Views RSS module conveniently has some hooks. By this, I could easily extend and custormize RSS feeds.

Hooks for defining new namespaces, <channel> and <item> elements, and date sources:

• hook_views_rss_namespaces()
• hook_views_rss_channel_elements()
• hook_views_rss_item_elements()
• hook_views_rss_date_sources()

Hooks for altering definitions provide:

• hook_views_rss_namespaces_alter(&$namespaces)
• hook_views_rss_channel_elements_alter(&$elements)
• hook_views_rss_item_elements_alter(&$elements)
• hook_views_rss_date_sources_alter(&$date_sources)

Hooks for processing admin configuration form:

• hook_views_rss_options_form_validate($form, &$form_state);
• hook_views_rss_options_form_submit($form, &$form_state);

In my case, it only takes two hook functions. In the modules:

/**
* Implements hook_views_rss_channel_elements().
*
* Add a new RSS Channel field for adding a new image
*/
function MODULE_views_rss_channel_elements() {
  $elements['custom_image'] = array(
'title' => t('My Custom Image'),
'description' => t('It replaces the above Core Image for the feed.
Please leave the Core Image field empty when inserting and updating.'),
);
  return $elements;
}

/**
* Implements hook_views_rss_options_form_validate().
*
* Walk around the RSS Channel image restriction (144 X 400)
*/
function MODULE_rss_options_form_validate($form, &$form_state) {
  if (!empty($form_state['values']['style_options']['channel']['core']['my_custom']['custom_image'])) {
  $form_state['values']['style_options']['channel']['core']['views_rss_core']['image'] =
  $form_state['values']['style_options']['channel']['core']['my_custom']['custom_image'];
  }
}

 

Getting a set of range records from Drupal 7 WebForm tables

Drupal 7 WebForm is a kind of a double edge sword just like any other Drupal contribution modules.

It creates a beautiful and fully functional web forms but makes customizing work a hard task. Of course it provides hooks but even those functions are not clearly defined some reasonings and other times it doesn’t provide things we need. I think the lack of support has a lot to do with the data structure of the WebForm; we already know it isn’t created by the best mind of structuring. I wouldn’t say this is a flaw but it is definitely not an advantage. Maybe that’s why there are some Drupal folks try to avoid WebForm. Maybe those flaws will go away when Drupal 8 rolls out; well, that’s beside of the point.

I used a WebForm because of the simplicity. Then later I faced a need bringing the data according to a predefined range. As you know, WebForm stores data vertically and bringing a record in horizontally isn’t a hard task if you’re familiar with SQL; however adding a range can be a challenge. Well, not quite if you understand WebForm is a content type. It means it is a node from Drupal point of view.

Here is an example:

/**
 * Queries and returns WebForm data with range
 * @param int $nid Webform Node Id
 * @param int $cid Webform field Id, the order of the webform field you created in webform_component table)
 * @return $result Returns WebForm data object or null if not found
 */
function get_results_with_range($nid, $cid) {
  // sub_data field collects other field information
  // so that group by sid is necessary
  // NOTE: sid is Serial Id of data
  $sql = "SELECT sd.sid, group_concat(IFNULL(sd.data, '') SEPARATOR ';') AS sub_data, rd.d_range, rd.d_order
	FROM {webform_submitted_data} AS sd
	JOIN (
		SELECT sid, data,
		CASE
			WHEN data >= 91 THEN 'Over 90'
			WHEN data >= 80 AND data <= 90 THEN '81 - 90'
			WHEN data >= 70 AND data <= 80 THEN '71 - 80'
			WHEN data >= 60 AND data <= 70 THEN '61 - 60'
			ELSE 'Under 60'
		END AS d_range,
		CASE
			WHEN data >= 91 THEN 1
			WHEN data >= 80 AND data <= 90 THEN 2
			WHEN data >= 70 AND data <= 80 THEN 3
			WHEN data >= 60 AND data <= 70 THEN 4
			ELSE 5
		END AS d_order
		FROM {webform_submitted_data}
		WHERE nid = :nid AND cid = :cid
		AND data != ''
	) AS rd
	ON sd.sid = rd.sid
	WHERE sd.nid = :nid
	GROUP BY sd.sid
	ORDER BY rd.d_order;";

	return db_query($sql, array(':nid' => $nid, ':cid' => $cid));
}

Displaying the results is pretty much intuitive. Here is an example:

/**
 * Getting html from WebForm data with range
 * @param object $result SQL results set
 * @return $html Returns html with a set of list
 */
function html_range_results($results) {
  $html = '';
  if ($results) {
    $old_range = '';
    $html .= '<ul>';
    foreach ($results as $row) {
      $sub_data = $row->sub_data;
      $new_range = $row->d_range;
      if (!empty($sub_data)) {
        // if range is different from the previous one
        if ($old_range != $new_range) {
          // taking care of html combination
          $html .= (empty($old_range) ? '' : '</ul>');
    	  // add a new range header
          $html .= '<h3>' . $new_range . '</h3>';
          $html .= '<ul>';
        }
        $data_array = explode(';', $sub_data);
        // let's say first field holds user name and we need to display it
        $user_name = $data_array[0];
        $html .= '<li>' . $user_name . '</li>';
      }
      $old_range = $new_range;
    }
    $html .= '</ul>';
  }
  return $html;
}