Faceted Search (Layered Search) on MySQL database with example.

Last few day I was searching for the layered search using MySQL. Layered Search is actually a Faceted Search. (Magento, a highly professional e-commerce platform on php name it Layered Search). Faceted Search can be done using two way MySQL/Any DB Application or using Apache Solr.

In this post I will show you how we can do Faceted search using MySQL database. You need a specific database schema, but it’s feasible. Here’s a simple example:

product Table

+----+------------+
| id | name       |
+----+------------+
|  1 | blue paint |
|  2 | red paint  |
+----+------------+

classification Table

+----+----------+
| id | name     |
+----+----------+
|  1 | color    |
|  2 | material |
|  3 | dept     |
+----+----------+

product_classification Table

+------------+-------------------+-------+
| product_id | classification_id | value |
+------------+-------------------+-------+
|          1 |                 1 | blue  |
|          1 |                 2 | latex |
|          1 |                 3 | paint |
|          1 |                 3 | home  |
|          2 |                 1 | red   |
|          2 |                 2 | latex |
|          2 |                 3 | paint |
|          2 |                 3 | home  |
+------------+-------------------+-------+

So, say someones search for paint, you’d do something like:

SELECT p.* FROM product p WHERE name LIKE '%paint%';

This would return both entries from the product table.

Once your search has executed, you can fetch the associated facets (filters) of your result using a query like this one:

SELECT c.id, c.name, pc.value FROM product p
   LEFT JOIN product_classification pc ON pc.product_id = p.id
   LEFT JOIN classification c ON c.id = pc.classification_id
WHERE p.name LIKE '%paint%'
GROUP BY c.id, pc.value
ORDER BY c.id;

This’ll give you something like:

+------+----------+-------+
| id   | name     | value |
+------+----------+-------+
|    1 | color    | blue  |
|    1 | color    | red   |
|    2 | material | latex |
|    3 | dept     | home  |
|    3 | dept     | paint |
+------+----------+-------+

So, in your result set, you know that there are products whose color are blue and red, that the only material it’s made from is latex, and that it can be found in departments home and paint.

Once a user select a facet, just modify the original search query:

SELECT p.* FROM product p
   LEFT JOIN product_classification pc ON pc.product_id = p.id
WHERE 
   p.name LIKE '%paint%' AND (
      (pc.classification_id = 1 AND pc.value = 'blue') OR
      (pc.classification_id = 3 AND pc.value = 'home')
   )
GROUP BY p.id
HAVING COUNT(p.id) = 2;

So, here the user is searching for keyword paint, and includes two facets: facet blue for color, andhome for department. This’ll give you:

+----+------------+
| id | name       |
+----+------------+
|  1 | blue paint |
+----+------------+

So, in conclusion. Although it’s available out-of-the-box in Solr, it’s possible to implement it in SQL fairly easily.

 

Thanks stackoverflow.com

Adding New Fields to the attachment of wordpress

Recently I was developing plugin for image features. I have to add some fields to the attachment window of wordpress or I can say in Add media box.

We can achieve this by using two hooks of wordpress.
“attachment_fields_to_edit” and “attachment_fields_to_save”;

attachment_fields_to_edit

function get_attachment_fields_to_edit($post,$errors=null){
 // Some Code
 $form_fields = apply_filters("attachment_fields_to_edit", $form_fields, $post);
 // Some Code
 }
  • $form_fields is a special array which will be described in detail in a moment.
  • $post is the attachment as an object (attachments are treated as post objects in WordPress).

attachment_fields_to_save

functionmedia_upload_form_handler(){
	//Some Code
	$post=apply_filters("attachment_fields_to_save",$post,$attachment);
	//Some Code
}
  • $post is the attachment as an array (attachments are treated as post objects in WordPress).
  • $attachment is the attachment part of the form $_POST which will include the fields setup through the attachment_fields_to_edit hook.

Note: Be careful in your code, as $post is sent to one function as an object and to the other as an array.

We will add this fields information to Custom Fields.

How to add this Fields to Custom Fields for Working is Properly

The new fields being added will be saved as post meta, just like the custom fields section of the post/page edit screen. Fields prefixed with an underscore (_my_custom_field) will not be listed in the drop down of available custom fields on the post/page screen; all other existing post meta fields will be listed. We can use this knowledge to hide the fields we’re adding to the media form, since they aren’t relevant for posts/pages.

There is a similar rule to keep in mind when choosing the $form_fields array key to use for your new field. Here, if you use an underscore ($form_fields[‘_my_custom_field’]) your field will be skipped and will not be added to the form.

So in order to show our fields in the media form, but also not list them in the page/post custom fields drop down, we must combine both methods. This will invlove both the edit and save functions we’ll be creating. For the ‘attachment_fields_to_edit‘ hook we’ll set the $form_fields keys up to not have underscore prefixes, and for the ‘attachment_fields_to_save‘ hook we’ll prefix our fields with an underscore before saving them as post meta. This is a workaround worth doing in order to not muddy our users’ interface with unneeded info.

Hook 1: attachment_fields_to_edit

<?php
/**
 * Adding our custom fields to the $form_fields array
 *
 * @param array $form_fields
 * @param object $post
 * @return array
 */
function my_image_attachment_fields_to_edit($form_fields, $post) {
	// $form_fields is a special array of fields to include in the attachment form
	// $post is the attachment record in the database
	// $post->post_type == 'attachment'
	// (attachments are treated as posts in WordPress)
	// add our custom field to the $form_fields array
	// input type="text" name/id="attachments[$attachment->ID][custom1]"
	$form_fields["custom1"] = array(
	"label" => __("Custom Text Field"),
	"input" => "text", // this is default if "input" is omitted
	"value" => get_post_meta($post->ID, "_custom1", true)
	);
	// if you will be adding error messages for your field,
	// then in order to not overwrite them, as they are pre-attached
	// to this array, you would need to set the field up like this:
	$form_fields["custom1"]["label"] = __("Custom Text Field");
	$form_fields["custom1"]["input"] = "text";
	$form_fields["custom1"]["value"] = get_post_meta($post->ID, "_custom1", true);
	return $form_fields;
}
// attach our function to the correct hook
add_filter("attachment_fields_to_edit", "my_image_attachment_fields_to_edit", null, 2);

The $form_fields array has several options for including different types of inputs and custom content. I’ve compiled the various methods below with notes and screenshots of how they render in the form.

Text Input

// input type="text"
$form_fields["custom1"]["label"] = __("Custom Text Field");
$form_fields["custom1"]["input"] = "text"; // this is default if "input" is omitted
$form_fields["custom1"]["value"] = get_post_meta($post->ID, "_custom1", true);

Hook 2: attachment_fields_to_save

Saving your custom fields is a much simpler process than adding them to the form; just check if your field is set and update its value as post meta. Remeber to prefix your field with an underscore when saving to hide it on the post/page edit screen.

/**
 * @param array $post
 * @param array $attachment
 * @return array
 */
function my_image_attachment_fields_to_save($post, $attachment) {
 // $attachment part of the form $_POST ($_POST[attachments][postID])
 // $post attachments wp post array - will be saved after returned
 // $post['post_type'] == 'attachment'
 if( isset($attachment['my_field']) ){
 // update_post_meta(postID, meta_key, meta_value);
 update_post_meta($post['ID'], '_my_field', $attachment['my_field']);
 }
 return $post;
}

You can also add errors here that will automatically be displayed below your field in the form. The$post['errors'] array gets merged with the $form_fields array before being sent through theattachment_fields_to_edit hook.

/**
 * @param array $post
 * @param array $attachment
 * @return array
 */
function my_image_attachment_fields_to_save($post, $attachment) {
 if( isset($attachment['my_field']) ){
 if( trim($attachment['my_field']) == '' ){
 // adding our custom error
 $post['errors']['my_field']['errors'][] = __('Error text here.');
 }else{
 update_post_meta($post['ID'], 'my_field', $attachment['my_field']);
 }
 }
 return $post;
}

Thanks to http://net.tutsplus.com

I have posted here for my future reference.