Solving the country/state problem with CakePHP and jQuery

I’d say that it is a pretty common issue (and maybe not quite “a problem”), where you would have a page with a billing form, which in turn, has a “Country” select as well as a “State” select.
Obviously not all countries have states, and thus comes the question of how to handle the situation.

Before going much further, I’d like to say that the whole point of the post is to show a solution to the common problem, but by no means it is the de facto standard of handling the form fields (as a matter of fact, I’ll say that there simply isn’t one).
If you consider every billing form that you’ve had to fill out in your internet life, each one, most likely, behaved differently in some way from the others.
Country, for which the application is purposed, and thus the target audience, not to mention the specific business needs of the application should guide the actual implementation.

For this example, we’ll do something like this:

  1. If selected country is “US” show the select input with the states
  2. For any other country change the select to a text input (allowing the user to enter what they please, if required)

Hmmm… a seemingly simple solution quite often requires a little bit of hackery. Or does it?
For security (or browser-related) reasons you cannot change an input type on the fly, otherwise it would be a perfectly reasonable thing to do.

… So how, after all, do we tackle this with cake and jQuery, without hacking around the problem?

First let’s consider this little snippet of a CakePHP form (view):

<div id="state-wrapper">
  <?php echo $this->Form->input('state', array('id' => 'state-select')); ?>
  <?php echo $this->Form->input('state', array('type' => 'text', 'id' => 'state-text')); ?>
</div>

As you see we have two nearly identical inputs. (Please note the different DOM id’s for later).
Also, you can guess which one is the select and which one will be a text input ;)

Now comes the fun jQuery code:

$(document).ready(function()  {
  stateText = $('#state-text').detach();
  stateSelect = $('#state-select').detach();

  checkSelected(stateText, stateSelect);

  $('#country-select').change(function() {
    checkSelected(stateText, stateSelect);
  });
});

function checkSelected(stateText, stateSelect) {
  if ($('#country-select').val() != 'US') {
    stateText.appendTo('#state-wrapper');
    stateSelect.detach();
  } else if ($('#country-select').val() == 'US') {
    stateSelect.appendTo('#state-wrapper');
    stateText.detach();
  }
}

Let’s take a closer look at this…
Upon the initial load of the page we detach() both the select and text inputs from the DOM.

First of all… what is detach()?
Yet, another beauty of jQuery, which was introduced sometime in 1.4.
The detach() method actually removes the element from the DOM… but (unlike a more common remove()) it keeps all the element’s properties, data, associated jQuery events and everything else that comes with it. This allows us to later inject the “detached” element, as though it never “left”, back into the DOM.

So, once both elements are “detached”, we go ahead and check the currently selected country.
Here, the code should be pretty simple:

  1. If country does not equal “US”, show (via appendTo()) the text input
  2. Otherwise, show the select input (with all the pre-loaded states, as they were before the “detachment”).

Ah, The power of jQuery.

p.s. A more elegant solution would be to keep a list of states/provinces/regions/etc. for alll countries, which require one.
With that we could populate the state select via AJAX once such country is selected… or remove the field altogether. Surely this option would provide more consistency and data integrity, but introduce a bit of maintenance overhead. However, I’ve already showed this approach in an older post, so we’ll just leave things a little different and simpler this time.

Self-adjusting credit card expiration year

Once in a while, it happens, that you need to build a payment form with credit card expiration year as a select input.

With CakePHP’s form helper it’s pretty easy, but what’s even nicer is that you don’t have to hard-code any values for the actual years (and then have to remember to update them as the time goes on).

With the little snippet below we build a select input which starts and defaults to the current year and goes up to seven years ahead.

(So at the time of writing the select input range is: 2010 – 2017)

$this->Form->input('card_expiration_year', array(
                           'type' => 'date',
                           'maxYear' => date('Y', strtotime('+ 7 years')),
                           'minYear' => date('Y'),
                           'dateFormat' => 'Y',
                           'default' => date('Y')));

p.s. Why seven years? (Not sure, just like the number).

Make sure your app runs within the specified time zone…

Update: 8/2/2010 As red pointed out in the recent versions of cake this is handled through core.php:
http://github.com/cakephp/cakephp/blob/master/app/config/core.php#L247

For those stuck with the earlier versions of cake, but newer version of PHP (5.3+)… please see below:

Even with a simple shared hosting, the server time zone might differ from the time zone where you do business in, yet this becomes more important if you have multiple servers (perhaps even geo-distributed).

If you rely on fields like created and modified to do some basic reporting… or maybe you simply don’t wish to add/subtract server time difference against your region of business, the following one-liner will solve all your trouble.

In bootstrap.php:

date_default_timezone_set('America/New_York');

This is what I have in mine, since we are running on the Eastern Standard US time.
Of course, you’d set the zone per your specific requirement.

Key/value tables and how to use them in CakePHP 1.3

The key/value tables are a nice a approach to database modeling, when we need to store some arbitrary data about another model.

For example, let’s take a User model.
We could create a single users table to hold all of the potential data or even create an additional table such as user_details with all additional fields, which “might” be needed.

However, what happens when we don’t exactly know how much or what data will be eventually required by the application?

  • How many phones does the user have? Should we create a table with fields phone1, phone2, phone3, etc.?
  • What about emails? (Some users have 1 some have 5)
  • What happens if the business later on decides to ask for a fax number?.. and person’s height or favorite color?

If we attempt to predefine all the place-holders (columns or fields in a table) for the potential data, the table can grow horizontally into an unmanageable monster.

This is the perfect time to enter key/value tables.

Let’s consider our User model and, therefore the users table:

CREATE TABLE `users` (
  `id` char(36) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `username` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `password` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The table couldn’t be simpler. All we’ll store here is the user’s login information. The rest will be handled by the user_details table…
It’s worth to mention that our models will have a pretty standard association:
User hasMany UserDetail and on the flip side UserDetail belongsTo User

Alright, next, we have the user_details table:

CREATE TABLE `user_details` (
  `id` char(36) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `user_id` char(36) COLLATE utf8_unicode_ci DEFAULT NULL,
  `field` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `value` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The columns field and value will actually store the user details. Because this table is going to grow vertically, potentially we can store an unlimited and completely arbitrary information about a user. As a matter of fact two different parts of your app can handle storing the user details completely independent of each other. For example, when a user plays the role of a “Guest” versus a user who represents a “Member”, or better yet… a user who is not event a human being, but rather a corporate entity, in which case typical fields like first_name and last_name, may become completely irrelevant.

Hopefully you can start seeing how the key/value approach can help our modeling be a lot more flexible.

… but let’s look closer at some details… how do we deal with this in cake-like-ways?

Actually, it is not that different from what you are already accustomed to.

Let’s see our UsersController:

class UsersController extends AppController {

  public function add () {
    if(!empty($this->data)) {
      $this->User->saveAll($this->data, array('validate' => 'first'));
    }
  }

}

Nothing unusual so far…

Next comes our add.ctp view:

echo $this->Form->create('User');
echo $this->Form->input('User.username');
echo $this->Form->input('UserDetail.0.dob');
echo $this->Form->input('UserDetail.1.gender', array(
                        'type' => 'radio',
                        'legend' => FALSE,
                        'options' => array(
                                  'M' => 'M',
                                  'F' => 'F'
                       )));
echo $this->Form->input('UserDetail.2.phone');
echo $this->Form->input('UserDetail.3.email');
echo $this->Form->end('Save');

Nothing unusual so far, yet again.
We are going to save a User with some username as well as some User details: date of birth, gender, phone and an email.
Thinking back, once the save happens, our user_details table will have four records for a single user ID.

Let’s see the models then:

User:

class User extends AppModel {
  public $hasMany = array('UserDetail');

  public $validate = array(
    'username' => array(
        'rule' => 'isUnique',
        'message' => 'Username already exists'
    )
  );
}

UserDetail:

class UserDetail extends AppModel {
  public $belongsTo = array('User');

  public $validate = array(
     'dob' => array(
        'rule' => 'notEmpty',
        'message' => 'Please select a date of birth'
     ),
     'gender' => array(
        'rule' => 'notEmpty',
        'message' => 'Please select a gender'
     ),
     'phone' => array(
        'rule' => 'phone',
        'message' => 'Please enter a phone'
     ),
     'email' => array(
        'rule' => 'email',
        'message' => 'Please enter a valid email'
     )
  );

  public function beforeSave() {
     foreach($this->data[$this->alias] as $field => $value) {
        if($field !== 'user_id') {
          $this->data[$this->alias]['field'] = $field;
          $this->data[$this->alias]['value'] = $value;
        }
     }
     return true;
  }
}

So the validation is handled “as always”.

The only little bit of trickery is in our beforeSave() method…
Here we take our existing fields from the data array (dob, gender, phone…), and convert them to the expected key/value pair (or field/value based on the column names). We are skipping the user_id which is automatically injected by saveAll() just as needed.

Well, this should be a good starting point…

The more extravagant example would be showing the ability to store validation rules and field types in the DB as well. This is helpful if you would like an admin back-end to manage the requirements for user details. As of this example, we’d have to manually adjust the views and validation rules to allow for any new fields. If we store the rules (as serialized array for example) as well as field type(s), the form building and validation would be done on the fly and would allow business users to manage the specifics of the required data.

… but that’s a topic for another day.

… moving on …

I hope the move to a new server (aka wordpress.com to wordpress.org) is complete.

Still need to do a little house keeping, but please let me know if I’ve totally messed it up… (or whatever you find).

Thanks.

Forcing cake to use "dashed" HTML DOM ID’s

Why bother?

I guess just to keep CSS purists happy, we should have our DOM id’s appear as “some-field-id” rather than CakePHP’s default “SomeFieldId”.
(Also, it’s just a matter of consistency and most certainly personal preference).
There is no defined rule for this, but in overwhelming CSS examples and tutorials it is quite common to see “dashed” names rather than camel cased.

Thanks to a tip from Mark Story, we can override the default behavior by creating an app_helper.php with the following method:

public function domId($options = null, $id = 'id') {
  $view =& ClassRegistry::getObject('view');
  if (is_array($options) && array_key_exists($id, $options) && $options[$id] === null) {
    unset($options[$id]);
    return $options;
  } elseif (!is_array($options) && $options !== null) {
    $this->setEntity($options);
    return $this->domId();
  }

  $entity = $view->entity();
  $model = array_shift($entity);
  $replacement = array($model .' '. implode('-', $entity));
  $dom = $model . join('', array_map(array('Inflector', 'camelize'), $entity));
  $dom =  preg_replace('/(?<=[^A-Z])([A-Z])/', ' $1', $replacement);
  $dom = strtolower(implode('-', Inflector::slug($dom, '-')));
  if (is_array($options) && !array_key_exists($id, $options)) {
    $options[$id] = $dom;
  } elseif ($options === null) {
    return $dom;
  }

  return $options;
}

This overrides Helper::domId() and gives us nice “model-field-name” ID’s with dashes rather than “CamelCase”.
The actual change happens in lines 13 – 16 (if you care), the rest of the method remains the same as the core.

Important database session limitation

If you are using a database to keep your sessions, there is one rather significant limitation which you should be aware of.

It does depend on the specifics of your DB, but I am going to guess that in most cases it is something that could happen to any database…

For example, we were using MySQL to keep the session data.
Looking at the table you’ll see that session data is serialized and stored in the “data” field…

By default that field is set to MySQL’s “TEXT” type, which has a size limitation of ~ 65K.

I know, I know… why would you want sessions larger than 65K?
Well…

“640k ought to be enough for anybody”

So, just in case, you do need a larger session the remedy is pretty simple, change the field type to “MEDIUMTEXT”, which effectively gives you ~ 16MB.

p.s. If you need even a larger size you can go with “LONGTEXT”.

For your reference, here’s the semi-official breakdown of different text-type fields in MySQL and their limitations:

TEXT
A BLOB or TEXT column with a maximum length of 65535 (2^16 – 1) characters

MEDIUMTEXT
A BLOB or TEXT column with a maximum length of 16777215 (2^24 – 1) characters

LONGTEXT
A BLOB or TEXT column with a maximum length of 4294967295 (2^32 – 1) characters

Couple of convenience methods to add to your bootstrap.php

Although most of the convenience methods will be removed from CakePHP going forward (see the note about basics.php), and I say good riddance, you still have the opportunity to add your own to bootstrap.php.

For example a couple that I like are wrappers for var_dump(). The main reason being is that var_dump() will properly display NULL and FALSE. Unlike debug() and pr() (alias to) print_r(), which will show… well… nothing.

So here you go:

function vd($var) {
  if(Configure::read() > 0) {
    echo '<pre>';
      echo var_dump($var);
    echo '</pre>';
  }
}

Or, if you’d like to die() out:

function vdd($var) {
  if(Configure::read() > 0) {
      die(var_dump($var));
  }
}

Putting semi-RESTful API development to… rest

With some of the older web API standards subsiding, such as SOAP based interfaces, the new buzz seems to be all about REST (for the last few years at least).
Not that SOAP is “bad” in any way, as a matter of fact self-discovery via WSDL, and the standard itself is quite beneficial in many cases. It’s just that… programmers are notoriously lazy, and bulkiness of deploying a SOAP-like API makes most of us cringe.

So, let’s take a deeper look at REST for a second.

I think one of the most important principles behind the REST, is the fact that:

… [it] depends on HTTP headers for direction.

What that simply means is that a server, which exposes its API and expects a RESTful request, should rely on the HTTP headers (sent by the client) to make certain assumptions on what exactly the client is expecting to do (or acquire from the server).

CakePHP already does and excellent job of handling this issue for you. Let’s take a look at the mapping table from cake’s manual. (The table represents a specific HTTP method being used to send a request, and how it would be mapped, or actually expected to be mapped to a CakePHP action is some controller).
http://book.cakephp.org/view/1239/The-Simple-Setup

HTTP Method URL.method Controller action invoked
GET /recipes.method RecipesController::index()
GET /recipes/123.method RecipesController::view(123)
POST /recipes.method RecipesController::add()
PUT /recipes/123.method RecipesController::edit(123)
DELETE /recipes/123.method RecipesController::delete(123)
POST /recipes/123.method RecipesController::edit(123)

This is just my opinion, but while the REST ideology makes a lot of sense, it doesn’t always work out exactly as intended in the real world. That being said, REST is not meant to be a rule or a standard, rather it is a great set of suggestions on how to make your web app communicate efficiently with third-parties without much effort.

I guess, this is the point where I should explain the “semi-RESTful” part of the article…

According to the REST manifest (and as seen from the above mapping table), a POST action should be sent when any sort of data is supposed to be added (…again, as seen above, CakePHP will handle it perfectly well for you, out of the box).

To cut to the chase, a RESTful API will provide an opportunity for another web app to access yours and perform certain tasks.
As an example, some partner site might wish to send over the user credentials in order to gain certain information about the user attempting log-in.

Surely, issuing a GET request with such sensitive information wouldn’t be acceptable at all. Based on the above requirements we would have to send the data as POST (preferably over HTTPS). And thus our API deviates a bit from the REST approach, but… only slightly so.

… let’s keep this in mind and switch gears for a second.

Setting up a simple RESTful API in CakePHP will only take you a few minutes. (By following the manual and quite a few great tutorials out there).

Let’s see how to set things up when we need to stray away from the basics a little.

To illustrate a pseudo-real-world example, let’s consider the scenario mentioned above.
(A partner site is going to log-in into your application and get some data for the user).

I am going to do things a little backwards and present the following method for your consideration:
(this is going to be a part of our Users Controller):


public function api_info() {

Configure::write('debug', 0);

$result = array('response' => $this->User->find('first',
array(
 'conditions' => array(
   'User.username' => $_POST['username'],
   'User.password' => $_POST['password']
),
 'fields' => array(
   'id', 'first_name', 'last_name', 'email',  'phone', 'dob', 'stats'
),
 'recursive' => -1,
 'callbacks' => FALSE
)));

if($result['response'] === FALSE) {
  $result = array('response' => 'no user found');
}

$this->set(compact('result'));
}

So the idea, is quite simple here. Some remote application will access the given method with the POST’ed credentials and receive back some information about a user (as provided in our fields key)… agreeably so, in an XML format.

Here’s what would happen in real life:

1. Do a form post with username and password, as seen above…
2. To the following URL: https://www.example.com/api/users/info.xml
3. Get back the information about the user (or receive a “no user found” response) if the credentials are wrong.

(There are a few ways to make such access more secure, via IP restriction for example, but I don’t feel that is extremely relevant at this point. Just something to keep in mind).

Technically all we would need at this point is a view and the following code added to the method:
$this->RequestHandler->respondAs('xml');
The RequestHandler component, in theory and according to the manual, would set the proper headers and provide a response, as expected in the XML format.
Well, for the life of me, I could not get this this to work… i.e. RequestHandler would not set the headers properly, thus a well formed XML string would be returned, as expected, but without telling the requesting application that it is an XML response, indeed (the magic is in the HTTP header).
(I hope someone can give me a guiding light…)

Nevertheless, this is not something to cry over as we can still fulfill our requirements, albeit in the “not-so-perfect-cake-way”.

Let’s take a look at the rest of the setup to make things a little more clear…

In order to respond with XML we will need to create the following structure in our views:
app/views/users/xml/api_info.ctp

See, the xml directory under our standard app/views/users will hold the view, which will be used to send the XML response back to the requestor. And, as you can see, the actual view api_info.ctp will match the action name, as usual.
If we had to send a JSON response the following structure would be needed:
app/views/users/json/api_info.ctp…. now, that everything is coming together let’s take a look at the view (api_info.ctp).

<?php
if(!empty($result)) {
  echo '<?xml version="1.0" encoding="UTF-8"?>';
  echo $this->Xml->serialize($result, array('format' => 'tags'));
}
?>

I am not going to dwell on the details here, but basically we’ve got our $result from the controller and now present one as XML in the view. (I surely hope that the code above is quite easy to grasp).

Now, remember my complaint about the XML headers above?

Here is the necessary remedy… in the layouts we do have to create the following:
app/views/layouts/xml/api.ctp
… and here it is in all its glory:

<?php
  header ("content-type: text/xml");
  echo $content_for_layout;
?>

As mentioned, I’ve abandoned my failed attempts with RequestHandler and set the header manually in the layout. (Not much harm done).

The next two steps, to complete our semi-RESTful API are as follows:

1. Enable parsing of extenstions in the routes.php file: Router::parseExtensions();
2. And… to use the proper prefix routing, i.e. transform the api_info() method into a cake-accessible URL such as outlined above: https://www.example.com/api/users/info.xml… we’ll need yet another addition to the routes.php file:
Router::connect('/:controller/:id', array('prefix' => 'api', 'action' => 'info', '[method]' => 'POST'), array('id' => '[0-9]+'));. (I believe this neat little trick was “inspired” by cakebaker, so please award the required props to him).

Hooray! We are done now. To further extend your application and open up certain functionality to some third-party apps,
all that needs to be done now is to create a few, api_some_methods() and appropriate views.

Let's talk about the “Search”…

Not sure how to get this post started exactly, but let me first say that: “I am not a Doctor… Err… Search Engine specialist”…

Now, I think, it is an interesting topic that a few people might find helpful and perhaps (and hopefully) some could even provide a little further insight in the comments as my dear readers often do…

So does your app need a search feature?.. More than often — it does.
(Do I need to mention that there are about 92308.6147 solutions that exist out there?)

I am going to go over some of the findings, headaches and success we’ve had while implementing a super-cool-and-robust search feature.

However, before proceeding, let’s take a few things into consideration:

  1. The search feature has to be fast and flexible; a simple “LIKE” is not going to cut it in this case.
    If this is beyond your current needs you might stop reading here :)
  2. MySQL’s, MyISAM engine has a lovely full-text search capability, but it is lacking in some areas. (Unfortunately too many details to list, but they are out there).
  3. MySQL’s, InnoDB does not have a lovely full-text search capability, but it does offer better performance (i.e. no table locks) and is the only supported option (at least at the time of this writing) on Amazon’s RDS.
  4. No need to try and beat Google at this game.

Given all the potential options, as always, it is extremely important to use the right tool for the right job. (So anything below may be a guiding light for your project or a complete dead-end, but at least, and hopefully, you’ll get to that realization sooner than later).

Let’s plow on…

I should share some of the high-level specifics of the app where the super-duper search engine was required.

In general, it is a rather active (yet simple) forum application (CakePHP, of course) with over a million comments, and with a new post or comment coming in approximately every few seconds or so (Decent LAMP stack powered by AWS).

Therefore we had to evaluate some options to make sure (as mentioned) we use right tool for the right job.

  1. MyISAM full-text search. Excellent for simple needs, not powerful enough for our requirements. Let me say a few quick points about it… Default index is four characters, and we actually had a business requirement to have at least a two character search, under any circumstances. Yes, you can index with 2 chars, but the performance comes to a grinding halt (OK, I am being a bit dramatic here, but it does nothing to improve what is inherently “not so great”). Not to mention that after moving to InnoDB we simply could not use MyISAM anymore.
  2. Google’s custom search. Why not let the best in business handle your needs?
    Well, to fully utilize the best of the best, you need to have a business account (to allow for customized branding, no-ads, etc. which is very important in some cases). You can certainly look-up some of the pros and cons and options at the Google’s custom search pages. In most cases I would stop right here and suggest that people give it a shot. (But then the post would be too boring)…
  3. Google Appliance. One of the issues that we faced right off the bat, was the fact that in order to access the app (and consequently the search feature) you had to be authenticated. Which, from my research (albeit somewhat limited) could only be done by hosting the google appliance. Obviously having your own little piece of google is cool, but the price and overhead of maintenance are certain drawbacks, which we weren’t ready to face, at least just yet.
  4. Lucene. A very popular and quite powerful tool for database indexing and searching. There are a few implementations out there… The most important for us, cake-people, is the Zend PHP version. While the tool is great (and is actually used by CakePHP’s own manual) it has a few extremely strange behavioral problems. Without going into much detail, there are 3 magic numbers (options), which control the auto-optimization of your index.
    Again, I am not a Lucene expert, but after trying out Lucene in a real-world application the gravity of attempts to properly adjust the auto-optimization options in order to keep the application performing smoothly seemed liked trying to control a magic carpet flight.
    Alrighty, to be fair, if you have a rather static site (similar to cake’s manual) there should be no issue with using Lucene. Needless to say, there are a few CakePHP-specific ways, which show you how to integrate Lucene into your environment. However, for a highly dynamic site (i.e. the forum) the auto-optimization of the index would bring down the application and make everyone cry, not to mention the indexing of the existing content would take hours upon initial installation.
  5. Sphinx. While hesitant at first, we’ve decided to give it a shot… One issue is that you have to install sphinx as as service (daemon) running on your web server. In many cases (smaller applications or shared hosting) it could be a showstopper. That being said, the installation is quite simple, and very little effort is required as far as any maintenance. (It also has a very low foot-print and so far, after a few months of real-world testing, it has not impacted server performance in any way).

Well… now you can probably guess that Sphinx was our final candidate and ultimately the chosen solution. A few things that made my colleagues and I very impressed was that the indexing is extremely fast. Compared to Lucene the initial indexing only took minutes for about one million records.
As well, and quite importantly, updating the index for any new posts/comments is also very fast.

By default the results are filtered by relevance as well as the date of the post (well, at least in our app it had an importance).
For example, most relevant and recent posts would be at the top of the resultset of a search, while older, yet still relevant results would appear lower.
(Word highlighting and other “neat” features are also available, but justly so… with other search tools as well).

Now then, what about the actual implementation?

Once the decision has been made to try out Sphinx, it was actually rather simple…

First of all a HUGE “thank you”, to the creator of this excellent behavior.
It has performed flawlessly in both CakePHP 1.2 and 1.3… (certainly some adjustments might be required for anyone’s specific needs, but the foundation, which has been laid down, is outstanding).

Once the behavior is properly attached to the required models, the Sphinx configuration couldn’t be easier:
(I will skip over the defaults and just point out what was required to get this thing off the ground)

source main
This is where the overall configuration of the Sphinx search engine is stored, as well as our initialization query to get thing up and running:

sql_query_pre = SET NAMES utf8
sql_query_pre = REPLACE INTO forum_counter SELECT 1, MAX(id) FROM forum_comments
sql_query = SELECT id, category_id, topic_id, user_id, body, UNIX_TIMESTAMP(created) AS created FROM forum_comments WHERE active = 1

sql_attr_uint = topic_id
sql_attr_timestamp = created

sql_query_info = SELECT * FROM forum_comments WHERE id = $id

Yep, besides any server-side defaults this is all that was custom-tailored and needed to get things going.
I hope you see how simple the queries are and can utilize the setup in your app.
(Notice, that we are using cake’s excellent counter cache here).

source delta : main

{
sql_query_pre = SET NAMES utf8
sql_query = SELECT id, category_id, topic_id, user_id, body, UNIX_TIMESTAMP(created) AS created FROM forum_comments WHERE active = 1 AND id > ( SELECT max_doc_id FROM forum_counter WHERE counter_id = 1)
}

This little snippet controls the “delta”, i.e. the difference between the original index and, well, any new additions to the forum.
Notice the max_doc_id, which is referring to the Sphinx index.

Again, besides the defaults (and attaching the above-mentioned behavior), this is all that was needed to be done to get a really great search engine working in our app.

I know that this has become a rather long post already, so I’d like to cut it short right about now…

  1. Please ask any specific questions in the comments. I do realize that everything above is quite a generic overview.
  2. Sphinx doesn’t play well with UUID’s, hopefully someone will prove me wrong ;)
  3. Default search return is 1,000 results… in most cases this is more than plenty, but hopefully someone can show us a cake-way to bring back even more.

Well then, if you’ve made it this far, next round of beers is on me ;)