Docs
ServoyCloudSupportSamplesOpen SourceCommunity
  • Home
  • GUIDES
    • Platform Overview
    • Get Servoy
    • Get Started
      • Chapter 0
      • Chapter 1
      • Chapter 2
      • Chapter 3
      • Chapter 4
      • Chapter 5
    • Get Help
      • SuLA Instructions
      • SuLA Terms
    • Develop
      • Application Design
        • Forms
          • Layout Management
            • Simple Layout
            • Fully Responsive
              • Bootstrap 12-Grid
              • Flexbox
              • Custom Div
              • Collapsible Container
              • Simple Collapsible
              • Center Container
              • Inline Group Container
            • Abstract Form
          • Form Containers
            • Form Container
            • Tab Panel
            • Split Pane
            • Collapse
            • Accordion Panel
            • Form Component
            • List Form Component
          • Form Inheritance
          • Form Components
        • UI Components
          • Buttons and Text
            • Badge
            • Button
            • Data Label
            • Dropdown
            • Image
            • Label
          • Grids
            • Data Grid
            • Power Grid
            • Table
          • Input Controls
            • Calendar
            • Calendar Inline
            • Checkbox
            • Choice Group
            • Combobox
            • Group Buttons
            • Html Area
            • Input Group
            • Rating
            • Slider
            • Switch
            • Spinner
            • TextArea
            • Textbox
            • TextBoxGroup
            • Type Ahead
            • select2tokenizer
          • Media
            • Carousel
            • Embedded YouTube
            • File upload
            • Image label
            • Lightbox Gallery
            • MultiFile Upload
            • PDF JS Viewer
            • PDF Viewer
          • Mobile
            • Native Data List
            • Native Select
          • Navigation
            • Breadcrumbs
            • Navbar
            • sidenav
          • Visualization
            • DBTreeview
            • Progress Bar
            • Treeview
            • ChartJS
              • Data Labels Plugin
              • Funnel Chart
              • OutLabels Plugin
              • TreeMap
          • Specialized Components
            • Smart Document Editor
            • Servoy PDF Viewer
            • Fullcalendar Component
              • Fullcalendar
              • Fullcalendar Events
              • Migrating calendar from Smart and Web client to NG client
            • Fullcalendar Component 2
            • Canvas
            • Google Charts
            • Google Maps
            • svySignature
            • svywebcam
        • Styling and Themes
          • Default Theme Properties
          • CSS / LESS
          • Component Variants
          • Font Icons
        • Data Modeling
          • Databases
            • Tables
              • Columns
              • Calculations
              • Aggregations
              • Methods
              • Events / Triggers
              • Table security
            • Views
            • Procedures
          • Relations
          • Value Lists
          • Menus
          • In-Memory Databases
          • View Datasource
        • Modular Design
      • Programming Guide
        • Scripting the UI
          • Scripting Forms
          • Interacting with Components
          • Navigation
          • Windows, Dialogs and Popups
          • Events Manager
        • Working with Data
          • Concepts
          • Loading
          • Sorting
          • Iterating
          • Searching
            • Find mode
            • Query Builder
            • SQL String
          • Filtering
          • Editing
          • Validation
        • Automation and Scheduling
          • Batch Processor
          • Headless API
          • Scheduler API
        • Creating REST APIs
        • Consuming Webservices
        • Working with Files
          • File Plugin Basics
          • Images
          • Excel
          • PDF
          • JSON
        • Browser Utilities
          • Notifications
            • Web Notifications (Toastr)
            • Web Notifications (Native)
            • Block UI
          • Session Management
          • Keyboard Utilities
          • NG Utils
        • Sending and Receiving Email
        • Servoy Jasper Reports
          • Input Type
          • Display Mode
          • Output format
          • Jaspe Reports Viewer
        • Extensions
          • Modules
            • svyPhonegap
              • First Use Guide
              • Barcode Scanner
              • Browser
              • Camera
              • File
              • Fingerprint
              • Location
              • Network Interface
              • Phonegap
              • Printer
              • Push Notifications
            • svyAPI
              • Getting Started
            • svyLookup
              • Custom Templates
              • API Documentation
              • svyLookup v1
                • API Documentation v1.0.0
                • Base Form svyLookupTable v1.0.0
                • Base Form AbstractLookup v1.0.0
            • svyNavigation
              • API Documentation
                • API svyNavigation
                • API svyNavigationHistory
                • API svyNavigationUX
            • svyPopupFilter
              • Simple Filter Pickers
              • Custom Templates
              • API Documentation
                • API svyToolbarFilter
                • API svyPopupFilter
              • API Documentation v1
                • API svyToolbarFilter v1
                • API svyPopupFilter v1
            • svySearch
            • svySecurity
              • Overview
              • Token-based Auth and SSO (NEW!)
              • API Documentation
              • Getting Started
              • Tenant replication
              • Security Management Console
                • Classic Security Management Console
              • svySecurityUX
              • svyProperties
              • API Doc svyProperties
            • svyUtils
              • Log Manager
              • Log Manager Appenders
              • Smart Doc Editor Utils
              • Custom Dialogs
              • Excel Utils
                • Full API Reference
              • Crypto Utils
      • Security
        • Authentication
        • Permissions
          • UI Permissions
          • Data Permissions
          • Runtime Permissions
        • Audit Logging
        • OAuth
        • JWT
      • Multi-Language Support
      • Mobile Development
        • Getting Started
        • Mobile Design
        • Native Mobile Integrations
        • Building a Native Binary for the App Store
      • Native Desktop Development
        • NGDesktop UI plugin
        • NGDesktop Utils
        • NGDesktop File Plugin
    • Deploy
      • WAR Deployment
      • Docker Deployment
      • Server Configuration
        • Server Settings
        • Server Plugins
        • Database Servers
          • Locks
          • Transactions
          • Oracle
          • SQLServer
        • Client Sessions
        • Batch Processors
        • License Management
        • Monitoring
          • Server Log
          • Browser Console Log
          • Websocket Log
          • Query Performance
          • Client Performance
          • Client Event Tracing
          • Monitoring Servoy with JMX
      • NGClient deployment
      • Troubleshooting
      • Other Topics
        • Security: Cross-site Scripting
        • Running in an iFrame
    • Extend
    • Contribute
  • Reference
    • Servoy Core
      • Object Model
        • Database Server
          • Table
            • Column
            • Calculation
            • Method
            • Aggregation
          • View
          • Procedure
        • Solution
          • Form
          • Form Component
          • Relation
          • Relation Item
          • Table Node
          • Value List
          • Menu
          • Menu item
          • In-Memory Data Source
          • View Foundset Data Source
          • Variable
          • Column Info
          • Media
          • Layout Container
          • Method
        • i18n Message Key
      • Developer API
        • Globals
        • Forms
          • RuntimeForm
            • controller
            • containers
              • RuntimeContainer
            • elements
              • Component
              • RuntimeAccordionPanel
              • RuntimeBean
              • RuntimeButton
              • RuntimeCalendar
              • RuntimeCheck
              • RuntimeCombobox
              • RuntimeComponent
              • RuntimeDataButton
              • RuntimeDataLabel
              • RuntimeGroup
              • RuntimeHtmlArea
              • RuntimeImageMedia
              • RuntimeInsetList
              • RuntimeLabel
              • RuntimeListBox
              • RuntimePassword
              • RuntimePortal
              • RuntimeRadio
              • RuntimeChecks
              • RuntimeRadios
              • RuntimeRectangle
              • RuntimeRtfArea
              • RuntimeSpinner
              • RuntimeSplitPane
              • RuntimeTabPanel
              • RuntimeTextArea
              • RuntimeTextField
              • RuntimeWebComponent
        • JS Lib
          • Array
          • BigInt
          • Boolean
          • Date
          • Function
          • IterableValue
          • Iterator
          • JSON
          • Map
          • Set
          • Math
          • Namespace
          • Number
          • Object
          • Promise
          • QName
          • RegExp
          • Special Operators
          • Statements
          • String
          • XML
          • XMLList
        • Application
          • APPLICATION_TYPES
          • CLIENTDESIGN
          • DRAGNDROP
          • ELEMENT_TYPES
          • LOGGINGLEVEL
          • NGCONSTANTS
          • UICONSTANTS
          • CSSPosition
          • JSDimension
          • JSDNDEvent
          • JSEvent
          • JSLogger
          • JSLogBuilder
          • JSPoint
          • JSUpload
          • JSWindow
          • JSBounds
          • Renderable
          • UUID
        • SolutionModel
          • ALIGNMENT
          • ANCHOR
          • BEVELTYPE
          • CURSOR
          • DEFAULTS
          • FONTSTYLE
          • MEDIAOPTION
          • PAGEORIENTATION
          • PRINTSLIDING
          • SCROLLBAR
          • TITLEJUSTIFICATION
          • TITLEPOSITION
          • UNITS
          • JSBean
          • JSButton
          • JSCalculation
          • JSComponent
          • JSDataSourceNode
          • JSField
          • JSForm
          • JSLabel
          • JSMedia
          • JSMethod
          • JSPart
          • JSPortal
          • JSRelation
          • JSRelationItem
          • JSStyle
          • JSTab
          • JSTabPanel
          • JSValueList
          • JSVariable
          • JSTitle
          • JSChecks
          • JSCombobox
          • JSHeader
          • JSInsetList
          • JSList
          • JSPassword
          • JSRadios
          • JSText
          • JSTextArea
          • JSWebComponent
          • JSLayoutContainer
        • Database Manager
          • QUERY_COLUMN_TYPES
          • SQL_ACTION_TYPES
          • JSColumn
          • JSDataSet
          • JSBaseRecord
          • JSBaseSqlRecord
          • JSBaseFoundset
          • JSBaseSQLFoundset
          • JSFoundSet
          • JSFoundSetUpdater
          • JSRecord
          • JSRecordMarker
          • JSRecordMarkers
          • JSTable
          • JSTableFilter
          • MenuFoundSet
          • MenuItemRecord
          • QBAggregate
          • QBAggregates
          • QBCase
          • QBCaseWhen
          • QBColumn
          • QBColumns
          • QBCondition
          • QBFunction
          • QBFunctions
          • QBGroupBy
          • QBJoin
          • QBJoins
          • QBLogicalCondition
          • QBParameter
          • QBParameters
          • QBPart
          • QBResult
          • QBSearchedCaseExpression
          • QBSelect
          • QBSort
          • QBSorts
          • QBTableClause
          • QBWhereCondition
          • ViewFoundSet
          • ViewRecord
        • Events Manager
          • EVENTS_AGGREGATION_TYPES
          • EventType
        • Menus
          • JSMenu
          • JSMenuItem
        • JSMenuDatasource
        • Utils
        • Client Utils
          • JSBlobLoaderBuilder
        • History
        • Security
        • i18n
        • ServoyException
          • DataException
        • JSUnit
        • Datasources
          • DBDataSource
          • DBDataSourceServer
          • JSConnectionDefinition
          • JSDataSource
          • MemDataSource
          • MenuDataSource
          • SPDataSource
          • SPDataSourceServer
          • ViewDataSource
        • JSViewDatasource
        • servoyDeveloper
        • enum
    • Extensions
      • UI Components
        • Buttons and Text
          • Badge
          • Button
          • Data Label
          • Drop Down
          • Image
          • Label
        • Form Containers
          • Accordion Panel
          • Collapse
          • Form Container
          • Form Component Container
          • List Form Component
          • Default Loading Indicator
          • Error Bean
          • Portal
          • Responsive Container
          • Servoy Default Navigator
          • Slider
          • Split Pane
          • TabPanel
        • Grids
          • Data Grid
          • Power Grid
          • Table
        • Input Controls
          • Calendar
          • Calendar Inline
          • Checkbox
          • Choice Group
          • Combobox
          • Floatinglabel Calendar
          • Floatinglabel Combobox
          • Floatinglabel Textarea
          • Floatinglabel Textbox
          • Floatinglabel Type Ahead
          • Group Buttons
          • Html Area
          • Input Group
          • Rating
          • Select2tokenizer
          • Slider
          • Spinner
          • Switch
          • TextArea
          • TextBox
          • TextBox Group
          • Type Ahead
        • Media
          • Carousel
          • Embedded YouTube
          • File upload
          • Image label
          • Lightbox Gallery
          • MultiFile Upload
          • PDF JS Viewer
          • PDF Viewer
        • Mobile
          • Native Data List
          • Native Select
        • Navigation
          • Breadcrumbs
          • Navbar
          • sidenav
        • SmartDocEditor
          • Smart Document Editor
        • Visualization
          • Canvas
          • Chart
          • Custom List
          • DBTreeview
          • Foundset List
          • Fullcalendar
          • Gauge
          • Google Maps
          • Kanban
          • Progress Bar
          • Treeview
      • Server Plugins
        • amortization
          • AmortizationCalculation
          • Polynomial
        • clientmanager
          • Broadcaster
          • JSClientInformation (cm)
        • excelxport
        • file
          • JSFile
          • JSProgressMonitor
        • headlessclient
          • JSClient
        • http
          • HTTP_STATUS
          • Cookie
          • DeleteRequest
          • GetRequest
          • HeadRequest
          • HttpClient
          • HttpClientConfig
          • OptionsRequest
          • PostRequest
          • PutRequest
          • Response
          • TraceRequest
          • PatchRequest
          • JSFileUpload
        • images
          • JSImage
        • jwt
          • Algorithm
          • Builder
          • JWTClaims
        • mail
          • Attachment
          • MailMessage
        • maintenance
          • JSClientInformation
          • JSColumnObject
          • JSServer
          • JSTableObject
        • mobileservice
          • OfflineDataDescription
        • oauth
          • ClientAuthentication
          • CustomApiBuilder
          • OAuthProviders
          • OAuthRequest
          • OAuthResponse
          • OAuthResponseBinary
          • OAuthResponseJSON
          • OAuthResponseText
          • OAuthService
          • OAuthServiceBuilder
          • OAuthTokenExtractors
          • RequestType
        • pdf_output
        • rawSQL
        • RestWs plugin
        • rest_ws
          • WsContents
          • WsCookie
          • WsRequest
          • WsResponse
        • scheduler
        • serialize
        • textxport
          • DataProviderExport
          • TabExporter
        • udp
          • JSPacket
        • XmlReader
          • XmlNode
      • Browser Plugins
        • Block UI
        • Block UI (ref)
        • Dialogs Plugin
        • Idle
        • Idle (ref)
        • Key Listener
        • Key Listener (ref)
        • NGDesktop File
        • NGDesktop File (ref)
        • NGDesktop UI (ref)
        • NGDesktop Utils (ref)
        • NG Utils (ref)
        • Office Javascript API for Servoy
        • Phonegap
        • Web Notifications (Native)
        • Web Notifications (Toastr)
        • Window Plugin
        • Font Awesome
      • Modules
        • Smart Doc Editor Utils
        • svyAPI
          • Getting Started
        • svyLookup
          • Custom Templates
          • API Documentation
          • svyLookup v1
            • API Documentation v1.0.0
            • Base Form svyLookupTable v1.0.0
            • Base Form AbstractLookup v1.0.0
        • svyLookupExample
        • svyNavigation
          • API Documentation
            • API svyNavigation
            • API svyNavigationHistory
            • API svyNavigationUX
        • svyNavigationUX
        • svyNavigationUXSample
        • svyPopupFilter
          • Simple Filter Pickers
          • Custom Templates
          • API Documentation
            • API svyToolbarFilter
            • API svyPopupFilter
          • API Documentation v1
            • API svyToolbarFilter v1
            • API svyPopupFilter v1
        • svyProperties
        • svySearch
        • svySecurity
          • Overview
          • Token-based Auth and SSO (NEW!)
          • API Documentation
          • Getting Started
          • Tenant replication
          • Security Management Console
            • Classic Security Management Console
          • svySecurityUX
          • svyProperties
          • API Doc svyProperties
        • svySecurityConsole
        • svySecurityUX
        • svyUtils
          • Log Manager
          • Log Manager Appenders
          • Smart Doc Editor Utils
          • Custom Dialogs
          • Excel Utils
          • Full API Reference
          • Crypto Utils
        • svyUtils$Excel
        • svyUtils$NGClient
        • svyUtils$customDialogs
        • svyUtils$logManagerAppenders
        • svyUtils$tableGrid
      • Layout
        • Boostrap 12grid layout
          • Collapsible Container
          • Simple Collapsible
          • Center Container
          • Inline Group Container
          • Flexbox Layout
      • Solutions
        • Sample Application
          • Tutorial Part 1
          • Tutorial Part 2
      • Packages
        • UI Component Packages
          • Advanced Renderers
          • Bootstrap Components
          • Bootstrap Extra Components
          • Canvas
          • Chart JS
          • Fullcalendar Component
          • googlemaps
          • kanban
          • Servoy Core
          • Servoy Extra Components
          • Servoy NG-Grids
          • Servoy PDF Viewer
          • smartDocumentEditor
        • Browser Plugin Packages
          • Block UI
          • Core NG only Services
          • Key Listener
          • NGDesktop File
          • NGDesktop UI
          • NGDesktop Utils
          • Web Notifications
          • Idle Web Service
    • Servoy Developer
      • Menu
        • File
        • Edit
        • Source
        • Refactor
        • Navigate
        • Search
        • Project
        • Actions
        • Run
        • Window
        • Help
      • Toolbar
      • Solution Explorer
        • Resources
          • Database Servers
            • Database Server
              • Procedures
              • Tables
              • Views
          • Security
          • i18n
        • All Solutions
          • Solution
          • Active Solution
            • Scopes
              • Scope
                • Variables
            • Forms
              • Working Set
              • Form
                • Controller
                • Variables
                • Elements
                • Relations
            • Form Components
              • Form Component
            • Relations
              • Relation
            • ValueLists
              • ValueList
            • Menus
              • Menu
                • MenuItem
            • Media
              • Folder
              • File
            • DataSources
              • In Memory DataSources
                • In Memory DataSource
              • View Foundsets DataSources
                • View Foundset DataSources
            • Servoy Packages
              • Package
            • Modules
              • Module
        • Solution Explorer Contextual List
      • Views
        • Command Console
        • Console
        • Problems
        • Tasks
        • Bookmarks
        • Search
        • Form Hierarchy
        • Help
        • JUnit
        • Outline
        • Profiler
        • Properties
        • Project Explorer
        • Call Hierarchy
      • Editors and Wizards
        • Property configurator for columns
        • Relation Editor
        • Text Format Editor
        • Date Format Editor
        • Number Format Editor
        • Database Synchronization Wizard
        • Security Editor
        • SQL Editor
        • i18n Editor
        • File Import Wizard
        • File Export Wizard
        • WAR Export Wizard
        • NGDesktop Export Wizard
        • Table Editor
        • Metadata Synchronization Wizard
        • Database Server Connection Editor
        • Text Property Editor
        • Valuelist Editor
        • Form Editor
          • Parts of the Form Editor
          • Designing a Form
          • Layout Tools
          • Using Containers
          • Creating and Using Forms
          • Using Servoy Beans
          • Using Shapes
          • Form Editor Subtabs
          • [Tips and Shortcuts][reference/servoy-developer/object-editors/form-editor-tips_and_shortcuts.md]
          • Editong a Responsive LayoutForm
          • CSS Positioning
        • Form Hierarchy
        • Component Properties Editor
        • Variable Editor
        • Method Selection Wizard
        • New Solution Wizard
        • Application Event Types Editor
        • New Form Wizard
        • Form Dataproviders Configurator
        • Servoy Resource Locator
        • Datasource Selection Wizard
        • Sorting Fields Wizard
        • Scripting Editor
          • Getting Started-Overview of Script Editor
          • Code Writing Features
          • Code Navigation
          • Code Rewriting Tools
          • Refactoring
          • Code Formatting
          • Script Editor Tips and Shortcuts
          • Unit Tests
        • Theme Editor
          • General Properties
          • Sidenav Properties
          • Navbar Properties
          • Tabs Properties
          • Windows Properties
          • Dialogs Properties
          • Tables Properties
          • Breadcrumb specific style Properties
          • Brand colors Properties
          • Validations Properties
          • Margins and Padding Properties
        • Editor Selection
        • Run Configurations
        • Debug Configurations
        • Externalize Strings Wizard
        • Font Chooser Wizard
        • Color Chooser Wizard
        • Project Properties Configurator
        • Move Wizard
        • External Tools Configurations
        • About Servoy Developer
        • Servoy Developer Installation Details
        • Eclipse Marketplace Wizard
        • Install Available Software Wizard
        • Cheat Sheet Selection Wizard
        • Servoy Developer Help Wizard
        • Switch to Editor Wizard
        • Find Actions Wizard
        • Customize Perspective Configurator
        • Quick Search Wizard
        • Search Wizard
        • Encoding Wizard
        • Task Wizard
        • Find - Replace Wizard
        • Properties Editor
        • New Project Wizard
        • New Method Wizard
        • New Menu Wizard
          • New MenuItem Wizard
        • Menu Editor
        • ServoyMenu Selection Wizard
        • JSMenu compatible component selector
        • Permissions Configuration Dialog
      • Project File Structure
        • Servoy Installation Directory
          • servoy.properties
        • Workspace Folder
          • Resources Directory
            • Database Information (.dbi) Files
          • Solution Folder
      • Package Manager
      • Preferences
      • Property Types for components / services
      • Debugger
        • Debug Explorer
        • Breakpoints
          • Breakpoint Properties
        • Variables
        • Expressions
        • Interactive Console
    • Application Server
    • Servoy Cloud
      • Cloud Control Center
        • Home
          • Setup Namespace
          • User profile
            • My Profile
            • Support
        • Download IDE
        • Application Overview
          • Applications
            • Pipelines
              • Jobs
                • Packages
                  • Commits
                  • Artifacts
                  • Configuration
                  • Quality reports
                  • Build markers
                • Job Configuration
                  • Build and Deploy or Build
                  • Mobile
                  • Desktop
            • Environments
              • Metrics
                • Users
                • Databases
                  • Metrics
                • System Health
                • Log Activity
                • App performance
                • Query performance
              • Cloud reporting
            • Security
              • Permissions
              • Tenants
            • Reports
            • Security Login Designer
              • Single Sign-On
                • Sign in with Google
                • Sign in with Microsoft
          • Code Repositories
        • Project Management
          • Tickets
          • Backlog
          • Active Sprints
        • User Management
          • Users
          • Roles
        • Administration
          • Security
            • ServoyCloud Roles
          • Credentials
          • Settings
        • Add-Ons
      • Database Backup
      • E2E Cypress Testing
        • Using Cypress
      • On-Premise Deployment
      • Privacy policy (ServoyAI)
      • FAQ
      • Migrate to Servoy Cloud
    • Extensions Developement
      • Component and Services
        • Component Development
        • Service Development
        • Tools and Dev Tips to use for Component/Service developement
        • Manifest (.mf file)
        • Specification (.spec file)
          • Property Types
            • Array property types
            • Custom object property types
            • Findmode property type
            • Foundset property type
            • Tags
        • Directives And Filters
          • Sablotabsequence
        • Console (serverside)
        • ServoyApi (serverside)
      • Serverside Plugins
        • Data Convertors and Validators
        • UI Convertors
  • Release notes
    • Release notes
      • 2025.03
      • 2024.03 (LTS)
      • 2023.03 (LTS)
      • 2025.03 (Servoy Cloud)
      • 2024.12
      • 2024.12 (Servoy Cloud)
      • 2024.09
      • 2024.09 (Servoy Cloud)
      • 2024.06
      • 2024.06 (Servoy Cloud)
      • 2024.03 (Servoy Cloud)
      • 2023.12
      • 2023.12 (Servoy Cloud)
      • 2023.09
      • 2023.09 (Servoy Cloud)
      • 2023.06
      • 2022.03 (LTS)
      • 2022.12 What's new
      • 2022.12
      • 2022.09 What's new
      • 2022.09
      • 2022.06 What's new
      • 2022.06
      • 2021.03.3 (LTS)
Powered by GitBook
On this page
  • Overview
  • Properties Summarized
  • Methods Summarized
  • Properties Detailed
  • aggregates
  • and
  • case
  • columns
  • comment
  • functions
  • groupBy
  • having
  • joins
  • or
  • params
  • parent
  • result
  • root
  • sort
  • where
  • Methods Detailed
  • clearHaving()
  • exists(query)
  • getColumn(name)
  • getColumn(columnTableAlias, name)
  • getDataSet(max_returned_rows)
  • getDataSet(max_returned_rows, useTableFilters)
  • getDataSource()
  • getFoundSet()
  • getParameter(name)
  • getSQL()
  • getSQL(includeFilters)
  • getSQLParameters()
  • getSQLParameters(includeFilters)
  • getTableAlias()
  • inline(number)
  • inline(number, columnForType)
  • inline(string)
  • not(cond)
  • not(cond)

Was this helpful?

  1. Reference
  2. Servoy Core
  3. Developer API
  4. Database Manager

QBSelect

PreviousQBSearchedCaseExpressionNextQBSort

Last updated 4 months ago

Was this helpful?

Overview

QBSelect is a wrapper for building SQL SELECT queries in Servoy, providing a flexible API to add conditions, sorting, grouping, joins, and parameters to SQL-based queries. Through structured access to clauses like where, groupBy, and joins, QBSelect supports complex query construction and parameterized queries.

For detailed query building, see in the Servoy documentation.

Properties Summarized

Type
Name
Summary

Get the aggregates clause from a query, used for aggregates that are not tied to a column.

Create an AND-condition to add conditions to.

Create an case searched expression.

Get all the columns of the datasource that can be used for this query (select or where clause)

Specifies a comment of the query.

Get the functions clause from a query, used for functions that are not tied to a column.

Get the group by clause from a query

Get the having-part of the query, used to add conditions.

Get the joins clause of this table based clause.

Create an OR-condition to add conditions to.

Get the named parameters from a query.

Get query builder parent table clause, this may be a query or a join clause.

Get the result part of the query, used to add result columns or values.

Get query builder parent.

Get the sorting part of the query.

Get the where-part of the query, used to add conditions.

Methods Summarized

Type
Name
Summary

Clear the having-part of the query.

Get an exists-condition from a subquery

Get a column from the table.

Get a column from the table with given alias.

Performs a sql query with a query builder object.

Performs a sql query with a query builder object.

Returns the datasource for this.

Returns a foundset object for a specified pk base query.

Get or create a parameter for the query, this used to parameterize queries.

Returns the internal SQL of the QBSelect.

Returns the internal SQL of the QBSelect.

Returns the parameters for the internal SQL of the QBSelect.

Returns the parameters for the internal SQL of the QBSelect.

Returns the table alias for this.

Create an inlined value.

Create an inlined value converted to the type of the column.

Create an inlined (quoted) value.

Create an negated condition.

Create an negated condition.

Properties Detailed

aggregates

Get the aggregates clause from a query, used for aggregates that are not tied to a column.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.result.add(query.aggregates.count().add(query.columns.countryCode)
query.groupBy.add(query.columns.countryCode)
var ds = databaseManager.getDataSetByQuery(query, 100);

and

Create an AND-condition to add conditions to.

Sample

query.where.add(
	  query.or
	    .add(
	      query.and
		    .add(query.columns.flag.eq(1))
	    .add(query.columns.order_date.isNull)
		 )
	    .add(
	      query.and
	        .add(query.columns.flag.eq(2))
	        .add(query.columns.order_date.gt(new Date()))
	     )
	);

case

Create an case searched expression.

Sample

var query = datasources.db.example_data.order_details.createSelect();

// case expressions can be added to the result of the query
	query.result.add(query.case.when(query.columns.quantity.ge(1000)).then('BIG').else('small'));

 // they can also be used in conditions
	query.where.add(query.case
		.when(query.columns.discount.gt(10)).then(50)
		.when(query.columns.quantity.le(20)).then(70)
		.else(100)
	.multiply(query.columns.unitprice).lt(10000));

columns

Get all the columns of the datasource that can be used for this query (select or where clause)

Sample

var query = foundset.getQuery();
query.result.add(query.columns.name, "name");
query.where.add(query.columns.orderdate.isNull)

comment

Specifies a comment of the query.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.comment = 'Query comment'

functions

Get the functions clause from a query, used for functions that are not tied to a column.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.where.add(query.columns.shipname.upper.eq(query.functions.upper('servoy')))
foundset.loadRecords(query)

groupBy

Get the group by clause from a query

Sample

var query = datasources.db.example_data.orders.createSelect();
query.groupBy.addPk() // have to group by on pk when using having-conditions in (foundset) pk queries
.root.having.add(query.joins.orders_to_order_details.columns.quantity.count.eq(0))
foundset.loadRecords(query)

having

Get the having-part of the query, used to add conditions. The conditions added here are AND-ed.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.groupBy.addPk() // have to group by on pk when using having-conditions in (foundset) pk queries
.root.having.add(query.joins.orders_to_order_details.columns.quantity.count.eq(0))
foundset.loadRecords(query)

joins

Get the joins clause of this table based clause. Joins added to this clause will be based on this table clauses table.

Sample

foundset.getQuery().joins

or

Create an OR-condition to add conditions to.

Sample

query.where.add(
	  query.or
	    .add(
	      query.and
		    .add(query.columns.flag.eq(1))
	    .add(query.columns.order_date.isNull)
		 )
	    .add(
	      query.and
	        .add(query.columns.flag.eq(2))
	        .add(query.columns.order_date.gt(new Date()))
	     )
	);

params

Get the named parameters from a query.

Sample

var query = datasources.db.example_data.orders.createSelect();
	query.where.add(query.columns.contact_id.eq(query.getParameter('mycontactid')))

	// load orders where contact_id = 100
	query.params['mycontactid'] = 100
	foundset.loadRecords(query)

	// load orders where contact_id = 200
	query.params['mycontactid'] = 200
	foundset.loadRecords(query)

parent

Get query builder parent table clause, this may be a query or a join clause.

Sample

var query = datasources.db.example_data.person.createSelect();
	query.where.add(query.joins.person_to_parent.joins.person_to_parent.columns.name.eq('john'))
	foundset.loadRecords(query)

result

Get the result part of the query, used to add result columns or values.

Sample

query.result.add(query.columns.company_id).add(query.columns.customerid)

root

Get query builder parent.

Sample

var subquery = datasources.db.example_data.order_details.createSelect();

	var query = datasources.db.example_data.orders.createSelect();
	query.where.add(query
		.or
			.add(query.columns.order_id.not.isin([1, 2, 3]))

			.add(query.exists(
					subquery.where.add(subquery.columns.orderid.eq(query.columns.order_id)).root
			))
		)

	foundset.loadRecords(query)

sort

Get the sorting part of the query.

Sample

var query = datasources.db.example_data.orders.createSelect();
query.sort
.add(query.joins.orders_to_order_details.columns.quantity.desc)
.add(query.columns.companyid)
foundset.loadRecords(query)

where

Get the where-part of the query, used to add conditions. The conditions added here are AND-ed.

Sample

var query = foundset.getQuery()
query.where.add(query.columns.flag.eq(1))

Methods Detailed

clearHaving()

Clear the having-part of the query.

Sample

var q = foundset.getQuery()
q.where.add(q.columns.x.eq(100))
query.groupBy.clear.root.clearHaving()
foundset.loadRecords(q);

exists(query)

Get an exists-condition from a subquery

Parameters

Sample

foundset.query.where.add(query.exists(query2))

getColumn(name)

Get a column from the table.

Parameters

Sample

foundset.getQuery().getColumn('orderid')

getColumn(columnTableAlias, name)

Get a column from the table with given alias. The alias may be of the main table or any level deep joined table.

Parameters

Sample

foundset.getQuery().getColumn('orderid', 'opk')

getDataSet(max_returned_rows)

Performs a sql query with a query builder object. Same as databaseManager.getDataSetByQuery. Will throw an exception if anything did go wrong when executing the query.

Using this variation of getDataSet any Tablefilter on the involved tables will be taken into account.

Parameters

Sample

// use the query from foundset and add a condition
/** @type {QBSelect<db:/example_data/orders>} */
var q = foundset.getQuery()
q.where.add(q.joins.orders_to_order_details.columns.discount.eq(2))
var maxReturnedRows = 10;//useful to limit number of rows
var ds = q.getDataSet( maxReturnedRows);

// query: select PK from example.book_nodes where parent = 111 and(note_date is null or note_date > now)
var query = datasources.db.example_data.book_nodes.createSelect().result.addPk().root
query.where.add(query.columns.parent_id.eq(111))
	.add(query.or
	.add(query.columns.note_date.isNull)
	.add(query.columns.note_date.gt(new Date())))
query.getDataSet(max_returned_rows)

getDataSet(max_returned_rows, useTableFilters)

Performs a sql query with a query builder object. Same as databaseManager.getDataSetByQuery. Will throw an exception if anything did go wrong when executing the query.

Parameters

Sample

// use the query from a foundset and add a condition
/** @type {QBSelect<db:/example_data/orders>} */
var q = foundset.getQuery()
q.where.add(q.joins.orders_to_order_details.columns.discount.eq(2))
var maxReturnedRows = 10;//useful to limit number of rows
var ds = q.getDataSet(true, maxReturnedRows);

// query: select PK from example.book_nodes where parent = 111 and(note_date is null or note_date > now)
var query = datasources.db.example_data.book_nodes.createSelect().result.addPk().root
query.where.add(query.columns.parent_id.eq(111))
	.add(query.or
	.add(query.columns.note_date.isNull)
	.add(query.columns.note_date.gt(new Date())))
query.getDataSet(true, max_returned_rows)

getDataSource()

Returns the datasource for this.

getFoundSet()

Returns a foundset object for a specified pk base query. Same as databaseManager.getFoundSet(QBSelect).

Sample

var qb = datasources.db.example_data.orders.createSelect();
qb.result.addPk();
qb.where.add(qb.columns.product_id.eq(1))
var fs = qb.getFoundSet();

getParameter(name)

Get or create a parameter for the query, this used to parameterize queries.

Parameters

Sample

var query = datasources.db.example_data.orders.createSelect();
	query.where.add(query.columns.contact_id.eq(query.getParameter('mycontactid')))

	// load orders where contact_id = 100
	query.params['mycontactid'] = 100
	foundset.loadRecords(query)

	// load orders where contact_id = 200
	query.params['mycontactid'] = 200
	foundset.loadRecords(query)

getSQL()

Returns the internal SQL of the QBSelect. Table filters are on by default.

Sample

var sql = query.getSQL(true)

getSQL(includeFilters)

Returns the internal SQL of the QBSelect. Table filters are on by default.

Parameters

Sample

var sql = query.getSQL(true)

getSQLParameters()

Returns the parameters for the internal SQL of the QBSelect. Table filters are on by default.

Sample

var parameters = query.getSQLParameters(true)

getSQLParameters(includeFilters)

Returns the parameters for the internal SQL of the QBSelect. Table filters are on by default.

Parameters

Sample

var parameters = query.getSQLParameters(true)

getTableAlias()

Returns the table alias for this.

inline(number)

Create an inlined value. An inlined value is a value that will appear literally in the resulting sql. For example

 	query.where.add(query.columns.custid.eq(query.inline(200)))
 

results in sql

 	where custid = 200
 

And

 	query.where.add(query.columns.custid.eq(200))
 

results in sql

 	where custid = ?
 

with prepared statement value 200.

Inlined values can be used in situations where prepared statement expressions give sql problems, for example in some group-by clauses.

Note that using the same query with different inlined values effectively disables prepared statement caching for the query and may have a negative performance impact.

In case of a string will the value be validated, values that contain a single quote will not be inlined.

Parameters

Sample

var query = datasources.db.example_data.order_details.createSelect();
	var mult = query.columns.unitprice.multiply(query.inline(100, query.columns.unitprice));
	query.result.add(mult);
	query.result.add(query.columns.discount.max);
	query.groupBy.add(mult);

inline(number, columnForType)

Create an inlined value converted to the type of the column.

Parameters

Sample

var query = datasources.db.example_data.order_details.createSelect();
	var mult = query.columns.unitprice.multiply(query.inline(100, query.columns.unitprice));
	query.result.add(mult);
	query.result.add(query.columns.discount.max);
	query.groupBy.add(mult);

inline(string)

Create an inlined (quoted) value.

Parameters

Sample

var query = datasources.db.example_data.order_details.createSelect();
	var mult = query.columns.unitprice.multiply(query.inline(100, query.columns.unitprice));
	query.result.add(mult);
	query.result.add(query.columns.discount.max);
	query.groupBy.add(mult);

not(cond)

Create an negated condition.

Parameters

Sample

foundset.query.where.add(query.not(query.columns.flag.eq(1)))

not(cond)

Create an negated condition.

Parameters

Sample

foundset.query.where.add(query.not(query.columns.flag.eq(1)))

Type the aggregates clause of the query for non-column-bound aggregates.

Type an AND-condition for adding logical conditions.

Type a case expression for conditional logic in the query.

Type

Type the comment associated with this query.

Type the functions clause of the query for non-column-bound functions.

Type the group-by clause of the query.

Type the having-part of the query for adding conditions.

Type

Type an OR-condition for adding logical conditions.

Type the named parameters of the query.

Type

Type the result part of the query for adding result columns or values.

Type

Type the sorting part of the query.

Type the where-part of the query for adding conditions.

Returns: the updated query builder after clearing the having clause.

query the sub query

Returns: a condition that checks the existence of the given subquery.

name the name of column to get

Returns: the QBColumn representing the specified column name.

columnTableAlias the alias for the table

name the name of column to get

Returns: the QBColumn representing the specified column from the table with the given alias.

max_returned_rows The maximum number of rows returned by the query.

Returns: The JSDataSet containing the results of the query.

max_returned_rows The maximum number of rows returned by the query.

useTableFilters use table filters (default true).

Returns: The JSDataSet containing the results of the query.

Returns: the dataSource

Returns: A new JSFoundset with the query as its base query.

name the name of the parameter

Returns: the parameter with the specified name.

Returns: String representing the sql of the Query Builder.

includeFilters include the table filters [default true].

Returns: String representing the sql of the Query Builder.

Returns: An Array with the sql parameter values.

includeFilters include the table filters [default true].

Returns: An Array with the sql parameter values.

Returns: the tableAlias

number value to inline

Returns: an inlined value appearing literally in the resulting SQL.

number value to inline

columnForType convert value to type of the column

Returns: an inlined value converted to the type of the specified column.

string value to inline

Returns: an inlined (quoted) string value.

cond the condition to negate

Returns: a negated version of the specified condition.

cond the logical condition to negate

Returns: a negated version of the specified logical condition.

Query Builder
QBAggregates
QBLogicalCondition
QBCase
QBColumns
String
QBFunctions
QBGroupBy
QBLogicalCondition
QBJoins
QBLogicalCondition
QBParameters
QBTableClause
QBResult
QBSelect
QBSorts
QBLogicalCondition
QBSelect
Object
QBCondition
String
QBColumn
String
String
QBColumn
Number
JSDataSet
Number
Boolean
JSDataSet
String
JSFoundSet
String
QBParameter
String
Boolean
String
Array
Boolean
Array
String
Number
Object
Number
QBColumn
Object
String
Object
QBCondition
QBCondition
QBLogicalCondition
QBCondition
QBAggregates
QBLogicalCondition
QBCase
QBColumns
String
QBFunctions
QBGroupBy
QBLogicalCondition
QBJoins
QBLogicalCondition
QBParameters
QBTableClause
QBResult
QBSelect
QBSorts
QBLogicalCondition
QBSelect
QBCondition
QBColumn
QBColumn
JSDataSet
JSDataSet
String
JSFoundSet
QBParameter
String
String
Array
Array
String
Object
Object
Object
QBCondition
QBCondition
aggregates
and
case
columns
comment
functions
groupBy
having
joins
or
params
parent
result
root
sort
where
clearHaving()
exists(query)
getColumn(name)
getColumn(columnTableAlias, name)
getDataSet(max_returned_rows)
getDataSet(max_returned_rows, useTableFilters)
getDataSource()
getFoundSet()
getParameter(name)
getSQL()
getSQL(includeFilters)
getSQLParameters()
getSQLParameters(includeFilters)
getTableAlias()
inline(number)
inline(number, columnForType)
inline(string)
not(cond)
not(cond)