How to use match() in numtel:mysql


#1

How should a server side SQL match against query look in numtel:mysql. I can’t get it quite right. If someone could show me an example that would be very helpful. Thanks in advanced.


#2

Using the natural language example from the MySQL documentation, the following example provides a starting point for using the example query with efficient updating.

  // Supply regex that matches same results in Javascript
  var matcher = /database/i
  // liveDb is LiveMysql instance
  var matchedSelect = liveDb.select(
    'SELECT * FROM articles WHERE MATCH (title,body) AGAINST (\'database\')',
    [
      {
        table: 'articles',
        condition: function(row, newRow){
          // Check if any relevant fields match
          // newRow only exists on UPDATE events
          var doRefresh =
            row.title.match(matcher)    !== null ||
            row.body.match(matcher)     !== null ||
            (newRow && (
              newRow.title.match(matcher) !== null ||
              newRow.body.match(matcher)  !== null));

          doRefresh && console.log('Refreshing match() query');

          return doRefresh;
        }
      }
    ]
  );

This works but is not 100% perfect. The regex in this example will match strings containing the plural form, databases , while the MySQL query does not, resulting in possible unnecessary refreshes.

For an example of how to publish this result set, see the leaderboard example app.


#3

Thanks. Would it go in Meter.methods({}); or where the publish statements go?