# Dynamic Query Builder

Pustaka Go yang powerful untuk membangun query SQL dan MongoDB secara dinamis dengan dukungan filtering, sorting, pagination, join, CTE, window functions, dan operasi JSON/Array.

## Fitur Utama

- **Multi-Database**: Mendukung PostgreSQL, MySQL, SQLite, SQL Server, dan MongoDB
- **Dynamic Filtering**: Berbagai operator filter (_eq, _neq, _like, _in, _between, dll.)
- **JSON Operations**: Dukungan penuh untuk query dan update data JSON
- **Array Operations**: Query array dengan berbagai operator
- **Security**: Built-in proteksi SQL injection dan kontrol akses kolom/tabel
- **Window Functions**: Dukungan ROW_NUMBER, RANK, dll.
- **CTE & Unions**: Support untuk Common Table Expressions dan UNION
- **Query Parsing**: Parse URL query parameters menjadi DynamicQuery

## Instalasi

```bash
go get github.com/Masterminds/squirrel
go get github.com/jmoiron/sqlx
go get go.mongodb.org/mongo-driver/mongo
```

## Contoh Penggunaan

### 1. Operator Perbandingan Dasar

```go
package main

import (
	"context"
	"fmt"
	"log"
	"time"

	"github.com/jmoiron/sqlx"
	_ "github.com/lib/pq"
) 

func main() {
	// Inisialisasi QueryBuilder
	qb := NewQueryBuilder(DBTypePostgreSQL)
	
	// Koneksi ke database
	db, err := sqlx.Connect("postgres", "user=postgres dbname=mydb sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	// Equal (_eq)
	query := DynamicQuery{
		From: "users",
		Filters: []FilterGroup{{
			Filters: []DynamicFilter{{
				Column:   "email",
				Operator: OpEqual,
				Value:    "john@example.com",
			}},
			LogicOp: "AND",
		}},
	}
	
	var users []map[string]interface{}
	err = qb.ExecuteQuery(context.Background(), db, query, &users)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Printf("Found %d users\n", len(users))

	// Greater Than (_gt)
	query = DynamicQuery{
		From: "products",
		Filters: []FilterGroup{{
			Filters: []DynamicFilter{{
				Column:   "price",
				Operator: OpGreaterThan,
				Value:    100000,
			}},
			LogicOp: "AND",
		}},
		Limit: 10,
	}
	
	var products []map[string]interface{}
	err = qb.ExecuteQuery(context.Background(), db, query, &products)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Printf("Found %d products with price > 100000\n", len(products))
}
```

### 2. Operator Like dan String Matching

```go
func stringMatchingExample(qb *QueryBuilder, db *sqlx.DB) {
	// Case Insensitive Like (_ilike)
	query := DynamicQuery{
		From: "products",
		Filters: []FilterGroup{{
			Filters: []DynamicFilter{{
				Column:   "name",
				Operator: OpILike,
				Value:    "%laptop%",
			}},
			LogicOp: "AND",
		}},
		Limit: 20,
	}
	
	var products []map[string]interface{}
	qb.ExecuteQuery(context.Background(), db, query, &products)
	
	// Contains (_contains)
	query = DynamicQuery{
		From: "articles",
		Filters: []FilterGroup{{
			Filters: []DynamicFilter{{
				Column:   "content",
				Operator: OpContains,
				Value:    "golang",
			}},
			LogicOp: "AND",
		}},
	}
	
	var articles []map[string]interface{}
	qb.ExecuteQuery(context.Background(), db, query, &articles)
	
	// Starts With (_starts_with)
	query = DynamicQuery{
		From: "users",
		Filters: []FilterGroup{{
			Filters: []DynamicFilter{{
				Column:   "username",
				Operator: OpStartsWith,
				Value:    "admin",
			}},
			LogicOp: "AND",
		}},
	}
	
	var admins []map[string]interface{}
	qb.ExecuteQuery(context.Background(), db, query, &admins)
}
```

### 3. Operator IN, NOT IN, dan Between

```go
func inBetweenExample(qb *QueryBuilder, db *sqlx.DB) {
	// In (_in)
	query := DynamicQuery{
		From: "products",
		Filters: []FilterGroup{{
			Filters: []DynamicFilter{{
				Column:   "category",
				Operator: OpIn,
				Value:    []string{"electronics", "computers", "gadgets"},
			}},
			LogicOp: "AND",
		}},
		Limit: 50,
	}
	
	var products []map[string]interface{}
	qb.ExecuteQuery(context.Background(), db, query, &products)
	
	// Between (_between)
	query = DynamicQuery{
		From: "orders",
		Filters: []FilterGroup{{
			Filters: []DynamicFilter{{
				Column:   "order_date",
				Operator: OpBetween,
				Value:    []interface{}{time.Now().AddDate(0, -1, 0), time.Now()},
			}},
			LogicOp: "AND",
		}},
		Sort: []SortField{{Column: "order_date", Order: "DESC"}},
		Limit: 100,
	}
	
	var orders []map[string]interface{}
	qb.ExecuteQuery(context.Background(), db, query, &orders)
}
```

### 4. Operator Null dan Exists

```go
func nullExistsExample(qb *QueryBuilder, db *sqlx.DB) {
	// Is Null (_null)
	query := DynamicQuery{
		From: "users",
		Filters: []FilterGroup{{
			Filters: []DynamicFilter{{
				Column:   "deleted_at",
				Operator: OpNull,
				Value:    nil,
			}},
			LogicOp: "AND",
		}},
	}
	
	var activeUsers []map[string]interface{}
	qb.ExecuteQuery(context.Background(), db, query, &activeUsers)
	
	// Is Not Null (_nnull)
	query = DynamicQuery{
		From: "products",
		Filters: []FilterGroup{{
			Filters: []DynamicFilter{{
				Column:   "description",
				Operator: OpNotNull,
				Value:    nil,
			}},
			LogicOp: "AND",
		}},
	}
	
	var productsWithDesc []map[string]interface{}
	qb.ExecuteQuery(context.Background(), db, query, &productsWithDesc)
}
```

### 5. Operator JSON

```go
func jsonExample(qb *QueryBuilder, db *sqlx.DB) {
	// JSON Contains (_json_contains)
	query := DynamicQuery{
		From: "products",
		Filters: []FilterGroup{{
			Filters: []DynamicFilter{{
				Column:   "attributes",
				Operator: OpJsonContains,
				Value:    `{"color": "red"}`,
			}},
			LogicOp: "AND",
		}},
	}
	
	var redProducts []map[string]interface{}
	qb.ExecuteQuery(context.Background(), db, query, &redProducts)
	
	// JSON Exists (_json_exists)
	query = DynamicQuery{
		From: "users",
		Filters: []FilterGroup{{
			Filters: []DynamicFilter{{
				Column:   "profile",
				Operator: OpJsonExists,
				Options: map[string]interface{}{
					"path": "$.social_media",
				},
			}},
			LogicOp: "AND",
		}},
	}
	
	var usersWithSocialMedia []map[string]interface{}
	qb.ExecuteQuery(context.Background(), db, query, &usersWithSocialMedia)
	
	// JSON Equal (_json_eq)
	query = DynamicQuery{
		From: "settings",
		Filters: []FilterGroup{{
			Filters: []DynamicFilter{{
				Column:   "preferences",
				Operator: OpJsonEqual,
				Value:    "dark_mode",
				Options: map[string]interface{}{
					"path": "$.theme",
				},
			}},
			LogicOp: "AND",
		}},
	}
	
	var darkModeUsers []map[string]interface{}
	qb.ExecuteQuery(context.Background(), db, query, &darkModeUsers)
}
```

### 6. Operator Array

```go
func arrayExample(qb *QueryBuilder, db *sqlx.DB) {
	// Array Contains (_array_contains)
	query := DynamicQuery{
		From: "products",
		Filters: []FilterGroup{{
			Filters: []DynamicFilter{{
				Column:   "tags",
				Operator: OpArrayContains,
				Value:    "premium",
			}},
			LogicOp: "AND",
		}},
	}
	
	var premiumProducts []map[string]interface{}
	qb.ExecuteQuery(context.Background(), db, query, &premiumProducts)
	
	// Array Length (_array_length)
	query = DynamicQuery{
		From: "galleries",
		Filters: []FilterGroup{{
			Filters: []DynamicFilter{{
				Column:   "images",
				Operator: OpArrayLength,
				Options: map[string]interface{}{
					"length": 5,
				},
			}},
			LogicOp: "AND",
		}},
	}
	
	var galleriesWith5Images []map[string]interface{}
	qb.ExecuteQuery(context.Background(), db, query, &galleriesWith5Images)
}
```

### 7. Complex Query dengan Multiple Filters

```go
func complexQueryExample(qb *QueryBuilder, db *sqlx.DB) {
	// Query kompleks dengan multiple filter groups
	query := DynamicQuery{
		From: "products",
		Fields: []SelectField{
			{Expression: "id", Alias: "product_id"},
			{Expression: "name", Alias: "product_name"},
			{Expression: "price", Alias: "price"},
			{Expression: "category", Alias: "category"},
		},
		Filters: []FilterGroup{
			// Filter Group 1: Active products with price range
			{
				Filters: []DynamicFilter{
					{
						Column:   "status",
						Operator: OpEqual,
						Value:    "active",
					},
					{
						Column:   "price",
						Operator: OpBetween,
						Value:    []interface{}{100000, 500000},
					},
				},
				LogicOp: "AND",
			},
			// Filter Group 2: Category OR name matching
			{
				Filters: []DynamicFilter{
					{
						Column:   "category",
						Operator: OpIn,
						Value:    []string{"electronics", "computers"},
					},
					{
						Column:   "name",
						Operator: OpILike,
						Value:    "%laptop%",
					},
				},
				LogicOp: "OR",
			},
		},
		Sort: []SortField{
			{Column: "price", Order: "ASC"},
			{Column: "name", Order: "ASC"},
		},
		Limit:  20,
		Offset: 0,
	}
	
	var results []map[string]interface{}
	err := qb.ExecuteQuery(context.Background(), db, query, &results)
	if err != nil {
		log.Fatal(err)
	}
	
	fmt.Printf("Found %d products\n", len(results))
	for _, result := range results {
		fmt.Printf("Product: %s, Price: %v\n", result["product_name"], result["price"])
	}
}
```

### 8. Query dengan JOIN

```go
func joinExample(qb *QueryBuilder, db *sqlx.DB) {
	query := DynamicQuery{
		From:    "orders",
		Aliases: "o",
		Fields: []SelectField{
			{Expression: "o.id", Alias: "order_id"},
			{Expression: "o.order_date", Alias: "order_date"},
			{Expression: "c.name", Alias: "customer_name"},
			{Expression: "p.name", Alias: "product_name"},
		},
		Joins: []Join{
			{
				Type:  "INNER",
				Table: "customers",
				Alias: "c",
				OnConditions: FilterGroup{
					Filters: []DynamicFilter{
						{
							Column:   "o.customer_id",
							Operator: OpEqual,
							Value:    "c.id",
						},
					},
					LogicOp: "AND",
				},
			},
			{
				Type:  "LEFT",
				Table: "products",
				Alias: "p",
				OnConditions: FilterGroup{
					Filters: []DynamicFilter{
						{
							Column:   "o.product_id",
							Operator: OpEqual,
							Value:    "p.id",
						},
					},
					LogicOp: "AND",
				},
			},
		},
		Filters: []FilterGroup{{
			Filters: []DynamicFilter{
				{
					Column:   "o.order_date",
					Operator: OpGreaterThanEqual,
					Value:    time.Now().AddDate(0, -1, 0),
				},
			},
			LogicOp: "AND",
		}},
		Sort: []SortField{
			{Column: "o.order_date", Order: "DESC"},
		},
		Limit: 50,
	}
	
	var orders []map[string]interface{}
	err := qb.ExecuteQuery(context.Background(), db, query, &orders)
	if err != nil {
		log.Fatal(err)
	}
	
	fmt.Printf("Found %d orders\n", len(orders))
}
```

### 9. Query dengan Window Functions

```go
func windowFunctionExample(qb *QueryBuilder, db *sqlx.DB) {
	query := DynamicQuery{
		From: "sales",
		Fields: []SelectField{
			{Expression: "id", Alias: "sale_id"},
			{Expression: "product_name", Alias: "product"},
			{Expression: "amount", Alias: "sale_amount"},
		},
		WindowFunctions: []WindowFunction{
			{
				Function: "ROW_NUMBER",
				Over:     "product_name",
				OrderBy:  "amount DESC",
				Alias:    "rank_in_category",
			},
			{
				Function: "SUM",
				Over:     "product_name",
				OrderBy:  "sale_date",
				Frame:    "ROWS UNBOUNDED PRECEDING",
				Alias:    "running_total",
			},
		},
		Sort: []SortField{
			{Column: "product_name", Order: "ASC"},
			{Column: "amount", Order: "DESC"},
		},
	}
	
	var sales []map[string]interface{}
	err := qb.ExecuteQuery(context.Background(), db, query, &sales)
	if err != nil {
		log.Fatal(err)
	}
	
	fmt.Printf("Found %d sales records\n", len(sales))
}
```

### 10. MongoDB Example

```go
import (
	"context"
	"fmt"
	"log"
	"time"

	"go.mongodb.org/mongo-driver/bson"
	"go.mongodb.org/mongo-driver/mongo"
	"go.mongodb.org/mongo-driver/mongo/options"
)

func mongodbExample() {
	// Inisialisasi MongoQueryBuilder
	mqb := NewMongoQueryBuilder()
	
	// Koneksi ke MongoDB
	client, err := mongo.Connect(context.Background(), options.Client().ApplyURI("mongodb://localhost:27017"))
	if err != nil {
		log.Fatal(err)
	}
	defer client.Disconnect(context.Background())
	
	collection := client.Database("mydb").Collection("products")
	
	// Query dengan filter
	query := DynamicQuery{
		From: "products",
		Filters: []FilterGroup{
			{
				Filters: []DynamicFilter{
					{
						Column:   "status",
						Operator: OpEqual,
						Value:    "active",
					},
					{
						Column:   "price",
						Operator: OpBetween,
						Value:    []interface{}{100000, 500000},
					},
				},
				LogicOp: "AND",
			},
			{
				Filters: []DynamicFilter{
					{
						Column:   "category",
						Operator: OpIn,
						Value:    []string{"electronics", "computers"},
					},
					{
						Column:   "name",
						Operator: OpILike,
						Value:    "laptop",
					},
				},
				LogicOp: "OR",
			},
		},
		Sort: []SortField{
			{Column: "price", Order: "ASC"},
			{Column: "name", Order: "ASC"},
		},
		Limit:  20,
		Offset: 0,
	}
	
	var results []map[string]interface{}
	err = mqb.ExecuteFind(context.Background(), collection, query, &results)
	if err != nil {
		log.Fatal(err)
	}
	
	fmt.Printf("Found %d products\n", len(results))
	for _, result := range results {
		fmt.Printf("Product: %s, Price: %v\n", result["name"], result["price"])
	}
}
```

### 11. URL Query Parsing

```go
import (
	"net/url"
)

func urlParsingExample() {
	// Parse URL query parameters
	values, _ := url.ParseQuery("filter[status][_eq]=active&filter[price][_gte]=100000&sort=-created_at&limit=20")
	
	parser := NewQueryParser()
	query, err := parser.ParseQuery(values, "products")
	if err != nil {
		log.Fatal(err)
	}
	
	// Query akan berisi:
	// - Filters: status = 'active' AND price >= 100000
	// - Sort: created_at DESC
	// - Limit: 20
	
	qb := NewQueryBuilder(DBTypePostgreSQL)
	db, _ := sqlx.Connect("postgres", "connection-string")
	
	var products []map[string]interface{}
	err = qb.ExecuteQuery(context.Background(), db, query, &products)
	if err != nil {
		log.Fatal(err)
	}
}
```

## Konfigurasi Keamanan

```go
func securityExample() {
	qb := NewQueryBuilder(DBTypePostgreSQL)
	
	// Aktifkan security checks
	qb.SetSecurityOptions(true, 1000) // max 1000 rows
	
	// Tentukan kolom yang diizinkan
	qb.SetAllowedColumns([]string{
		"id", "name", "email", "status", "created_at",
		"price", "category", "description",
	})
	
	// Tentukan tabel yang diizinkan
	qb.SetAllowedTables([]string{
		"users", "products", "orders", "categories",
	})
	
	// Query dengan kolom/tabel tidak diizinkan akan error
	query := DynamicQuery{
		From: "users", // OK
		Filters: []FilterGroup{{
			Filters: []DynamicFilter{{
				Column:   "password", // ERROR: kolom tidak diizinkan
				Operator: OpEqual,
				Value:    "secret",
			}},
			LogicOp: "AND",
		}},
	}
	
	// Akan mengembalikan error
	_, _, err := qb.BuildQuery(query)
	fmt.Println(err) // "disallowed column: password"
}
```

## Referensi Operator

### Operator Perbandingan
- `_eq` - Equal
- `_neq` - Not Equal
- `_gt` - Greater Than
- `_gte` - Greater Than Equal
- `_lt` - Less Than
- `_lte` - Less Than Equal

### Operator String
- `_like` - Like (case-sensitive)
- `_ilike` - Like (case-insensitive)
- `_nlike` - Not Like (case-sensitive)
- `_nilike` - Not Like (case-insensitive)
- `_contains` - Contains substring
- `_ncontains` - Not Contains substring
- `_starts_with` - Starts with
- `_ends_with` - Ends with

### Operator Set
- `_in` - In list
- `_nin` - Not In list
- `_between` - Between two values
- `_nbetween` - Not Between two values

### Operator Null
- `_null` - Is Null
- `_nnull` - Is Not Null

### Operator JSON
- `_json_contains` - JSON contains value
- `_json_ncontains` - JSON not contains value
- `_json_exists` - JSON path exists
- `_json_nexists` - JSON path not exists
- `_json_eq` - JSON path equals value
- `_json_neq` - JSON path not equals value

### Operator Array
- `_array_contains` - Array contains value
- `_array_ncontains` - Array not contains value
- `_array_length` - Array has specific length

## Best Practices

1. **Selalu gunakan security checks** saat production:
   ```go
   qb.SetSecurityOptions(true, 1000)
   qb.SetAllowedColumns(allowedColumns)
   qb.SetAllowedTables(allowedTables)
   ```

2. **Gunakan context dengan timeout** untuk query:
   ```go
   ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
   defer cancel()
   ```

3. **Validasi input user** sebelum membuat DynamicFilter

4. **Gunakan prepared statements** (sudah otomatis di QueryBuilder)

5. **Log query** untuk debugging:
   ```go
   qb.SetQueryLogging(true)
   ```

## Kontribusi

Pull requests are welcome! For major changes, please open an issue first to discuss what you would like to change.

## License

MIT License