Querying Data
How to query data from the inpaas.core.entity.dao
interface
As normally in any back-end scripts you have to access data from your database storage. These objects are there to help you accessing data using a domain and fluent language.
Your Data Access Objects are created based on your entity name:
var dao = db.getDao("Book");
Retrieving Data
Data in the find
method is retrieved as an array that can be traversed in JavaScript code, or you can use the fetch
, a callback based data retriever.
find
// First, import the Database Entity Module
var bookDao = require("inpaas.core.entity.dao").getDao("Book");
// Now let's find all books from J.K. Rowling
var books = dao.getDao("Book")
.filter({ "ds_author": "J. K. Rowling" })
.sort({ "ds_book": "ASC" })
.limit(50)
.find();
for(var k in books) {
var book = books[k];
// for each row returned
}
fetch
var bookDao = require("inpaas.core.entity.dao").getDao("Book");
// Now let's find all books from J.K. Rowling
var rowsAffected = bookDao.getDao("Book")
.filter({ "ds_author": "J. K. Rowling" })
.sort({ "ds_book": "ASC" })
.limit(50)
.fetch(function(row) {
// for each row returned ...
});
Fetch
The fetch method can be slightly faster since you don't need to traverse a full array, but it depends on your use case.
findByPrimaryKey
If you know the primary key of the record, this method will be a lot faster and shall return a object
or null
of the item hasn't been found.
var book = bookDao.getDao("Book").findByPrimaryKey(15);
findByUniqueKey
If your entity has a ds_key
column, you can use the findByUniqueKey
method, which may be as fast as the primary key, since the ds_key
column is automatically indexed.
var book = bookDao.getDao("Book").findByUniqueKey("my.key");
Filters and Conditions
When using the find
and fetch
methods, you can provide a detailed complex filter with several conditions of search.
filter
The filter
method is used to start building a criteria, it returns a new object name CriteriaBuilder
. Within this object you can use the find
and fetch
methods, as shown in the first example:
bookDao.getDao("Book").filter({ "ds_author": "J. K. Rowling" }).find();
The example above would return all rows from the Book
entity where ds_author
equals "J. K. Rowling".
Other conditions ...
Sometimes you may want to use complex criterias, for that, you will need to use the fluent interface instead of the Object interface.
Instead of passing an Object to the filter
method, use the field name and complete your sentence with the condition:
dao.getDao("Book").filter("ds_isbn").equalsTo("0545139708").find();
This will lookup all books where the ds_isbn
field equals to 0545139708
value.
There are plenty of other operators:
- equalsTo(value)
- isGreaterThan(value)
- isGreaterOrEqTo(value)
- isLessThan(value)
- isLessOrEqTo(value)
- startsWith(value)
- like(value)
- notEqualsTo(value)
- isEmpty()
- isNotEmpty()
Check it out:
dao.getDao("Book")
.filter("ds_isbn").equalsTo("0545139708")
.or("ds_name").startsWith("Harry Potter")
.and("vl_cost").isNotEmpty()
.find();
This should bring all records where vl_cost
is not empty and ds_isbn
equals to 0545139708
or the ds_name
starts with Harry Potter
.
sort
The sort
method can also be used with the find
and fetch
methods and provide the result order.
.sort({ "ds_book": "ASC" })
You must always use the sort method with an object, where the key is the field name, and the value is the sort order for that field: ASC
or DESC
.
limit
The limit
method will limit results to the number of rows provided. Use .limit(10)
for 10 rows.
offset
The offset
method will cause the find
and fetch
methods to return results from that row number on. Use .offset(11)
to return only the 11th row and all else.
offset and limit
Using offset and limit is the best way to page your results in the back-end.
Updated less than a minute ago