使用神奇Elixir SQL dust生成復雜的SQL查詢

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)></pre> 
 
  
     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
"""</pre> 
 
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 
Copyright (c) 2016 Paul Engel, released under the MIT License
 http://github.com/archan937 – http://推ter.com/archan937 – pm_engel@icloud.com 
 
 </article>