使用神奇Elixir SQL dust生成复杂的SQL查询

sssssstttt 9年前

来自： https://github.com/archan937/sql_dust

SqlDust

使用神奇Elixir SQL dust生成复杂的SQL查询，具有简单，强大和简便的特性。

Introduction

Every language has its commonly used libraries / gems / packages to interact with database. Ruby hasActiveRecord and Elixir hasEcto. They provide a lot of functionality which are very useful but when it comes to quickly and easily querying tabular data they require too much hassle:

you have to describe models representing tables (example)
you have to describe how to join tables ( example )
using the query DSL requires a bit of reading and understanding how to use it

Actually, you do not want to waste time specifying how to join tables and thinking about table aliases when you have followed the standard naming convention. And you do not want to think about putting a condition in the WHERE or HAVING statement.

The solution is to think in paths (e.g. company.tags.name ) and letting the package do the magic regarding joining table and to use SELECT statement aliases to determine HAVING statements.

Enter SqlDust . It makes it as easy and simple as possible for the developer to generate SQL queries:

no models setup
no joins specifications
no DSL to learn

Just focus on what really matters! ;)

An example

Based on standard naming conventions, SqlDust will determine how to join tables. You just have to specify from which resource (table) to query from and which columns to select using paths:

iex(1)> IO.puts SqlDust.from("users", %{select: ~w(id first_name company.category.name)})  SELECT    `u`.id,    `u`.first_name,    `company.category`.name  FROM users `u`  LEFT JOIN companies `company` ON `company`.id = `u`.company_id  LEFT JOIN categories `company.category` ON `company.category`.id = `company`.category_id    :ok  iex(2)>

Installation

To install SqlDust, please do the following:

Add sql_dust to your list of dependencies in mix.exs :
```
def deps do    [{:sql_dust, "~> 0.0.1"}]  end
```

Ensure sql_dust is started before your application:

def application do    [applications: [:sql_dust]]  end

Usage

Generating SQL queries has never been simpler. Just invoke the SqlDust.from/3 function. It accepts the following arguments:

resource (required) - Usually this is the table from which you want to query from
options (required) - A map containing info about what the query should contain (e.g. :select , :where , :group_by )
schema (optional) - A map containing info which overrule the defacto derived schema

options = %{    select: "id, name, COUNT(orders.id) AS order_count, GROUP_CONCAT(DISTINCT tags.name) AS tags, foo.tags",    group_by: "id",    where: ["name LIKE '%Paul%'", "order_count > 5", "foo.tags = 1"],    order_by: "COUNT(DISTINCT tags.id) DESC",    limit: 5  }    schema = %{    customers: %{      tags: %{        macro: :has_and_belongs_to_many      }    }  }    IO.puts SqlDust.from("customers", options, schema)    """  SELECT    `c`.id,    `c`.name,    COUNT(`orders`.id) AS order_count,    GROUP_CONCAT(DISTINCT `tags`.name) AS tags,    `foo`.tags  FROM customers `c`  LEFT JOIN orders `orders` ON `orders`.customer_id = `c`.id  LEFT JOIN customers_tags `tags_bridge_table` ON `tags_bridge_table`.customer_id = `c`.id  LEFT JOIN tags `tags` ON `tags`.id = `tags_bridge_table`.tag_id  LEFT JOIN foos `foo` ON `foo`.id = `c`.foo_id  WHERE (`c`.name LIKE '%Paul%') AND (`foo`.tags = 1)  GROUP BY `c`.id  HAVING (order_count > 5)  ORDER BY COUNT(DISTINCT `tags`.id) DESC  LIMIT 5  """

Enjoy using SqlDust! ^^

Testing

Run the following command for testing:

mix test

Every SqlDust feature is tested in test/sql_dust_test.exs .

Nice To Have

Query from the database
Use database connection and/or Ecto to derive defacto schema even better
Support querying with an Ecto model SqlDust.from(Sample.Weather)

TODO

Add additional documentation to the README
Add doc tests for internal functions

License

http://github.com/archan937 – http://推ter.com/archan937 – pm_engel@icloud.com