MOST Web Framework database-agnostic query module and query language parser of @themost/data
@themost/query
introduces javascript closures-to-SQL prototype.
Write javascript closures and let @themost/query
extract the equivalent SQL statements for querying data:
new QueryExpression().select((x) => {
return {
id: x.id,
name: x.name,
category: x.category,
model: x.model,
price: x.price
}
}).from(Products)
.where((x) => {
return x.price > 500;
}).take(10);
License: BSD-3-Clause
Install @themost/query
npm i @themost/query
Use MOST Web Framework data adapters and connect with any of the available database engines:
An example of using @themost/sqlite#SqliteAdapter
to connect with a SQlite database and querying data:
import { QueryField } from '@themost/query';
import { QueryEntity, QueryExpression } from '@themost/query';
import { SqliteAdapter } from '@themost/sqlite';
// initialize data connection
const db = new SqliteAdapter({
name: 'local',
database: './db/local.db'
});
// get query entity
const Products = new QueryEntity('ProductData');
// build query
const query = new QueryExpression()
.select((x) => {
x.id,
x.name,
x.category,
x.price
})
.from(Products)
.take(25);
// execute query
const results = await db.executeAsync(query);
// and close connection
await db.close();
Features: SQL Select, SQL Select From, SQL Logical Operators, SQL Select Top, SQL Order By, SQL Group By, SQL Where, SQL Insert, SQL Update, SQL Delete, SQL Functions, SQL Date Functions, SQL String Functions
Use QueryExpression
to build SQL select statements:
const q = new QueryExpression().select('id', 'name', 'category', 'model', 'price')
.from('ProductData').orderBy('name');
const SQL = new SqlFormatter().format(q);
SQL > SELECT ProductData.id, ProductData.name, ProductData.category, ProductData.model, ProductData.price FROM ProductData ORDER BY name ASC
Use QueryExpression.and()
and QueryExpression.or()
to build logical SQL statements
const q = new QueryExpression().select('id', 'name', 'category', 'model', 'price')
.where('price').greaterThan(500).and('price').lowerOrEqual(1000)
.from('ProductData').orderBy('name');
const SQL = new SqlFormatter().format(q);
SQL > SELECT ProductData.id, ProductData.name, ProductData.category, ProductData.model, ProductData.price FROM ProductData WHERE ((price>500) AND (price<=1000)) ORDER BY name ASC
const q = new QueryExpression().select('id', 'name', 'category', 'model', 'price')
.where('category').equal('Laptops').or('category').equal('Desktops')
.from('ProductData').orderBy('name');
const SQL = new SqlFormatter().format(q);
SQL > SELECT ProductData.id, ProductData.name, ProductData.category, ProductData.model, ProductData.price FROM ProductData WHERE ((category='Laptops') OR (category='Desktops')) ORDER BY name ASC
or use javascript closures and build your SQL statement:
const Products = new QueryEntity('ProductData');
const q = new QueryExpression().select(function(x) {
return {
id: x.id,
name: x.name,
category: x.category,
price: x.price
}
}).where(function(x) {
return x.price > 500 && x.price <= 1000;
}).from(Products).orderBy(function(x) {
return {
price: x.price
};
});
const SQL = new SqlFormatter().format(q);
Use QueryExpression.take()
and QueryExpression.skip()
to specify the number of records to return.
const q = new QueryExpression().select('id', 'name', 'category', 'model','price')
.from('ProductData').orderBy('name').take(25).skip(25);
const SQL = new SqlFormatter().format(q);
SQL > SELECT ProductData.id, ProductData.name, ProductData.category, ProductData.model, ProductData.price FROM ProductData ORDER BY name ASC LIMIT 25, 25
Use QueryExpression.orderBy()
, QueryExpression.thenBy()
, QueryExpression.thenByDescending()
to sort the result-set in ascending or descending order.
const q = new QueryExpression().select('id', 'name', 'category', 'model','price')
.from('ProductData').orderBy('category').thenByDescending('price');
const SQL = new SqlFormatter().format(q);
SQL > SELECT ProductData.id, ProductData.name, ProductData.category, ProductData.model, ProductData.price FROM ProductData ORDER BY category ASC, price DESC
Use QueryExpression.groupBy()
to group rows that have the same values into summary rows.
const Orders = new QueryEntity('OrderData');
const Products = new QueryEntity('ProductData').as('orderedItem');
let query = new QueryExpression()
.select((x) => {
return {
total: count(x.id),
product: x.orderedItem.name,
orderYear: x.orderDate.getFullYear()
}
})
.from(Orders)
.join(Products).with((x, y) => {
return x.orderedItem === y.id;
})
.groupBy(x => x.orderedItem.name, x => x.orderDate.getFullYear());
const SQL = new SqlFormatter().format(query);
SQL > SELECT COUNT(OrderData.id) AS total, orderedItem.name AS product, YEAR(OrderData.orderDate) AS orderYear FROM OrderData INNER JOIN ProductData AS orderedItem ON OrderData.orderedItem = orderedItem.id GROUP BY orderedItem.name, YEAR(OrderData.orderDate)
Filter records by using comparison operators:
-
equal()
const q = new QueryExpression().select('id', 'name', 'category', 'model','price') .from('ProductData').where('category').equal('Laptops');
-
notEqual()
const q = new QueryExpression().select('id', 'name', 'category', 'model','price') .from('ProductData').where('category').notEqual('Desktops');
-
greaterThan()
const q = new QueryExpression().select('id', 'name', 'category', 'model','price') .from('ProductData').where('price').greaterThan(500);
-
lowerThan()
const q = new QueryExpression().select('id', 'name', 'category', 'model','price') .from('ProductData').where('price').lowerThan(1000);
-
greaterOrEqual()
const q = new QueryExpression().select('id', 'name', 'category', 'model','price') .from('ProductData').where('price').greaterOrEqual(500);
-
lowerOrEqual()
const q = new QueryExpression().select('id', 'name', 'category', 'model','price') .from('ProductData').where('price').lowerOrEqual(1000);
-
in()
const q = new QueryExpression().select('id', 'name', 'category', 'model','price') .from('ProductData').where('category').in([ 'Laptops', 'Desktops' ]);
SQL >
SELECT ProductData.id, ProductData.name, ProductData.category, ProductData.model, ProductData.price FROM ProductData WHERE (category IN ('Laptops', 'Desktops'))
-
notIn()
const q = new QueryExpression().select('id', 'name', 'category', 'model','price') .from('ProductData').where('category').notIn([ 'Laptops', 'Desktops' ]);
SQL >
SELECT ProductData.id, ProductData.name, ProductData.category, ProductData.model, ProductData.price FROM ProductData WHERE (NOT category IN ('Laptops', 'Desktops'))
Use QueryExpression.insert()
to build SQL insert statements
const q = new QueryExpression().insert({
name: 'Laptop XLG 15 16GB',
category: 'Laptops',
model: 'XLG15',
price: 1199.99
}).into('ProductBase');
const SQL = new SqlFormatter().format(q);
SQL > INSERT INTO ProductBase(name, category, model, price) VALUES ('Laptop XLG 15 16GB', 'Laptops', 'XLG15', 1199.99)
Use QueryExpression.update()
to build SQL update statements
const q = new QueryExpression().update('OrderBase').set({
status: 2
}).where('orderDate').lowerThan('2022-10-25');
const SQL = new SqlFormatter().format(q);
SQL > UPDATE OrderBase SET status=2 WHERE (orderDate<'2022-10-25')
Use QueryExpression.delete()
to build SQL delete statements
const q = new QueryExpression().delete('OrderBase').where('id').equal(12020);
const SQL = new SqlFormatter().format(q);
SQL > DELETE FROM OrderBase WHERE (id=12020)
Use QueryExpression.join()
, QueryExpression.leftJoin()
, QueryExpression.rightJoin()
to combine rows from two or more tables, based on a related column between them.
const q = new QueryExpression().select(
'id',
'orderDate',
'orderStatus',
new QueryField('name').from('ProductData').as('product')
).from('OrderData').join('ProductData').with(
new QueryExpression().where(new QueryField('orderedItem').from('OrderData'))
.equal(new QueryField('id').from('ProductData'))
);
const SQL = new SqlFormatter().format(q);
SQL > SELECT OrderData.id, OrderData.orderDate, OrderData.orderStatus, ProductData.name AS product FROM OrderData INNER JOIN ProductData ON (OrderData.orderedItem=ProductData.id)
or use closures:
const Orders = new QueryEntity('OrderData').as('Orders');
const Customers = new QueryEntity('PersonData').as('customer');
const familyName = 'Thomas'
const q = new QueryExpression().from(Orders).select((x) => {
return {
id: x.id,
orderedItem: x.orderedItem,
customerFamilyName: x.customer.familyName,
customerGivenName: x.customer.givenName,
orderStatus: x.orderStatus
}
}).leftJoin(Customers).with((x, y) => {
return x.customer === y.id;
}).where((x, familyName) => {
return x.customer.familyName === familyName;
}, {
familyName
}).orderByDescending((x) => x.orderDate).take(25);
const SQL = new SqlFormatter().format(q);
SQL > SELECT Orders.id AS id, Orders.orderedItem AS orderedItem, customer.familyName AS customerFamilyName, customer.givenName AS customerGivenName, Orders.orderStatus AS orderStatus FROM OrderData AS Orders LEFT JOIN PersonData AS customer ON Orders.customer = customer.id WHERE customer.familyName = 'Thomas' ORDER BY Orders.orderDate DESC LIMIT 25
@themost/query
prototype offers a set of common SQL functions for filtering records.
const Products = new QueryEntity('ProductData');
let query = new QueryExpression()
.select(function(x) {
return {
id: x.id,
name: x.name,
category: x.category,
model: x.model,
price: x.price,
newPrice: x.price + 50
}
})
.from(Products)
.where(function(x) {
return (x.price + 50) > 500;
}).take(10)
.orderByDescending(function(x) {
return {orderDate: x.orderDate };
}).take(25);
const SQL = new SqlFormatter().format(query);
SQL > SELECT ProductData.id AS id, ProductData.name AS name, ProductData.category AS category, ProductData.model AS model, ProductData.price AS price, (ProductData.price + 50) AS newPrice FROM ProductData WHERE (ProductData.price + 50) > 500 ORDER BY ProductData.orderDate DESC LIMIT 25
const Products = new QueryEntity('ProductData');
let query = new QueryExpression()
.select(function(x) {
return {
id: x.id,
name: x.name,
category: x.category,
model: x.model,
price: x.price,
newPrice: x.price - 100
}
})
.from(Products)
.where(function(x) {
return (x.price - 100) > 500;
}).take(10)
.orderByDescending(function(x) {
return {orderDate: x.orderDate };
}).take(25);
const SQL = new SqlFormatter().format(query);
SQL > SELECT ProductData.id AS id, ProductData.name AS name, ProductData.category AS category, ProductData.model AS model, ProductData.price AS price, (ProductData.price - 100) AS newPrice FROM ProductData WHERE (ProductData.price - 100) > 500 ORDER BY ProductData.orderDate DESC LIMIT 25
const Products = new QueryEntity('ProductData');
let query = new QueryExpression()
.select((x) => {
return {
id: x.id,
name: x.name,
category: x.category,
model: x.model,
price: x.price,
newPrice: x.price * 0.75
}
})
.from(Products)
.where((x) => {
return x.price > 500;
})
.take(10);
const SQL = new SqlFormatter().format(query);
SQL > SELECT ProductData.id AS id, ProductData.name AS name, ProductData.category AS category, ProductData.model AS model, ProductData.price AS price, (ProductData.price * 0.75) AS newPrice FROM ProductData WHERE ProductData.price > 500 LIMIT 10
const Products = new QueryEntity('ProductData');
let query = new QueryExpression()
.select((x) => {
return {
id: x.id,
name: x.name,
category: x.category,
model: x.model,
price: x.price,
newPrice: x.price / 1.5
}
})
.from(Products)
.where((x) => {
return x.price > 500;
})
.take(10);
const SQL = new SqlFormatter().format(query);
SQL > SELECT ProductData.id AS id, ProductData.name AS name, ProductData.category AS category, ProductData.model AS model, ProductData.price AS price, (ProductData.price / 1.5) AS newPrice FROM ProductData WHERE ProductData.price > 500 LIMIT 10
The floor() function always rounds down and returns the largest integer less than or equal to a given number.
const Products = new QueryEntity('ProductData');
let query = new QueryExpression()
.select((x) => {
x.id,
x.name,
x.category,
x.model,
x.price
})
.from(Products)
.where((x) => {
return Math.floor(x.price) === 877;
})
.orderBy((x) => x.price)
.take(10);
const SQL = new SqlFormatter().format(query);
SQL > SELECT ProductData.id, ProductData.name, ProductData.category, ProductData.model, ProductData.price FROM ProductData WHERE FLOOR(ProductData.price) = 877 ORDER BY ProductData.price ASC LIMIT 10
The ceil() function always rounds up and returns the smaller integer greater than or equal to a given number.
const Products = new QueryEntity('ProductData');
let query = new QueryExpression()
.select((x) => {
return {
id: x.id,
name: x.name,
category: x.category,
model: x.model,
price: x.price,
alternatePrice: Math.ceil(x.price)
}
})
.from(Products)
.where((x) => {
return Math.ceil(x.price) === 878;
})
.orderBy((x) => x.price)
.take(10);
const SQL = new SqlFormatter().format(query);
SQL > SELECT ProductData.id AS id, ProductData.name AS name, ProductData.category AS category, ProductData.model AS model, ProductData.price AS price, CEILING(ProductData.price) AS alternatePrice FROM ProductData WHERE CEILING(ProductData.price) = 878 ORDER BY ProductData.price ASC LIMIT 10
The round() function returns the value of a number rounded to the nearest number.
const {QueryExpression, SqlFormatter, QueryEntity, QueryField, round} = require("@themost/query");
const Products = new QueryEntity('ProductData');
let query = new QueryExpression()
.select((x) => {
return {
id: x.id,
name: x.name,
category: x.category,
model: x.model,
price: x.price,
newPrice: round(x.price, 1)
}
})
.from(Products)
.where((x) => {
return x.price > 500;
})
.take(10);
const SQL = new SqlFormatter().format(query);
SQL > SELECT ProductData.id AS id, ProductData.name AS name, ProductData.category AS category, ProductData.model AS model, ProductData.price AS price, ROUND(ProductData.price,1) AS newPrice FROM ProductData WHERE ProductData.price > 500 LIMIT 10
The max() function returns the largest value of the selected column.
const {QueryExpression, SqlFormatter, QueryEntity, QueryField, round, max} = require("@themost/query");
const Products = new QueryEntity('ProductData');
let query = new QueryExpression()
.select((x) => {
return {
maxPrice: max(x.price)
}
})
.from(Products)
.where((x) => {
return x.category === 'Laptops';
})
.take(1);
SQL > SELECT MAX(ProductData.price) AS maxPrice FROM ProductData WHERE ProductData.category = 'Laptops' LIMIT 1
The min() function returns the smallest value of the selected column.
onst {QueryExpression, SqlFormatter, QueryEntity, QueryField, min} = require("@themost/query");
const Products = new QueryEntity('ProductData');
let query = new QueryExpression()
.select((x) => {
return {
minPrice: min(x.price)
}
})
.from(Products)
.where((x) => {
return x.category === 'Laptops';
})
.take(1);
const SQL = new SqlFormatter().format(query);
SQL > SELECT MIN(ProductData.price) AS minPrice FROM ProductData WHERE ProductData.category = 'Laptops' LIMIT 1
The count() function returns the number of rows that matches a specified criteria.
const {QueryExpression, SqlFormatter, QueryEntity, QueryField, count} = require("@themost/query");
const Products = new QueryEntity('ProductData');
let query = new QueryExpression()
.select((x) => {
return {
category: x.category,
total: count(x.id)
}
})
.from(Products)
.groupBy((x) => x.category);
const SQL = new SqlFormatter().format(query);
SQL > SELECT ProductData.category AS category, COUNT(ProductData.id) AS total FROM ProductData GROUP BY ProductData.category
The avg() function returns the average value of a numeric column.
const {QueryExpression, SqlFormatter, QueryEntity, QueryField, round, avg} = require("@themost/query");
const Products = new QueryEntity('ProductData');
let query = new QueryExpression()
.select((x) => {
return {
avgPrice: round(avg(x.price),2)
}
})
.from(Products)
.where((x) => {
return x.category === 'Laptops';
})
.take(1);
const SQL = new SqlFormatter().format(query);
SQL > SELECT ROUND(AVG(ProductData.price),2) AS avgPrice FROM ProductData WHERE ProductData.category = 'Laptops' LIMIT 1
The sum() function returns the total sum of a numeric column.
const {QueryExpression, SqlFormatter, QueryEntity, QueryField, round, sum} = require("@themost/query");
const Orders = new QueryEntity('OrderData');
const Products = new QueryEntity('ProductData').as('orderedItem');
let query = new QueryExpression()
.select((x) => {
return {
total: round(sum(x.orderedItem.price),2)
}
})
.from(Orders)
.join(Products).with((x, y) => {
return x.orderedItem === y.id;
})
.where((x) => {
return x.orderedItem.category === 'Laptops';
})
.take(1);
const SQL = new SqlFormatter().format(query);
SQL > SELECT ROUND(SUM(orderedItem.price),2) AS total FROM OrderData INNER JOIN ProductData AS orderedItem ON OrderData.orderedItem = orderedItem.id WHERE orderedItem.category = 'Laptops' LIMIT 1
The getFullYear() method returns the year of the specified date according to local time.
const {QueryExpression, SqlFormatter, QueryEntity, QueryField, round, sum} = require("@themost/query");
const Orders = new QueryEntity('OrderData');
const Products = new QueryEntity('ProductData').as('orderedItem');
let query = new QueryExpression()
.select((x) => {
return {
total: round(sum(x.orderedItem.price),2)
}
})
.from(Orders)
.join(Products).with((x, y) => {
return x.orderedItem === y.id;
})
.where((x) => {
return x.orderedItem.category === 'Laptops' &&
x.orderDate.getFullYear() === 2019
});
const SQL = new SqlFormatter().format(query);
SQL > SELECT ROUND(SUM(orderedItem.price),2) AS total FROM OrderData INNER JOIN ProductData AS orderedItem ON OrderData.orderedItem = orderedItem.id WHERE (orderedItem.category = 'Laptops' AND YEAR(OrderData.orderDate) = 2019)
The getMonth() method returns the month in the specified date according to local time
const {QueryExpression, SqlFormatter, QueryEntity, QueryField, count} = require("@themost/query");
const Orders = new QueryEntity('OrderData');
const Products = new QueryEntity('ProductData').as('orderedItem');
let query = new QueryExpression()
.select((x) => {
return {
month: x.orderDate.getMonth(),
total: count(x.id)
}
})
.from(Orders)
.groupBy((x) => x.orderDate.getMonth())
.where((x) => {
return x.orderDate.getFullYear() === 2019;
});
const SQL = new SqlFormatter().format(query);
SQL > SELECT MONTH(OrderData.orderDate) AS month, COUNT(OrderData.id) AS total FROM OrderData WHERE YEAR(OrderData.orderDate) = 2019 GROUP BY MONTH(OrderData.orderDate)
The getDate() method returns the day of the month for the specified date according to local time.
const {QueryExpression, SqlFormatter, QueryEntity, QueryField, count} = require("@themost/query");
const Orders = new QueryEntity('OrderData');
const Products = new QueryEntity('ProductData').as('orderedItem');
let query = new QueryExpression()
.select((x) => {
return {
dayOfMonth: x.orderDate.getDate(),
total: count(x.id)
}
})
.from(Orders)
.groupBy((x) => x.orderDate.getDate())
.where((x) => {
return x.orderDate.getFullYear() === 2019 && x.orderDate.getMonth() === 1;
});
const SQL = new SqlFormatter().format(query);
SQL > SELECT DAY(OrderData.orderDate) AS dayOfMonth, COUNT(OrderData.id) AS total FROM OrderData WHERE (YEAR(OrderData.orderDate) = 2019 AND MONTH(OrderData.orderDate) = 1) GROUP BY DAY(OrderData.orderDate)
The getHours() method returns the hour for the specified date, according to local time.
const {QueryExpression, SqlFormatter, QueryEntity, QueryField, count} = require("@themost/query");
const Orders = new QueryEntity('OrderData');
let query = new QueryExpression()
.select((x) => {
return {
hours: x.orderDate.getHours(),
minutes: x.orderDate.getMinutes(),
seconds: x.orderDate.getSeconds(),
}
})
.from(Orders)
.where((x) => {
return x.orderDate.getFullYear() === 2019 && x.orderDate.getMonth() === 1;
});
const SQL = new SqlFormatter().format(query);
SQL > SELECT HOUR(OrderData.orderDate) AS hours, MINUTE(OrderData.orderDate) AS minutes, SECOND(OrderData.orderDate) AS seconds FROM OrderData WHERE (YEAR(OrderData.orderDate) = 2019 AND MONTH(OrderData.orderDate) = 1)
The getMinutes() method returns the minutes in the specified date according to local time.
const {QueryExpression, SqlFormatter, QueryEntity, QueryField, count} = require("@themost/query");
const Orders = new QueryEntity('OrderData');
let query = new QueryExpression()
.select((x) => {
return {
hours: x.orderDate.getHours(),
minutes: x.orderDate.getMinutes(),
seconds: x.orderDate.getSeconds(),
}
})
.from(Orders)
.where((x) => {
return x.orderDate.getFullYear() === 2019 && x.orderDate.getMonth() === 1;
});
const SQL = new SqlFormatter().format(query);
SQL > SELECT HOUR(OrderData.orderDate) AS hours, MINUTE(OrderData.orderDate) AS minutes, SECOND(OrderData.orderDate) AS seconds FROM OrderData WHERE (YEAR(OrderData.orderDate) = 2019 AND MONTH(OrderData.orderDate) = 1)
The getSeconds() method returns the seconds in the specified date according to local time.
const {QueryExpression, SqlFormatter, QueryEntity, QueryField, count} = require("@themost/query");
const Orders = new QueryEntity('OrderData');
let query = new QueryExpression()
.select((x) => {
return {
hours: x.orderDate.getHours(),
minutes: x.orderDate.getMinutes(),
seconds: x.orderDate.getSeconds(),
}
})
.from(Orders)
.where((x) => {
return x.orderDate.getFullYear() === 2019 && x.orderDate.getMonth() === 1;
});
const SQL = new SqlFormatter().format(query);
SQL > SELECT HOUR(OrderData.orderDate) AS hours, MINUTE(OrderData.orderDate) AS minutes, SECOND(OrderData.orderDate) AS seconds FROM OrderData WHERE (YEAR(OrderData.orderDate) = 2019 AND MONTH(OrderData.orderDate) = 1)
The startsWith() method determines whether a string begins with the characters of a specified string, returning true or false as appropriate.
const {QueryExpression, SqlFormatter, QueryEntity} = require("@themost/query");
const People = new QueryEntity('PersonData');
let query = new QueryExpression()
.select((x) => {
x.id,
x.familyName,
x.givenName,
x.email,
x.dateCreated
})
.from(People)
.where((x) => {
return x.familyName.startsWith('Cam') === true;
});
const SQL = new SqlFormatter().format(query);
SQL > SELECT PersonData.id, PersonData.familyName, PersonData.givenName, PersonData.email, PersonData.dateCreated FROM PersonData WHERE (PersonData.familyName REGEXP '^Cam') = true
The endsWith() method determines whether a string ends with the characters of a specified string, returning true or false as appropriate.
const {QueryExpression, SqlFormatter, QueryEntity} = require("@themost/query");
const People = new QueryEntity('PersonData');
let query = new QueryExpression()
.select((x) => {
x.id,
x.familyName,
x.givenName,
x.email,
x.dateCreated
})
.from(People)
.where((x) => {
return x.familyName.endsWith('er') === true;
});
const SQL = new SqlFormatter().format(query);
SQL > SELECT PersonData.id, PersonData.familyName, PersonData.givenName, PersonData.email, PersonData.dateCreated FROM PersonData WHERE (PersonData.familyName REGEXP 'er$$') = true
The includes() method performs a search to determine whether one string may be found within another string, returning true or false as appropriate.
const {QueryExpression, SqlFormatter, QueryEntity} = require("@themost/query");
const People = new QueryEntity('PersonData');
let query = new QueryExpression()
.select((x) => {
x.id,
x.familyName,
x.givenName,
x.email,
x.dateCreated
})
.from(People)
.where((x) => {
return x.givenName.includes('Chris') === true;
});
const SQL = new SqlFormatter().format(query);
SQL > SELECT PersonData.id, PersonData.familyName, PersonData.givenName, PersonData.email, PersonData.dateCreated FROM PersonData WHERE (PersonData.givenName REGEXP 'Chris') = true
const {QueryExpression, SqlFormatter, QueryEntity} = require("@themost/query");
const People = new QueryEntity('PersonData');
let query = new QueryExpression()
.select((x) => {
return {
id: x.id,
familyName: x.familyName,
givenName1: x.givenName.toUpperCase(),
givenName: x.givenName
}
})
.from(People)
.where((x) => {
return x.givenName.toUpperCase() === 'CHRISTOPHER';
});
const SQL = new SqlFormatter().format(query);
SQL > SELECT PersonData.id AS id, PersonData.familyName AS familyName, UPPER(PersonData.givenName) AS givenName1, PersonData.givenName AS givenName FROM PersonData WHERE UPPER(PersonData.givenName) = 'CHRISTOPHER'
const {QueryExpression, SqlFormatter, QueryEntity} = require("@themost/query");
const People = new QueryEntity('PersonData');
let query = new QueryExpression()
.select((x) => {
return {
id: x.id,
familyName: x.familyName,
givenName1: x.givenName.substr(0,4),
givenName: x.givenName
}
})
.from(People)
.where((x) => {
return x.givenName.substr(0,4) === 'Chri';
});
const SQL = new SqlFormatter().format(query);
SQL > SELECT PersonData.id AS id, PersonData.familyName AS familyName, SUBSTRING(PersonData.givenName,1,4) AS givenName1, PersonData.givenName AS givenName FROM PersonData WHERE SUBSTRING(PersonData.givenName,1,4) = 'Chri'
The concat() method concatenates the string arguments to the calling string and returns a new string.
const {QueryExpression, SqlFormatter, QueryEntity} = require("@themost/query");
const People = new QueryEntity('PersonData');
let query = new QueryExpression()
.select((x) => {
return {
id: x.id,
familyName: x.familyName,
givenName: x.givenName,
name: x.givenName.concat(' ', x.familyName)
}
})
.from(People)
.where((x) => {
return x.givenName.indexOf('Chri') >= 0;
});
const SQL = new SqlFormatter().format(query);
SQL > SELECT PersonData.id AS id, PersonData.familyName AS familyName, PersonData.givenName AS givenName, CONCAT(PersonData.givenName, ' ', PersonData.familyName) AS name FROM PersonData WHERE (LOCATE('Chri',PersonData.givenName)-1) >= 0
The indexOf() method, given one argument: a substring to search for, searches the entire calling string, and returns the index of the first occurrence of the specified substring.
const {QueryExpression, SqlFormatter, QueryEntity} = require("@themost/query");
const People = new QueryEntity('PersonData');
let query = new QueryExpression()
.select((x) => {
return {
id: x.id,
familyName: x.familyName,
givenName: x.givenName
}
})
.from(People)
.where((x) => {
return x.givenName.indexOf('Chri') >= 0;
});
const SQL = new SqlFormatter().format(query);
SQL > SELECT PersonData.id AS id, PersonData.familyName AS familyName, PersonData.givenName AS givenName FROM PersonData WHERE (LOCATE('Chri',PersonData.givenName)-1) >= 0
git clone http://github.com/themost-framework/query.git