Builds multi-row inserts for a list of rows (keyword lists or maps), split into commands that stay under Postgres's parameter limit. Run the result with run_batch/1, or with transact_batch/1 for all or nothing. For very large loads, copy/3 is faster.

The columns come from the first row; every row must have the same keys, in any order. A row missing a column raises ArgumentError. The rows are not returned.

Example:

data = [
  [first_name: "John", last_name: "Lennon", address: "123 Main St.", city: "Portland", state: "OR", zip: "98204"],
  [first_name: "Paul", last_name: "McCartney", address: "456 Main St.", city: "Portland", state: "OR", zip: "98204"],
  [first_name: "George", last_name: "Harrison", address: "789 Main St.", city: "Portland", state: "OR", zip: "98204"],
  [first_name: "Paul", last_name: "Starkey", address: "012 Main St.", city: "Portland", state: "OR", zip: "98204"],

]
result = db(:people) |> bulk_insert(data) |> Moebius.Db.transact_batch()

Executes a COUNT query based on the assembled pipeline. Analogous to map/reduce(:count). Filters and joins apply; sort, limit and offset are ignored, since a count is one row.

Example:

{:ok, %{count: count}} =
  db(:users)
  |> filter("order_count > 1")
  |> count
  |> Moebius.Db.run

Specifies the table or view you want to query and returns a QueryCommand struct.

"table" - the name of the table you want to query, such as membership.users :table - the name of the table you want to query, such as :users

Example

result =
  db(:users)
  |> to_list

result =
  db("membership.users")
  |> to_list

Or if you prefer more SQL-like syntax, you can use from, which is an alias for db:

result =
  from(:users)
  |> to_list

Creates a DELETE command

See Moebius.Query.db/1.

Executes a function with the given name, passed as an atom.

Example:

result =
  db(:users)
  |> function(:all_users)

Executes a function with the given name, passed as an atom.

params: - An array of values to be passed to the function.

Example:

result =
  db(:users)
  |> function(:friends, ["mike", "jane"])

Creates a function command

Specifies a GROUP BY for a map/reduce (aggregate) query.

cols - An atom indicating the column to GROUP BY. Will also be part of the SELECT list.

Example:

result =
  db(:users)
  |> map("money_spent > 100")
  |> group(:company)
  |> reduce(:sum, :money_spent)

Specifies a GROUP BY for a map/reduce (aggregate) query that is a string.

cols - A string specifying the column to GROUP BY. Will also be part of the SELECT list.

Example:

result =
  db(:users)
  |> map("money_spent > 100")
  |> group("company, state")
  |> reduce(:sum, :money_spent)

Creates an insert command based on the assembled pipeline

Build a table join for your query. There are a number of options to handle various joins. Joins can also be piped for multiple joins.

:join - set the type of join. LEFT, RIGHT, FULL, etc. defaults to INNER :on - specify the table to join on :foreign_key - specify the tables foreign key column :primary_key - specify the joining tables primary key column :using - used to specify a USING queries list of columns to join on

Example of simple join (assumes primary key is "id" and foreign key is "customer_id"):

  cmd =
    db(:customer)
    |> join(:order)
    |> select

Example specifying the primary key (customer.customer_id):

  cmd =
    db(:customer)
    |> join(:order, primary_key: :customer_id)
    |> select

Example specifying the foreign key (order.customer_number):

  cmd =
    db(:customer)
    |> join(:order, foreign_key: :customer_number)
    |> select

Example of multiple table joins:

  cmd =
    db(:customer)
    |> join(:order, on: :customer)
    |> join(:item, on: :order)
    |> select

Example of outer joins:

  cmd =
      db(:customer)
      |> join(:order, join: :left)
      |> select

Executes a given pipeline and returns the last matching result. You should specify a sort to be sure first works as intended. cols - Any columns (specified as a string) that you want to have aliased or restricted in your return.

      For example `now() as current_time, name, description`. Defaults to "*"

Example:

cheap_skate =
  db(:users)
  |> sort(:money_spent, :desc)
  |> last("first, last, email")

Sets the limit of the return.

bound - And integer limiter

Example:

result =
  db(:users)
  |> limit(20)
  |> to_list

An alias for filter, specifies a range to rollup on for an aggregate query using a WHERE statement.

criteria - A string, atom or list (see filter)

Example:

result =
  db(:users)
  |> map("money_spent > 100")
  |> reduce(:sum, :money_spent)

Offsets the limit and is an alias for skip/1"

Example:

result =
  db(:users)
  |> limit(20)
  |> offset(2)
  |> to_list

See Moebius.Query.sort/2.

See Moebius.Query.sort/3.

A rollup operation that aggregates the mapped result set by the specified operation.

op - An atom indicating what you want to have happen, such as :sum, :avg, :min, :max.

    Corresponds directly to a PostgreSQL rollup function.

Example:

result =
  db(:users)
  |> map("money_spent > 100")
  |> reduce(:sum, :money_spent)

Full text search, ranked with ts_rank_cd. The tsvector is built on the fly, so this scans the table; for a large table, keep a tsvector column with a GIN index and query it directly.

The term goes through websearch_to_tsquery, so anything a person types into a search box works: "red shoes", "O'Brien", "apple -pie", ""exact phrase"".

for: - The string term you want to query against. in: - An atomized list of columns to search against.

Example:

result =
  db(:users)
  |> search(for: "Mike", in: [:first, :last, :email])
  |> run

Creates a SELECT command based on the assembled pipeline. Uses the QueryCommand as its core structure.

cols - Any columns (specified as a string or list) that you want to have aliased or restricted in your return.

      For example `now() as current_time, name, description`, `["name", "description"]` or `[:name, :description]`

Example of String:

command =
  db(:users)
  |> limit(20)
  |> offset(2)
  |> select("now() as current_time, name, description")

#command is a QueryCommand object with all of the pipelined settings applied

Example of List:

command =
  db(:users)
  |> limit(20)
  |> offset(2)
  |> select([:name, :description])

#command is a QueryCommand object with all of the pipelined settings applied

Offsets the limit and is an alias for offset/1"

Example:

result =
  db(:users)
  |> limit(20)
  |> skip(2)
  |> to_list

Sets the order by. Ascending using :asc is the default, you can send in :desc if you like.

col - The atomized name of the column, such as :company dir (optional) - :asc (default) or :desc

Example of single order by:

result =
  db(:users)
  |> sort(:name, :desc)
  |> to_list

Example of multiple order by:

result =
  db(:users)
  |> sort(id: :asc, name: :desc)
  |> to_list

Or if you prefer more SQL-like syntax, you can use "order_by", which is an alias for "sort":

result =
  db(:users)
  |> order_by(id: :asc, name: :desc)
  |> to_list

Executes the SQL in a given SQL file without parameters. Specify the scripts directory by setting the scripts directive in the config. Pass the file name as an atom, without extension.

result = sql_file(:simple)

Executes the SQL in a given SQL file with the specified parameters. Specify the scripts directory by setting the scripts directive in the config. Pass the file name as an atom, without extension.

result = sql_file(:save_user, [1])

Creates a SQL File command

Creates an update command based on the assembled pipeline.

See Moebius.QueryFilter.filter/2.

See Moebius.QueryFilter.filter/3.