Skip to content

Instantly share code, notes, and snippets.

@d11wtq d11wtq/
Last active Dec 16, 2015

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;
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('' => 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
        ON posts.user_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.