search

timgws / QueryBuilderParser

A simple to use query builder for the jQuery QueryBuilder plugin for use with Laravel.
MIT License
159 stars 65 forks source link

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

Open VoordeMensen opened 5 years ago

VoordeMensen commented 5 years ago

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 commented 5 years ago

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: https://github.com/timgws/QueryBuilderParser/blob/db9a0dc0ed74f103c578e058f8c46ee18db73907/src/QueryBuilderParser/QBPFunctions.php#L163-L179

but we do not provide any options.

This could be fixed by changing the way getRuleValue parses the date: https://github.com/timgws/QueryBuilderParser/blob/e8ff09ca5bc038b7d20e12ff4053f1e503ee25ae/src/QueryBuilderParser/QBPFunctions.php#L226-L241

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: