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_keycolumn, 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

Querying Data


How to query data from the inpaas.core.entity.dao interface

Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.