SQL vs ORM vs QLCs

queries.md

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].