Working with relative dates

[netonme]

Is it possible to work with relative dates? I Try to get a SQL output like

field > DATE_SUB(NOW(), INTERVAL 14 DAY)

my idea was...

var filter = {
    "id": "field", 
    "field": "field", 
    "type": "string", 
    "operators":[ "equal", "not_equal", "less_or_equal", "greater_or_equal", "between"], 
    "data": {
        "mode": "rel"
        },
    "valueGetter": function (rule) {
        return 'DATE_SUB(NOW(), INTERVAL ' + rule.$el.find('.rule-value-container input').val() + ' DAY)';
        }
    }

but then the output is quoted like this

field > 'DATE_SUB(NOW(), INTERVAL 14 DAY)'

and if i use "type": "integer", the output is complete empty. It there any solutions to get this up?

Thanks

[mistic100]

Please use valid syntax for your code blocks,I edited your message with triple `

---

Your approach is the right one but the SQL generator was never designed for that.

What do you think about a new filter method getSQL which will be responsible to generate the SQL of this filter ?

Something like (will change to accommodate complex operators) :

getSQL: function(field, operator, value, rule) {
  return field + operator + 'DATE_SUB(NOW(), INTERVAL ' + value + ' DAY)';
}

Or this new method could only alter the value part.

And is also needs a new sqlParser method for the reverse operation.

[netonme]

Sounds great! But like something you have to implement? Or is this something i can do with the current version? in my case, the reverse operation, is not needed. But of course, it make sense to have it.

My Idea was something like SQLMethod: "DATE_SUB(NOW(), INTERVAL %s DAY)" but your solution is more flexible.

[mistic100]

The big problem is that https://github.com/forward/sql-parser does not support this kind of "advanced" syntax, so the setRulesFromSQL could not work

[mistic100]

It is done, it requires the latest version of mistic100/sql-parser (1.1.0).

There are not getters/setters but is totally based on events I added in a recent feature.
You can see how it works in the unit test here

jQuery-QueryBuilder/tests/plugins.sql-support.module.js

Lines 194 to 270 in c72d367

	QUnit.test('Custom export/parsing', function (assert) {
	var rules = {
	condition: 'AND',
	rules: [
	{
	id: 'name',
	operator: 'equal',
	value: 'Mistic'
	},
	{
	id: 'last_days',
	operator: 'greater',
	value: 5
	}
	]
	};
	var sql = 'name = \'Mistic\' AND display_date > DATE_SUB(NOW(), INTERVAL 5 DAY)';
	$b.queryBuilder({
	filters: [
	{
	id: 'name',
	type: 'string'
	},
	{
	id: 'last_days',
	field: 'display_date',
	type: 'integer',
	operators: ['greater']
	}
	]
	});
	$b.on('ruleToSQL.queryBuilder.filter', function (e, rule, sqlValue, sqlOperator) {
	if (rule.id === 'last_days') {
	e.value = rule.field + ' ' + sqlOperator('DATE_SUB(NOW(), INTERVAL ' + sqlValue + ' DAY)');
	}
	});
	$b.on('parseSQLNode.queryBuilder.filter', function (e) {
	var data = e.value;
	// left must be the field name and right must be the date_sub function
	if (data.left && data.left.value == 'display_date' && data.operation == '>' && data.right && data.right.name == 'DATE_SUB') {
	var right = data.right;
	// 1st argument is "NOW()" and 2nd argument is a list
	if (right.arguments.value.length === 2 && right.arguments.value[1].value.length === 3) {
	var params = right.arguments.value[1].value;
	// 1st item is "INTERVAL", 2nd item is the value, 3rd item is "DAY"
	if (params[0].value == 'INTERVAL' && params[2].value == 'DAY') {
	e.value = {
	id: 'last_days',
	operator: 'greater',
	value: params[1].value
	};
	}
	}
	}
	});
	$b.queryBuilder('setRules', rules);
	assert.equal(
	$b.queryBuilder('getSQL').sql,
	sql,
	'Should export custom date_sub function'
	);
	$b.queryBuilder('reset');
	$b.queryBuilder('setRulesFromSQL', sql);
	assert.rulesMatch(
	$b.queryBuilder('getRules'),
	rules,
	'Should parse date_sub function'
	);
	});

it's a bit complicated for parsing because it requires to inspect the AST.