0
# Query Building
1

2
Data query construction for pivot table requirements, handling groupby columns/rows, metrics, and filtering with support for temporal data and custom ordering.
3

4
## Capabilities
5

6
### buildQuery Function
7

8
Main function that constructs the database query for pivot table data requirements.
9

10
```typescript { .api }
11
/**
12
 * Builds query context for pivot table data retrieval
13
 * @param formData - Form data containing pivot table configuration
14
 * @returns QueryContext with query specifications for database
15
 */
16
export default function buildQuery(formData: PivotTableQueryFormData): QueryContext;
17
```
18

19
**Usage Example:**
20

21
```typescript
22
import buildQuery from "@superset-ui/plugin-chart-pivot-table/src/plugin/buildQuery";
23

24
const formData = {
25
  groupbyRows: ['region', 'category'],
26
  groupbyColumns: ['year'],
27
  metrics: ['sales', 'profit'],
28
  time_grain_sqla: 'P1M',
29
  temporal_columns_lookup: { year: true }
30
};
31

32
const queryContext = buildQuery(formData);
33
// Returns QueryContext with appropriate query specifications
34
```
35

36
### PivotTableQueryFormData Interface
37

38
Form data interface that extends QueryFormData with pivot table specific options.
39

40
```typescript { .api }
41
export interface PivotTableQueryFormData extends QueryFormData, PivotTableStylesProps, PivotTableCustomizeProps {
42
  /** Columns for grouping rows */
43
  groupbyColumns?: QueryFormColumn[];
44
  /** Columns for grouping columns */
45
  groupbyRows?: QueryFormColumn[];
46
  /** Time grain for temporal columns */
47
  time_grain_sqla?: string;
48
  /** Lookup for temporal columns */
49
  temporal_columns_lookup?: Record<string, boolean>;
50
}
51
```
52

53
### Query Context
54

55
Return type that contains the complete query specification for data retrieval.
56

57
```typescript { .api }
58
interface QueryContext {
59
  queries: QueryObject[];
60
}
61

62
interface QueryObject {
63
  /** Columns to include in the query */
64
  columns: QueryFormColumn[];
65
  /** Ordering specification */
66
  orderby?: QueryFormOrderBy[];
67
  /** Additional query parameters */
68
  extras?: Record<string, any>;
69
}
70
```
71

72
### QueryFormColumn Types
73

74
Column specification types for the query building process.
75

76
```typescript { .api }
77
export type QueryFormColumn = string | AdhocColumn;
78

79
interface AdhocColumn {
80
  /** Time grain for temporal columns */
81
  timeGrain?: string;
82
  /** Column type classification */
83
  columnType: 'BASE_AXIS' | string;
84
  /** SQL expression for the column */
85
  sqlExpression: string;
86
  /** Display label for the column */
87
  label: string;
88
  /** Expression type */
89
  expressionType: 'SQL' | string;
90
}
91
```
92

93
### QueryFormOrderBy
94

95
Ordering specification for query results.
96

97
```typescript { .api }
98
export type QueryFormOrderBy = [QueryFormMetric | string, boolean];
99
export type QueryFormMetric = string | AdhocMetric;
100

101
interface AdhocMetric {
102
  /** Metric definition */
103
  aggregate: string;
104
  column: QueryFormColumn;
105
  label: string;
106
}
107
```
108

109
### Query Building Process
110

111
The buildQuery function follows this process:
112

113
1. **Column Deduplication**: Combines groupbyColumns and groupbyRows, removing duplicates
114
2. **Temporal Column Processing**: Applies time grain to temporal columns when specified  
115
3. **Base Query Construction**: Uses buildQueryContext to create the base query structure
116
4. **Ordering Configuration**: Sets up ordering based on series_limit_metric or first metric
117
5. **Query Object Assembly**: Combines columns, ordering, and additional parameters
118

119
**Advanced Usage:**
120

121
```typescript
122
// Query with temporal columns and custom ordering
123
const complexFormData = {
124
  groupbyRows: ['region'],
125
  groupbyColumns: ['created_date'],
126
  metrics: ['count', 'avg_sales'],
127
  time_grain_sqla: 'P1M',
128
  temporal_columns_lookup: { created_date: true },
129
  series_limit_metric: 'count',
130
  order_desc: true
131
};
132

133
const queryContext = buildQuery(complexFormData);
134
// Results in query with monthly date grouping and count-based ordering
135
```