Struct sql_query_builder::Select

source ·

pub struct Select { /* private fields */ }

Expand description

Builder to contruct a Select command

Implementations§

source §

impl Select

source

pub fn as_string(&self) -> String

Gets the current state of the Select and returns it as string

§Example

let select_query = sql::Select::new()
  .select("id")
  .from("users")
  .where_clause("login = 'foo'")
  .as_string();

Output

SELECT id FROM users WHERE login = 'foo'

source

pub fn debug(self) -> Self

Prints the current state of the Select to the standard output in a more ease to read version. This method is useful to debug complex queries or just print the generated SQL while you type

§Example

let select = sql::Select::new()
  .select("*")
  .from("users")
  .where_clause("login = foo")
  .where_clause("active = true")
  .debug();

Prints to the standard output

-- ------------------------------------------------------------------------------
SELECT *
FROM users
WHERE login = foo AND active = true
-- ------------------------------------------------------------------------------

You can debug different parts of the select putting it in another position

§Example

let select_query = sql::Select::new()
  .select("*")
  .from("users")
  .debug()
  .where_clause("login = foo")
  .where_clause("active = true")
  .as_string();

Prints to the standard output

-- ------------------------------------------------------------------------------
SELECT *
FROM users
-- ------------------------------------------------------------------------------

source

pub fn from(self, tables: &str) -> Self

The from clause

§Example

let select = sql::Select::new()
  .from("users");

source

pub fn group_by(self, column: &str) -> Self

The group by clause

§Example

let select = sql::Select::new()
  .group_by("id");

source

pub fn having(self, condition: &str) -> Self

The having clause

§Example

let select_query = sql::Select::new()
  .group_by("status")
  .having("status != 'disabled'")
  .as_string();

Output

GROUP BY status HAVING status != 'disabled'

source

pub fn cross_join(self, table: &str) -> Self

The cross join clause

§Example

let select_query = sql::Select::new()
  .from("users")
  .cross_join("addresses")
  .as_string();

Output

FROM users CROSS JOIN addresses

source

pub fn inner_join(self, table: &str) -> Self

The inner join clause

§Example

let select_query = sql::Select::new()
  .from("users")
  .inner_join("addresses on addresses.user_login = users.login")
  .as_string();

Output

FROM users INNER JOIN addresses on addresses.user_login = users.login

source

pub fn left_join(self, table: &str) -> Self

The left join clause

§Example

let select_query = sql::Select::new()
  .from("users")
  .left_join("addresses on addresses.user_login = users.login")
  .as_string();

Output

FROM users LEFT JOIN addresses on addresses.user_login = users.login

source

pub fn right_join(self, table: &str) -> Self

The right join clause

§Example

let select_query = sql::Select::new()
  .from("users")
  .right_join("addresses on addresses.user_login = users.login")
  .as_string();

Output

FROM users RIGHT JOIN addresses on addresses.user_login = users.login

source

pub fn new() -> Self

Creates instance of the Select command

source

pub fn order_by(self, column: &str) -> Self

The order by clause

§Example

let select = sql::Select::new()
  .select("name, login")
  .order_by("login asc");

source

pub fn print(self) -> Self

Prints the current state of the Select to the standard output similar to debug method, the difference is that this method prints in one line.

source

pub fn raw(self, raw_sql: &str) -> Self

Adds at the beginning a raw SQL query.

§Example

let raw_query = "select * from users";

let select_query = sql::Select::new()
  .raw(raw_query)
  .where_clause("users.login = 'foo'")
  .as_string();

Output

select * from users WHERE users.login = 'foo'

source

pub fn raw_after(self, clause: SelectClause, raw_sql: &str) -> Self

Adds a raw SQL query after a specified clause.

§Example

let raw_join = "inner join addresses addr on u.login = addr.owner_login";

let select_query = sql::Select::new()
  .from("users u")
  .raw_after(sql::SelectClause::From, raw_join)
  .where_clause("u.login = foo")
  .as_string();

Output

FROM users u
inner join addresses addr on u.login = addr.owner_login
WHERE u.login = foo

source

pub fn raw_before(self, clause: SelectClause, raw_sql: &str) -> Self

Adds a raw SQL query before a specified clause.

§Example

let raw_query = "from users";

let select_query = sql::Select::new()
  .raw_before(sql::SelectClause::Where, raw_query)
  .where_clause("users.login = 'foo'")
  .as_string();

Output

from users
WHERE users.login = 'foo'

source

pub fn select(self, column: &str) -> Self

The select clause

§Example

let select = sql::Select::new()
  .select("count(id)");

source

pub fn where_and(self, condition: &str) -> Self

This method is un alias of where_clause. The where_and will concatenate mulltiples calls using the and operator. The intention is to enable more idiomatic concatenation of conditions.

§Example

let select_query = sql::Select::new()
  .from("carts")
  .where_clause("login = $1")
  .where_and("session_id = $2")
  .where_and("created_at >= current_date")
  .as_string();

Outputs

FROM carts
WHERE
  login = $1
  AND session_id = $2
  AND created_at >= current_date

source

pub fn where_clause(self, condition: &str) -> Self

The where clause, this method will concatenate mulltiples calls using the and operator. If you intended to use the or operator you should use the where_or method

§Example

let select_query = sql::Select::new()
  .where_clause("login = $1")
  .where_clause("status = 'active'")
  .as_string();

Outputs

WHERE
  login = $1
  AND status = 'active'

source

pub fn where_or(self, condition: &str) -> Self

The where clause that concatenate multiples calls using the OR operator. If you intended to use the and operator you should use the where_clause method

§Example

let select_query = sql::Select::new()
  .where_clause("login = 'foo'")
  .where_or("login = 'bar'")
  .as_string();

Output

WHERE
  login = 'foo'
  OR login = 'bar'

§Example

let select_query = sql::Select::new()
  .where_clause("login = 'foo'")
  .where_or("login = 'bar'")
  .where_clause("login = 'joe'")
  .as_string();

Output

WHERE
  login = 'foo'
  OR login = 'bar'
  AND login = 'joe'

§Example

If the where_or was the first clause then the operator will be ignored

let select_query = sql::Select::new()
  .where_or("login = 'joe'")
  .where_clause("login = 'foo'")
  .as_string();

Output

WHERE
  login = 'joe'
  AND login = 'foo'

source

pub fn window(self, name: &str) -> Self

The window clause

§Example

let select_query = sql::Select::new()
  .window("win as (partition by department)")
  .as_string();

Output

WINDOW win as (partition by department)

source §

impl Select

source

pub fn except(self, select: Self) -> Self

Available on crate features postgresql and sqlite only.

The except clause

§Example

let select_users = sql::Select::new()
  .select("login")
  .from("users");

let select_inactives = sql::Select::new()
  .select("login")
  .from("users")
  .where_clause("status = 'inactive'");

let select_query = select_users.except(select_inactives).as_string();

Output

(SELECT login FROM users)
EXCEPT
(SELECT login FROM users WHERE status = 'inactive')

source

pub fn intersect(self, select: Self) -> Self

Available on crate features postgresql and sqlite only.

The intersect clause

§Example

let select_users = sql::Select::new()
  .select("login")
  .from("users");

let select_inactives = sql::Select::new()
  .select("login")
  .from("users")
  .where_clause("status = 'inactive'");

let select_query = select_users.intersect(select_inactives).as_string();

Output

(SELECT login FROM users)
INTERSECT
(SELECT login FROM users WHERE status = 'inactive')

source

pub fn limit(self, num: &str) -> Self

Available on crate features postgresql and sqlite only.

The limit clause, this method overrides the previous value

§Example

let select = sql::Select::new()
  .limit("123");

let select = sql::Select::new()
  .limit("1000")
  .limit("123");

source

pub fn offset(self, num: &str) -> Self

Available on crate features postgresql and sqlite only.

The offset clause, this method overrides the previous value

§Example

let select = sql::Select::new()
  .offset("1500");

let select = sql::Select::new()
  .offset("1000")
  .offset("1500");

source

pub fn union(self, select: Self) -> Self

Available on crate features postgresql and sqlite only.

The union clause

§Example

let select_users = sql::Select::new()
  .select("login")
  .from("users");

let select_inactives = sql::Select::new()
  .select("login")
  .from("users")
  .where_clause("status = 'inactive'");

let select_query = select_users.union(select_inactives).as_string();

Output

(SELECT login FROM users)
UNION
(SELECT login FROM users WHERE status = 'inactive')

source

pub fn with(self, name: &str, query: impl WithQuery + 'static) -> Self

Available on crate features postgresql and sqlite only.

The with clause

§Example

let logins = sql::Select::new()
  .select("login")
  .from("users")
  .where_clause("id in ($1)");

let select = sql::Select::new()
  .with("logins", logins)
  .select("name, price")
  .from("orders")
  .where_clause("owner_login in (select * from logins)")
  .debug();

Prints to the standard output

-- ------------------------------------------------------------------------------
WITH
logins AS (
  SELECT login
  FROM users
  WHERE id in ($1)
)
SELECT name, price
FROM orders
WHERE owner_login in (select * from logins)
-- ------------------------------------------------------------------------------