Querying

Reading objects

patio provides a few separate methods for retrieving objects from the database. The underlying
method is forEach, which interates each row as the patio.Database provides it. However, while forEach can and often is used directly, in many cases there is a more convenient retrieval method you can use.

Note For all examples below a generic error handler is used which could be defined as follows

Filtering

The filter method is one of the most used methods when querying a dataset. The filter method similar to where except that it will apply the filter to the WHERE or HAVING clause depending on if a HAVING clause should be used.

Filtering With Objects

The most common format for providing filters is via an object. In general, patio treats conditions specified with an object as equality or inclusion. What type of condition is used depends on the values in the object.

Array of Two Element Arrays

If you use an array of two element arrays, it is treated as an Object. The only advantage to using an array of two element arrays is that it allows you to use values other than strings for keys, so you can do:

Literal Strings

However, if you are using any untrusted input, you should use placeholders. In general, unless you are hardcoding values in the strings, you should use placeholders. You should never pass a string that has been built using concatenation becuase it can lead to SQL injection.

//id is some user input
User.filter("id = " + id) //id could be anything so dont do it!
User.filter("id = ?", id) //Do this as patio will escape it
User.filter({ id : id}) // Best solution!

Selecting columns

NOTE: If you are dealing with Model objects, you'll want to include the primary key if you want to update or remove the object. You'll also want to include any keys (primary or foreign) related to associations you plan to use.

NOTE: If a column is not selected, and you attempt to access it, you will get null:

Join Conditions

The second argument to the specialized join methods is the conditions to use when joining, which is similar to a filter expression, with a few minor exceptions.

Implicit Qualification

An object used as the join conditions operates similarly to a filter, except that keys are automatically qualified with the table from the first argument, and unqualified values, that are sql.Identifiers, are automatically qualified with the first table or the last table joined.

NATURAL Joins

NATURAL Joins assume that all columns with the same names used for joining, so you do not need to use a join expression.

// SELECT * FROM user NATURAL JOIN blog
User.naturalJoin("blog");

Join Blocks

The block should accept 3 arguments, the table alias for the table currently being joined, the table alias for the last table joined (or first table), and an array of previous patio.sql.JoinClauses.

This allows you to qualify columns similar to how the implicit qualification works, without worrying about the specific aliases being used. For example, if you wanted to join the user and blog tables, but only want user where the user's name comes before the blog's title.

From

The FROM table is typically the first clause populated when creating a dataset. For a standard patio.Model, the dataset already has the FROM clause populated, and the most common way to create datasets is with the Database from method.

DB.from("user");
// SELECT * FROM user

However, you can also use the from method on the Dataset.

User.from("user", "oldUser");
// SELECT * FROM user, old_user
//Using from again will remove the previous FROM clause.
DB.from("user").from("oldUser");
// SELECT * FROM old_user

Note: multiple tables in the FROM clause use a cross join by default, so the number of rows will be number of user times the number of old user.

Subselects

Blog.order("userId").limit(100).fromSelf().group("userId");
//SELECT * FROM (SELECT * FROM user ORDER BY user_id LIMIT 100) AS t1 GROUP BY user_id

If you did not use the fromSelf method the query would be:

// SELECT * FROM user GROUP BY user_id ORDER BY user_id LIMIT 100
Blog.order("userId").limit(100).group("userId")

Without fromSelf, you are doing the grouping, and limiting the number of grouped records returned to 100. So assuming you have blogs written by more than 100 user, you'll end up with 100 results.

With fromSelf, you are limiting the number of records before grouping. So if the user with the lowest id had 100 blogs, you'd get 1 result, not 100.

Locking for Update

patio allows you to easily add a FOR UPDATE clause to your queries so that the records returned can't be modified by another query until the current transaction commits. You just use the forUpdate method: