Squel helps you quickly and easily build SQL query strings through an object oriented API.
There are two main benefits to using Squel to build your queries:
It requires less effort - instead of having to use brittle methods like string
concatenation and substitution you get a nice clean API to do the hard work for you.
It lets you build flexibly - Sometimes you don't yet know what your final query ought
to look like until you've checked information from various other parts of your program. Representing the
query as an object which can be manipulated allows you to be flexible about how you build it.
Ok, now you're convinced! Before you get stuck in here are a few things to be aware of...
Squel uses method chaining to
make things easy. For example, instead of writing:
var s = squel.select();
s.from("student");
alert( s.toString() ); /* SELECT * FROM student */
You can simply write:
alert( squel.select().from("students").toString() ); // SELECT * FROM students
What's even better, the toString() method gets called automatically by the Javascript interpreter
whenever a string representation of an object is needed. So we can actually simplify the previous statement to:
alert( squel.select().from("students") ); /* SELECT * FROM students */
Note: All of Squel's builders provide the toString() method. It is this
method which constructs the final query string.
Squel does not check to see if your final query is semantically correct (i.e. that it makes
sense). Your database engine already does a good enough job of that. On the plus side Squel allows you
to use proprietary database functions and other complex constructs:
alert(
squel.select()
.from("students")
.field("name")
.field("MIN(test_score)")
.field("MAX(test_score)")
.field("GROUP_CONCAT(DISTINCT test_score ORDER BY test_score DESC SEPARATOR ' ')")
.group("name")
);
/*
SELECT
name,
MIN(test_score),
MAX(test_score),
GROUP_CONCAT(DISTINCT test_score ORDER BY test_score DESC SEPARATOR ' ')
FROM
students
GROUP BY
name
*/
Note: Unless stated otherwise all query builder methods require string arguments. Passing in objects,
arrays or functions will result in exceptions being thrown.
Calling toString() on it now will throw an exception:
try {
alert( squel.select() ); /* remember that toString() is implicitly called here */
} catch (error) {
alert( error ); /* from() needs to be called */
}
Thus, at a minimum the name of a table to fetch data from must be provided:
alert(
squel.select()
.from("students")
);
/* SELECT * FROM students */
Multiple tables
You can select data from multiple tables and provide aliases where needed:
You can specify field names on their own or by the table they belong to:
alert(
squel.select()
.from("students")
.field("id")
.field("students.name")
);
/* SELECT id, students.name FROM students */
Field names can be given aliases in order to customize the column names in the query results :
alert(
squel.select()
.from("students")
.field("id", "Id")
.field("students.name", "Student Name")
);
/* SELECT id AS "Id", students.name AS "Student Name" FROM students */
Database functions can be used in place of field names for more complex results:
alert(
squel.select()
.from("students", "s")
.field("s.id")
.field("s.test_score", "Test score")
.field("DATE_FORMAT(s.date_taken, '%M %Y')", "Taken on")
);
/*
SELECT
s.id,
s.test_score AS "Test score"
DATE_FORMAT(s.date_taken, '%M %Y') AS "Taken on"
FROM
students `s`
*/
If you wish to obtain DISTINCT results you can do so:
alert(
squel.select()
.from("students")
.field("id")
.distinct()
);
/* SELECT DISTINCT id FROM students */
Joins
Squel supports INNER, OUTER, LEFT and RIGHT joins. To do an
INNER join:
alert(
squel.select()
.from("students")
.join("teachers")
);
/* SELECT * FROM students INNER JOIN teachers */
You can specify table aliases:
alert(
squel.select()
.from("students")
.join("teachers", "t")
.outer_join("expelled")
);
/* SELECT * FROM students
INNER JOIN teachers `t`
OUTER JOIN expelled
*/
And you can specify JOIN-ing conditions:
alert(
squel.select()
.field("students.id")
.from("students")
.left_join("teachers", null, "students.id = teachers.student_id")
.right_join("jailed", "j", "j.student_id = students.id")
);
/* SELECT students.id FROM students
LEFT JOIN teachers ON (students.id = teachers.student_id)
RIGHT JOIN jailed `j` ON (j.student_id = students.id)
*/
For an optional parameter such as an alias name, passing null is the same as omitting it.
Filtering
You can add WHERE clause filtering to your query very easily:
alert(
squel.select()
.field("id")
.from("students")
.where("name = 'Thomas'")
);
/* SELECT id FROM students WHERE (name = 'Thomas') */
Multiple filters are automatically combined using AND:
alert(
squel.select()
.field("id")
.from("students")
.where("name = 'Thomas'")
.where("age > 18")
);
/* SELECT id FROM students WHERE (name = 'Thomas') AND (age > 18) */
Also filter using OR or any other operator you like:
alert(
squel.select()
.field("id")
.from("students")
.where("name = 'Thomas' OR age > 18")
.where("id BETWEEN 200 and 300")
);
/* SELECT id FROM students WHERE (name = 'Thomas' OR age > 18) AND (id BETWEEN 200 and 300) */
alert(
squel.select()
.field("id")
.from("students")
.where(
squel.expr().and("name = 'Thomas'").or("age > 18")
)
);
/* SELECT id FROM students WHERE (name = 'Thomas' OR age > 18) */
Sorting
alert(
squel.select()
.field("id")
.from("students")
.order("id")
.order("name")
);
/* SELECT id FROM students ORDER BY id ASC, name ASC */
Use the second argument of order() to change the direction of a sort:
alert(
squel.select()
.field("id")
.from("students")
.order("id")
.order("name", false) /* using TRUE instead would be the same as omitting it. */
);
/* SELECT id FROM students ORDER BY id ASC, name DESC */
Grouping
alert(
squel.select()
.field("id")
.from("students")
.group("id")
);
/* SELECT id FROM students GR0UP BY id */
Grouping on multiple fields is supported:
alert(
squel.select()
.field("id")
.from("students")
.group("id")
.group("students.name")
);
/* SELECT id FROM students GR0UP BY id, students.name */
Ranges
To LIMIT the number of results:
alert(
squel.select()
.from("students")
.limit(10)
);
/* SELECT * FROM students LIMIT 10 */
To remove the previously set limit:
alert(
squel.select()
.from("students")
.limit(10)
.limit(0)
);
/* SELECT * FROM students */
To specify the OFFSET of the first row to return:
alert(
squel.select()
.from("students")
.offset(102)
);
/* SELECT * FROM students OFFSET 102 */
Same as for limit(), you can remove a previously set offset:
alert(
squel.select()
.from("students")
.offset(1)
.offset(0)
);
/* SELECT * FROM students */
Not all field values within a query need to be surrounded by quotes. Squel figures out how to represent each
field value based on its runtime type:
alert(
squel.update()
.table("students")
.set("name", "Fred")
.set("age", 29)
.set("score", 1.2)
.set("graduate", false)
);
/* UPDATE students SET name = "Fred", age = 29, score = 1.2, graduate = FALSE */
You can even use NULL values:
alert(
squel.update()
.table("students")
.set("name", "Fred")
.set("age", 29)
.set("nickname", null)
);
/* UPDATE students SET name = "Fred", age = 29, nickname = NULL */
You can use the usingValuePlaceholders option to stop Squel from automatically placing string
field values within quotes. This is useful if, for example, you're using parameterized queries:
alert(
squel.update({ usingValuePlaceholders: true })
.table("students")
.set("name", "?")
.set("age", 29)
.set("nickname", null)
);
/* UPDATE students SET name = ?, age = 29, nickname = NULL */
Filtering, sorting and limits
The where(), order() and limit() methods are supported the same
way they are by the SELECT builder:
alert(
squel.update()
.table("students")
.set("name", "Fred")
.set("age", 29)
.where("id > 5")
.where("id < 102")
.order("id", false)
.limit(5)
);
/* UPDATE students SET name = "Fred", age = 29 WHERE (id > 5) AND (id < 102) ORDER BY id DESC LIMIT 5 */
alert(
squel.delete()
.from("students")
);
/* DELETE FROM students */
Filtering, sorting and limits
The where(), order() and limit() methods are supported the same way
they are by the SELECT builder:
alert(
squel.delete()
.from("students")
.where("id > 5")
.where("id < 102")
.order("id", false)
.limit(5)
);
/* DELETE FROM students WHERE (id > 5) AND (id < 102) ORDER BY id DESC LIMIT 5 */
And same as for UPDATE queries, you can use the usingValuePlaceholders option to
stop Squel from automatically placing string field values within quotes:
Squel lets you use use complex expressions strings in WHERE and ON clauses. For
example:
alert(
squel.select()
.from("students")
.where("(id < 500 AND (id > 100 OR name <> 'Thomas') AND "
+ "(age BETWEEN 20 AND 25 OR (name <> 'Fred'))) OR (nickname = 'Hardy')")
);
/* SELECT * FROM students WHERE
((id < 500 AND (id > 100 OR name <> 'Thomas') AND (age BETWEEN 20 AND 25 OR (name <> 'Fred')))
OR (nickname = 'Hardy')) */
But having to change an expression string like that later on will be error-prone. It would be nice if you could build it
the same way you build queries. Luckily Squel provides an expression builder just for this purpose. To
get an instance of the builder:
var q = squel.expr();
To build the above expression string these are the calls you would make:
alert(
squel.expr()
.and("id < 500")
.and_begin()
.or("id > 100")
.or("name <> 'Thomas'")
.end()
.and_begin()
.or("age BETWEEN 20 AND 25")
.or("name <> 'Fred'")
.end()
.or("nickname = 'Hardy'")
);
/* id < 500 AND (id > 100 OR name <> 'Thomas') AND (age BETWEEN 20 AND 25 OR name <> 'Fred')
OR nickname = 'Hardy'
*/
Straight away the benefit of using the expression builder can be seen as it has simplified the
expression (AND has precendence over OR). Not to mention that it's easier to
figure out the meaning of the expression looking at this code than from the big string we were
using earlier.
As a convenience expression builder instances can be used in calls to where() and
join() when using the query builders:
alert(
squel.select()
.field("s.id")
.from("students", "s")
.where(
squel.expr()
.and("s.name <> 'Fred'")
.or_begin()
.or("s.id = 5")
.or("s.id = 6")
.end()
)
.join("teachers", "t",
squel.expr()
.and("s.id = t.sid")
.and("t.name = 'Frances'")
)
);
/*
SELECT s.id FROM students `s`
INNER JOIN teachers `t` ON (s.id = t.sid AND t.name = 'Frances')
WHERE (s.name <> 'Fred' AND (s.id = 5 OR s.id = 6))
*/
