ColdMVC

Queries

Most of the time you can use ColdMVC's basic ORM methods (get(), list(), findAllWhere(), etc...) for querying your database.

However, sometimes you need to build a complex query and using findAllWhere() won't get the job done.

When this situation occurs, you can use the find() or findAll() methods on your model and write raw HQL. While writing HQL is perfectly fine, it can sometimes lead to problems due to Hibernate's case-sensitivity and casting ColdFusion variables to Java data types.

A better option is to use ColdMVC's query builder using the createQuery() method on your models.

In addition to all of the standard ColdMVC operators, the following methods are available when building a query:

and

Appends clauses to a query concatenated by an and conjunction.

var q = _Team.createQuery();

q.innerJoin("city");

q.where(
    q.and(
        q.eq("team.name", "Twins"),
        q.eq("city.name", "Minneapolis")
    )
);

var teams = q.list();

andWhere

Appends clauses to a query concatenated by an and conjunction.

var q = _Team.createQuery();

q.innerJoin("city");

q.where(
    q.eq("team.name", "Twins")
);

q.andWhere(
    q.eq("city.name", "Minneapolis")
);

var teams = q.list();

count

Executes the query and returns the number of results.

var q = _Team.createQuery();

var teams = q.count();

get

Executes the query and returns the first result or a new instance of the model if no results are found.

var q = _Team.createQuery();

q.innerJoin("city");

q.where(
    q.eq("team.name", "Twins"),
    q.eq("city.name", "Minneapolis")
);

var team = q.get();

innerJoin

Adds an inner join to the query.

var q = _Team.createQuery();

q.innerJoin("team.city");

q.where(
    q.eq("city.name", "Minneapolis")
);

var teams = q.list();

join

Adds a join to the query.

var q = _Team.createQuery();

q.join("city");

q.where(
    q.eq("city.name", "Minneapolis")
);

var teams = q.list();

leftJoin

Adds a left join to the query.

var q = _Team.createQuery();

q.leftJoin("team.city", "c");

q.where(
    q.isNull("c.id");
);

var teams = q.list();

list

Executes the query and returns an array of results.

var q = _Team.createQuery();

var teams = q.list();

max

Specifies the maximum number of results for the query.

var q = _Team.createQuery();

q.max(10);

var teams = q.list();

offset

Specifies the offset for the query.

var q = _Team.createQuery();

q.offset(20);

var teams = q.list();

or

Appends clauses to a query concatenated by an or disjunction.

var q = _Team.createQuery();

q.innerJoin("city");

q.where(
    q.or(
        q.eq("team.name", "Twins"),
        q.eq("city.name", "Minneapolis")
    )
);

var teams = q.list();

order

Specifies the order for the query.

var q = _Team.createQuery();

q.order("asc");

var teams = q.list();

orWhere

Appends clauses to a query concatenated by an or disjunction.

var q = _Team.createQuery();

q.innerJoin("city");

q.where(
    q.eq("team.name", "Twins")
);

q.orWhere(
    q.eq("city.name", "Minneapolis")
);

var teams = q.list();

select

Specifies the select statement for the query. Can be used to execute custom select statements.

var q = _Team.createQuery();

q.select("select count(team.id)");

var teams = q.get();

sort

Specifies the sort for the query.

var q = _Team.createQuery();

q.sort("name");

var teams = q.list();

where

Appends clauses to a query concatenated by an and conjunction.

var q = _Team.createQuery();

q.where(
    q.eq("name", "Twins")
);

var teams = q.list();