read-filter-order.md

Getting and filtering data

You can get data from the database using the db variable

const book = await db.book.find("<uuid>");
const books = await db.book.toArray();
const authorCount = await db.authr.count();

Conditions

Throw in some conditions to filter the data

const books = await db.book
	.where(book => book.title == "Test")
	.toArray();

// this will automatically join the author table and compare the value in there
const alicesBook = await db.book.first(book => book.author.firstname == "Alice");

// will fail if none or multiple items are found
const book = await db.book.single(book => book.title == "A Book");

Columns can be filtered with functions (Full function list)

// case insensitive title matching
await db.book.where(book => book.title.lowercase() == "test").toArray();

// searching for books by title (case insensitive)
await db.book.where(book => book.title.lowercase().startsWith("tes")).toArray();

// searching for autobios by searching for books with the authors name in their title
await db.book.where(book => book.title.uppercase().includes(book.author.firstname.uppercase())).toArray();

Putting everyting into order

postgres can be very funny when it comes to the order of records in a table, so to make sure everyting is ordered properly, use the orderBy methods.

const books = await db.book
	.orderByAscending(book => book.author.lastname)
	.orderByAscending(book => book.title)
	.toArray();

If your ordering column contains NULLs and you want a fallback sorting column, use ||

// some books do not have a release date yet, so we'll use the planned date instead!
const books = await db.book
	.orderByDescending(book => book.releaseDate || book.plannedReleaseDate)
	.toArray();

You can use the afformentioned query functions for sorting! (Full function list)

// case insensitive title sorting
const books = await db.book
	.orderByDescending(book => book.title.lowercase())
	.toArray();

Resolving references

Relations in entities can be resolved like this:

const book = await db.book.find("<uuid>");
const author = await book.author.fetch();

const authorsBooks = await author.books.toArray();

const authorsBooksFrom2001 = await author.books
	.where(book => book.publishedAt.year == 2001) // add a condition
	.orderByAscending(book => book.title) // and an order
	.toArray();

vlquery was designed to be as simple to use as Microsofts EntityFramework. Everybody who used Entity in a big project had to deal with the big implications that come with inexplicit lazy loading, thus we never implemented it into the framework. Every database access requires an await and thus reduces the chances of introducing performance issues. If you want to prefetch certain items to improve performance, you can do it like this. The fetch-call is still required!

const books = await db.book
	.include(book => book.author) // preload authors
	.toArray();

for (let book of books) {
	const author = await book.author.fetch(); // this will resolve instantly
}

Limit, skip and paging

The bigger your database gets, the more important this will be.

const books = await db.book.limit(3).skip(1).toArray();

const booksOnPage3 = await db.book.page(3).toArray(); // 0 = first page
const books51to100 = await db.book.page(1, 50).toArray();

You can set the default page size by setting

Qurey.defaultPageSize = 120;