Skip to content

Instantly share code, notes, and snippets.

@d11wtq d11wtq/queries.md
Last active Dec 16, 2015

Embed
What would you like to do?
SQL vs ORM vs QLCs

Comparing the power and expressiveness of various database query systems

Simple select all.

SELECT * FROM users;
User.all
Users = mnesia:table(user).

Project a few fields.

SELECT username FROM users;
User.all(fields: [:username])
[Name || #user{username=Name} <- Users].

Convert a field

SELECT age * 2 FROM users;
# concept does not work in an ORM
[Age * 2 || #user{age=Age} <- Users].

Restrict to a subset

SELECT * FROM users WHERE age > 18;
User.all('age.gt' => 18)
[U || U = #user{age=Age} <- Users, Age > 18].

Restrict with an OR condition

SELECT * FROM users WHERE moderator = true OR admin = true;
User.all(moderator: true) | User.all(admin: true)
[U || U = #user{moderator=M, admin=A} <- Users, M orelse A].

Join two tables

    SELECT posts.*
      FROM posts
INNER JOIN users
        ON posts.user_id = users.id
     WHERE user.moderator = true
       AND posts.published = false;
Post.all(links: [:user], 'user.moderator' => true, published: false)
[P || P = #post{published=Pub, user_id=UID} <- Posts,
      #user{id=UID moderator=Mod} <- Users,
      Mod == true andalso Pub == false].
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.