Select Builder

The select builder allows you to create SELECT queries, to fetch data from your database.

.select(...columns:Pointers[])

The select builder factory takes as argument a list of pointers with or without alias. You can also use any aggregation function. 

const {toJson} = require('ship-hold-querybuilder');

sh
    .select('posts.post_id', 'published_at', {
        as: 'author',
        value: sh.select(toJson('"users".*'))
            .from('users')
            .where('users.user_id', '"posts"."user_id"')
            .noop()
    })
    .from('posts')
    .build();

// { text:
//    'SELECT
//          "posts"."post_id",
//          "published_at",
//          (SELECT to_json("users".*) FROM "users" WHERE "users"."user_id" = "posts"."user_id") AS "author"
//     FROM "posts"',
//   values: [] }

You can also use it without any argument: the wildcard sign * will be used instead.

You can also call .select(...args) as a method of an existing select builder with the same effect (if you only know a part of the column list later, when your builder has already been instantiated)

.from(...tables:Pointers[])

This method allows you to specify a list of tables. It takes the same kind of arguments as the select method. 

sh
    .select()
    .from('posts', 'public.users', {
        value: sh.select().from('comments'),
        as: 'comzz'
    })
    .build();

// { text:
//    'SELECT * FROM
//      "posts",
//      "public"."users",
//      (SELECT * FROM "comments") AS "comzz"',
//   values: [] }

If you use a sub query as a table expression, SQL syntax enforces you to use an alias

Joins

There are few methods to join table together. They take the same arguments as the from method. You usually call the on method after you have joined a table to apply a join condition but you can also pass a leftOperand and a rightOperand for usual join equality condition

.join(table: Pointer, operator?, rightOperand?)

Performs an INNER JOIN

.leftJoin(table: Pointer, leftOperand?, rightOperand?)

Performs a LEFT OUTER JOIN

.rightJoin(table: Pointer, leftOperand?, rightOperand?)

Performs a RIGHT OUTER JOIN

You can of course combine multiple joins.

.on(leftOperand: Pointer, operator ?: Operator, rightOperand?: value)

This method returns a proxy of a condition builder which will be revoked whenever you call a method which does not belong to the condition builder. See conditions builder for more details. You use it to create a join condition on a particular join 

sh
    .select()
    .from('posts')
    .leftJoin('users')
    .on('posts.user_id', '"users"."user_id"')
    .build();

// is equivalent to

sh
    .select()
    .from('posts')
    .leftJoin('users', 'posts.user_id', '"users"."user_id"')
    .build();

// > { text:
//    'SELECT * FROM "posts" LEFT JOIN "users" ON "posts"."user_id" = "users"."user_id"',
//   values: [] }


sh
    .select()
    .from('posts')
    .join({
        value: sh
            .select()
            .from('users')
            .where('users.first_name', 'Laurent')
            .noop(),
        as: 'Laurents'
    })
    .on('posts.user_id', '"Laurents".user_id')
    .and('published_at', '>', new Date(2010))
    .build();

// > { text:
//    `SELECT * FROM "posts" JOIN
//     (SELECT * FROM "users" WHERE "users"."first_name" = 'Laurent') AS "Laurents" ON
//     "posts"."user_id" = "Laurents".user_id AND "published_at" > '1970-01-01T00:00:02.010Z'`,
//   values: [] }

Group

You can decide to group some rows and eventually filter those groups.

.groupBy(...columns:string[])

This method allows you to create a group by clause. Just pass column names you wish to group by 

const {count} = require('ship-hold-querybuilder');

// Find how many articles each user has written
sh
    .select(count('*'), 'user_id')
    .from('posts')
    .groupBy('user_id')
    .build();

// { text: 'SELECT count(*), "user_id" FROM "posts" GROUP BY "user_id"',
//   values: [] }

.having(leftOperand: Pointer, operator?, rightOperand?)

This method allows you to apply a filter on grouped rows. It defers from the where clause which applies the filter before the groups are formed. In the same way as the the where method it returns a proxied condition builder so you can chain conditions 

const {count} = require('ship-hold-querybuilder');

// only users who have published more than 3 articles
sh
    .select(count('*'), 'user_id')
    .from('posts')
    .groupBy('user_id')
    .having('posts.count', '>', 3)
    .build();

// { text:
//    'SELECT count(*), "user_id" FROM "posts" GROUP BY "user_id"
//     HAVING "posts"."count" > 3',
//   values: [] }

.where(leftOperand, ?operator, ?rightOperand)

This method returns a proxy of a condition builder which will be revoked whenever you call a method which does not belong to the condition builder. See conditions builder for more details. You use it to create where clause in your select query 

sh
    .select()
    .from('posts')
    .where('posts.user_id', 42)
    .and('published_at', '>', new Date(2010, 1, 1))
    .build();

//{ text:
//    `SELECT * FROM "posts" WHERE
//      "posts"."user_id" = 42 AND
//      "published_at" > '2010-02-01T05:00:00.000Z'`,
//   values: [] }

.orderBy(column: string, direction?: SortDirection)

This method will let you order the result rows according to a given column. The second argument will give the direction: it can be 'desc' either 'asc'. If no direction is provided the direction will be ascendant. 

sh
    .select()
    .from('posts')
    .orderBy('published_at', 'desc')
    .build();

// > { text: 'SELECT * FROM "posts" ORDER BY "published_at" DESC',
//   values: [] }

You can combine different order by clauses by calling multiple times the orderBy method.

Pagination

To have your rows paginated you have to combine the SQL clauses LIMIT and OFFSET. In ship hold this is controlled by the limit method

.limit(size: number, offset?: number)

The first argument will be the size of you page whereas the second one will be the number of row skipped. If you don't provide a second argument, no row will be skipped 

// fetch the third page (page size = 10) of the latest posts
sh
    .select()
    .from('posts')
    .orderBy('published_at', 'desc')
    .limit(10, 20)
    .build();

// { text:
//    'SELECT * FROM "posts" ORDER BY "published_at" DESC LIMIT 10 OFFSET 20',
//   values: [] }

If you want to have consistent pagination result, you have to order your result. Otherwise the database may return different results depending on the query plan it has chosen.