Support date ranges being converted to SQL and MongoDB queries with jQuery-QueryBuilder

[VoordeMensen]

I have a database-design where the date I want to query is of the datetime-type. But when I try to select all orders that equal to todays date for example, the parser will add 00:00:00 and I will not get any results.

I usually solve this to use the mysql date function (select * from orders where order_date = date('2019-06-28')) - is there any way to do this with the QueryBuilderParser?

I now manually loop through the rules and rebuild an equals to to between and manipulate fields, but I hope this can be done more easily?

[timgws]

This is not something that is currently possible, but I would not mind a pull request if the functionality is appropriately implemented.

Normally, with the querybuilder, you could do something like:

$q->whereDate('created_at', '=', Carbon::today()->toDateString());

to just get the date.

We are using Carbon to convert the input from QueryBuilderParser:

QueryBuilderParser/src/QueryBuilderParser/QBPFunctions.php

Lines 163 to 179 in db9a0dc

	/**
	* Convert a Datetime field to Carbon items to be used for comparisons.
	*
	* @param $value
	* @return \Carbon\Carbon
	* @throws QBParseException
	*/
	protected function convertDatetimeToCarbon($value)
	{
	if (is_array($value)) {
	return array_map(function ($v) {
	return new Carbon($v);
	}, $value);
	}
	return new Carbon($value);
	}

but we do not provide any options.

This could be fixed by changing the way getRuleValue parses the date:

QueryBuilderParser/src/QueryBuilderParser/QBPFunctions.php

Lines 226 to 241 in e8ff09c

	/**
	* get a value for a given rule.
	*
	* throws an exception if the rule is not correct.
	*
	* @param stdClass $rule
	* @throws QBRuleException
	*/
	private function getRuleValue(stdClass $rule)
	{
	if (!$this->checkRuleCorrect($rule)) {
	throw new QBRuleException();
	}
	return $rule->value;
	}

So that it creates a correct DateBasedWhere when using different formats of dates:

https://github.com/laravel/framework/blob/15917aa3a2616344a1ffb896610878b929544f77/src/Illuminate/Database/Query/Builder.php#L1104-L1124

You would need a way to be able to specify what format the date is desired to be in. A quick browse of Laravel shows it supports the following:

• Y-m-d (which you want)
• H:i:s
• Y
• m
• d