timgws / querybuilderparser Goto Github PK

QueryBuilderParserTest::testMoreComplexQuery() asserts that this JSON:

{
       "condition":"AND",
       "rules":[
          {
             "id":"price",
             "field":"price",
             "type":"double",
             "input":"text",
             "operator":"less",
             "value":"10.25"
          },
          {
             "condition":"OR",
             "rules":[
                {
                   "id":"name",
                   "field":"name",
                   "type":"string",
                   "input":"text",
                   "operator":"begins_with",
                   "value":"Thommas"
                },
                {
                   "id":"name",
                   "field":"name",
                   "type":"string",
                   "input":"text",
                   "operator":"equal",
                   "value":"John Doe"
                }
             ]
          }
       ]
    }

Hello,
it would be helpful to be able to create bindings for fields similarly at what is done for JoinSupportingQueryBuilderParser. If I store some values within json columns I can't use the parser to filter on these fields easily. Something like that could be usefull :

$bindingsFields = array(
        'myjsonfield1' => array(
            'from_table'      => 'table1',
            'from_col'        => 'json_col',
            'json_path'        => 'json_fields->myjsonfield1->myjsonfield1->value' // should be optional if field is not within a json column
        ),

Just wondering if collections are supported with this package? Or only DB:: queries?

hello, I would like to know if it is possible to join (aggregate) for mongoDB?
Because the library is very nice, but to use it on a day-to-day basis, I need to be able to do aggregation for mongoDB.

Or if it is not possible to join (aggregate) for mongoDB with the library, if you have any alternative to be able to do it.

Is there a working example in combination with datatables somewhere? Laravel version 6^

hello , I am trying to append a new group of conditions in the background and in this group, I want to use OR and the relation between this group and the group that was submitted by the user will be AND
so this is how it will work
group1 which is submitted by a user
group 2 which I want to add in the background
the relation between group1 and group2 is AND
and the conditions in group2 are OR
how can I do that? how can I push my conditions in the condition that the customer has been sent?

Given the Schema Files -> FilesPaymentAgreements -> FilesPaymentAgreementsDetail, where Files is the master table, is it possible to add define a join for a field on table FilesPaymentAgreementsDetail?

I have tried these two things:

$joins = [
    'FilesPaymentAgreementsDetail.PaymentDate' => [
        'from_table'      => 'FilesPaymentAgreements',
        'from_col'        => 'ID',
        'to_table'        => 'FilesPaymentAgreementsDetail',
        'to_col'          => 'ExID',
        'to_value_column' => 'PaymentDate',
        'to_clause' => function($q) {
            $q->join('FilesPaymentAgreementsDetail', 'FilesPaymentAgreementsDetail.ExID', '=', 'FilesPaymentAgreements.ID');
        },
    ],
];

$qbp = new \timgws\JoinSupportingQueryBuilderParser(['FilesPaymentAgreementsDetail.PaymentDate'], $joins); 
dd($qbp->parse(request()->query_builder, DB::table('Files')->toSql());

$joins = [
    'FilesPaymentAgreementsDetail.PaymentDate' => [
        [
        'from_table'      => 'Files',
        'from_col'        => 'ID',
        'to_table'        => 'FilesPaymentAgreements',
        'to_col'          => 'ExID',
        'to_value_column' => 'PaymentDate',
        ],
        [
            'from_table'      => 'FilesPaymentAgreements',
            'from_col'        => 'ID',
            'to_table'        => 'FilesPaymentAgreementsDetail',
            'to_col'          => 'ExID',
            'to_value_column' => 'PaymentDate',
        ],
    ],
];

How to Use Model instead of using DB? So that its easier to get data that is set or provided in the model?

İs there any examples with eloquent ?

Which code should i put at view page ? I want to filter for products catalog using for example:Id, description,category, price, Brand. then show images with the fields previous named .

My project is in Laravel´s Framework.

Thank you.

timgws/query-builder-parser 1.3 requires illuminate/database 4.1 - 5.4 -> satisfiable by laravel/framework[v5.4.33], illuminate/database[4.1.x-dev, 4.2.x-dev, 5.0.x-dev, 5.1.x-dev, 5.2.x-dev, 5.3.x-dev, 5.4.x-dev, v4.1.0, v4.1.1, v4.1.10, v4.1.11, v4.1.12, v4.1.13, v4.1.14, v4.1.15, v4.1.16, v4.1.17, v4.1.18, v4.1.19, v4.1.2, v4.1.20, v4.1.21, v4.1.22, v4.1.23, v4.1.24, v4.1.25, v4.1.26, v4.1.27, v4.1.28, v4.1.29, v4.1.3, v4.1.30, v4.1.4, v4.1.5, v4.1.6, v4.1.7, v4.1.8, v4.1.9, v4.2.0-BETA1, v4.2.1, v4.2.12, v4.2.16, v4.2.17, v4.2.2, v4.2.3, v4.2.4, v4.2.5, v4.2.6, v4.2.7, v4.2.8, v4.2.9, v5.0.0, v5.0.22, v5.0.25, v5.0.26, v5.0.27, v5.0.28, v5.0.33, v5.0.4, v5.1.1, v5.1.13, v5.1.16, v5.1.2, v5.1.20, v5.1.22, v5.1.25, v5.1.28, v5.1.30, v5.1.31, v5.1.41, v5.1.6, v5.1.8, v5.2.0, v5.2.19, v5.2.21, v5.2.24, v5.2.25, v5.2.26, v5.2.27, v5.2.28, v5.2.31, v5.2.32, v5.2.37, v5.2.43, v5.2.45, v5.2.6, v5.2.7, v5.3.0, v5.3.16, v5.3.23, v5.3.4, v5.4.0, v5.4.13, v5.4.17, v5.4.19, v5.4.27, v5.4.36, v5.4.9].

Hi,

dont know if you are still supporing this project, but i would like to have a question.
I made an implementation of your filter in my laravel project. Working fine. Also could make the Join filtering work.

So if i want to get all my contracts with a partner located in a specific country i can just go:

protected $joins = [
        'country_id' =>  [
            'from_table'      => 'contract',
            'from_col'        => 'partner_id',
            'to_table'        => 'partner',
            'to_col'          => 'partner_id',
            'to_value_column' => 'country_id'
        ]
];

Hello,

I try to install the library on Laravel 9 but I have the following errors:

Problem 1
    - illuminate/database[v5.6.0, ..., 5.8.x-dev] require php ^7.1.3 -> your php version (8.1.0) does not satisfy that requirement.
    - illuminate/database[v6.0.0, ..., v6.19.1] require php ^7.2 -> your php version (8.1.0) does not satisfy that requirement.
    - illuminate/database[v7.0.0, ..., v7.28.4] require php ^7.2.5 -> your php version (8.1.0) does not satisfy that requirement.
    - illuminate/database[v8.0.0, ..., v8.11.2] require php ^7.3 -> your php version (8.1.0) does not satisfy that requirement.
    - Root composer.json requires timgws/query-builder-parser dev-master -> satisfiable by timgws/query-builder-parser[dev-master].
    - Conclusion: don't install laravel/framework v9.3.0 (conflict analysis result)
    - Conclusion: don't install laravel/framework v9.3.1 (conflict analysis result)
    - Conclusion: don't install laravel/framework v9.2.0 (conflict analysis result)
    - timgws/query-builder-parser dev-master requires illuminate/database ^8.0||^7.0||^6.0||4.1 - 6.0 -> satisfiable by illuminate/database[v4.1.0, ..., 4.2.x-dev, v5.0.0, ..., 5.8.x-dev, v6.0.0, ..., 6.x-dev, v7.0.0, ..., 7.x-dev, v8.0.0, ..., 8.x-dev].
    - Only one of these can be installed: illuminate/database[v4.1.0, ..., 4.2.x-dev, v5.0.0, ..., 5.8.x-dev, v6.0.0, ..., 6.x-dev, v7.0.0, ..., 7.x-dev, v8.0.0, ..., 8.x-dev, v9.0.0-beta.1, ..., 9.x-dev], laravel/framework[v9.2.0, v9.3.0, v9.3.1, 9.x-dev]. laravel/framework replaces illuminate/database and thus cannot coexist with it.
    - Root composer.json requires laravel/framework ^9.2 -> satisfiable by laravel/framework[v9.2.0, v9.3.0, v9.3.1, 9.x-dev].

Hey,

just wanted to Upgrade to Laravel 5.4 but it is blocked by QueryBuilderParser. I saw that you made the required changes already.

When there will be a release?

Best regards

In Using QueryBuilderParser you use DB::table and DB::collection. May I suggest adding the namespace for these. That would make it easier to understand how to integrate with Illuminate, without Laravel.

I'm making a pull request to the documentation of https://querybuilder.js.org/, to label your project for both Laravel and Illuminate. It's currently labelled just for Laravel.

Hello! I found that there's a SQLi bypass on your code;
The method validateCondition QBPFunctions.php validates "And, or" inputs but it only works for createNestedQuery of the Class QueryBuilderParser.php
From a non Nested Query that validation do not occur, so the request is send to the DB without the valid input
So an attacker can send the payload instead of the "And,Or" function with non nested querys

Can you solve this please?
Thanks in Advance
Have a Great Day!

Use Model instead of DB?

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?

I let my users create their own subqueries which can be used as filters in the query builder. But wheres are not working with values from subqueries so I need this one to be $query->having() instead of $query->where(). But in QBPFunctions.php and in JoinSupportingQueryBuilderParser.php there are some more wheres which should change in that case.

Maybe a new parameter in the constructor?

public function __construct(array $fields = null, $useHaving = false)

I have the following error. Version 2.0.0

#message: "Undefined property: stdClass::$field"
#code: 0
#file: "C:\Users\vp1zakq\Projects\sug-data\vendor\timgws\query-builder-parser\src\QueryBuilderParser\QueryBuilderParser.php"
#line: 310

rule value

{#1308 ▼ // vendor\timgws\query-builder-parser\src\QueryBuilderParser\QueryBuilderParser.php:314
+"condition": "AND"
+"rules": array:2 [▼
0 => {#1309 ▼
+"id": "k000_unsel"
+"field": "k000_unsel"
+"type": "string"
+"input": "text"
+"operator": "is_not_empty"
+"value": null
}
1 => {#1310 ▼
+"id": "k000_herkunf"
+"field": "k000_herkunf"
+"type": "string"
+"input": "text"
+"operator": "is_not_empty"
+"value": null
}
]
}

Should this:

$table = DB::table('table_of_data_to_integrate');
$jsqbp = new JoinSupportingQueryBuilderParser($fields, $this->getJoinFields());
$test = $parser->parse($json, $builder);

$table = DB::table('table_of_data_to_integrate');
$jsqbp = new JoinSupportingQueryBuilderParser($fields, $this->getJoinFields());
$test = $jsqbp->parse($json, $table);

The makeQuery method inherited from the QueryBuilderParser class in the JoinSupportingQueryBuilderParser class was not rewritten in v2.0.0, causing an error.

QueryBuilderParser:

protected function makeQuery(EloquentBuilder|Builder $query, stdClass $rule, $queryCondition = 'AND')

JoinSupportingQueryBuilderParser:

protected function makeQuery(Builder $query, stdClass $rule, $queryCondition = 'AND')

Error:

[2023-08-11 13:37:30]
local.ERROR: Declaration of timgws\JoinSupportingQueryBuilderParser::makeQuery(Illuminate\Database\Query\Builder $query, stdClass $rule, $queryCondition = 'AND')
must be compatible with timgws\QueryBuilderParser::makeQuery(Illuminate\Database\Eloquent\Builder|Illuminate\Database\Query\Builder $query, stdClass $rule, $queryCondition = 'AND')

Can't composer require this for 5.7, get this error:

timgws/query-builder-parser 1.5 requires illuminate/database 4.1 - 5.6

Possible to push your 5.8 changes? Or at least just the composer file to allow 5.7.* in the meantime?

Does the package still have tracking? I'm interested in Laravel version 11.

Would you mind to tag master with Laravel 9 support, please?

is there an easy way to change the behavior of contains, so instead of using like, use ilike ?
I'm using postgres.

timgws/query-builder-parser 1.5.1 requires illuminate/database 4.1 - 5.8

which seem an issue when trying to upgrade from laravel 5.8 to Laravel 6

Hi. I wondered if it's possible to filter many-to-many joins using the JoinSupportingQueryBuilderParser?

I have a table setup similar to this (the real thing is more complex, but this is the gist):

products
--------
id
name

attributes
----------
id
name
value

products_attributes
-------------------
product_id
attribute_id

Current version blocking update to laravel/framework 5.3
Any chance that this package will support 5.3?

Hi,

Is it possible to have it pure JS/jQuery based.
I want to use it with C# but I need ot have joins functionaity.

Thanks

Hi,

I added the is_null operator on the QueryBuilder but it seems to be parsed like a value, the resulting SQL query looks like:
... field_name = "NULL" ...

but it should be like this:
... field_name IS NULL ...

I will try to make a test with this case in a few days.

Thank you again!

Hi there,

First of all this is a great stuff, thank you for this.

I noticed a small bug, when I select OR in the QueryBuilder the parser always seperate them with AND instead of OR statements.

Here is an example query output:

select * from table where exists (...) and exists (...)

Not sure but I think it comes from this:
https://github.com/timgws/QueryBuilderParser/blob/master/src/QueryBuilderParser/JoinSupportingQueryBuilderParser.php#L117

I will try to make a test for this case later.

Thank you!

Dates were not being cast as dates, so if you have a query that was filtering between two dates it won't work.

Simple fix would be to turn any field of date into a Carbon object. I did this by editing the getValueForQueryFromRule method like this (just before returning the value)

	 if ( $rule->type == 'datetime' ) {
		if ( is_array( $value ) ) {
			$value = array_map( function ( $v ) {
				return new Carbon( $v );
			}, $value );
		} else {
			$value = new Carbon( $value );
		}
	}

There is a bug in QueryBuilderParser class in line 82.

If you have nested rules makeQuery function is called before isNested. As nested rules have different array structure makeQuery cannot be processed - throws an error that field does not exists.

So, you have to check if rules are nested and call makeQuery if they are not.

Original:
$querybuilder = $this->makeQuery($querybuilder, $rule, $queryCondition); if ($this->isNested($rule)) { $querybuilder = $this->createNestedQuery($querybuilder, $rule, $queryCondition); }

Replace with:
if ($this->isNested($rule)) { $querybuilder = $this->createNestedQuery($querybuilder, $rule, $queryCondition); } else { $querybuilder = $this->makeQuery($querybuilder, $rule, $queryCondition); }

Using version ^1.5 for timgws/query-builder-parser
./composer.json has been updated
Loading composer repositories with package information
Updating dependencies (including require-dev)
Your requirements could not be resolved to an installable set of packages.

Problem 1
- The requested package laravel/passport (locked at v7.5.1, required as ^8.4) is satisfiable by laravel/passport[v7.5.1] but these conflict with your requirements or minimum-stability.
Problem 2
- Conclusion: don't install laravel/passport v8.5.0
- Conclusion: don't install laravel/passport v8.4.4
- Conclusion: don't install laravel/passport v8.4.3
- Conclusion: don't install laravel/passport v8.4.2
- Conclusion: don't install laravel/passport v8.4.1
- Conclusion: remove league/oauth2-server 7.4.0
- Installation request for laravel/passport ^8.4 -> satisfiable by laravel/passport[v8.4.0, v8.4.1, v8.4.2, v8.4.3, v8.4.4, v8.5.0].
- Conclusion: don't install league/oauth2-server 7.4.0
- laravel/passport v8.4.0 requires league/oauth2-server ^8.0 -> satisfiable by league/oauth2-server[8.0.0, 8.1.0, 8.1.1].
- Can only install one of: league/oauth2-server[8.0.0, 7.4.0].
- Can only install one of: league/oauth2-server[8.1.0, 7.4.0].
- Can only install one of: league/oauth2-server[8.1.1, 7.4.0].
- Installation request for league/oauth2-server (locked at 7.4.0) -> satisfiable by league/oauth2-server[7.4.0].

Installation failed, reverting ./composer.json to its original content.

I came across a use-case where not_equal does not produce the result I expected because of the following MySQL behavior: https://stackoverflow.com/questions/16186674/mysql-syntax-not-evaluating-not-equal-to-in-presence-of-null

Could you consider changing the way the not_equal operator is translated in MySQL? It should be (but I know this is a personal opinion) NOT column <=> value.

Full disclosure: I'd like to avoid my users create a query column IS NULL OR column != value.

Let me know what you think :) Or if it's unclear.