MySQL order by value in EAV table?

Question

How can I sort by value here? I need to get documents ID in order of a value, if present.

Example of result query:

+------+------+------------+-------+---------------------+---------------------+
| id   | name | wfid       | docid | created             | updated             |
+------+------+------------+-------+---------------------+---------------------+
| 5269 | DOC2 | documentos | doc   | 2014-02-25 09:34:56 | 2014-02-25 09:39:51 |
| 5270 | DOC2 | documentos | doc   | 2014-02-25 09:34:57 | 2014-02-25 10:41:57 |
| 5271 | DOC2 | documentos | doc   | 2014-02-25 09:34:57 | 2014-02-25 10:42:20 |
+------+------+------------+-------+---------------------+---------------------+

I don't know how to achieve it. It works with a very ugly query but with no capability of ordering. Example, I would like to have all documents that are from Client1 and order it by Period. The user must have permission to see the document, permission are composed by wfid, docid, and userid.

Is it possible?

Fiddle: http://sqlfiddle.com/#!2/3b49e8

Schema:

mysql> describe documents;
+---------+-----------+------+-----+---------------------+-----------------------------+
| Field   | Type      | Null | Key | Default             | Extra                       |
+---------+-----------+------+-----+---------------------+-----------------------------+
| id      | int(11)   | NO   | PRI | NULL                | auto_increment              |
| name    | char(100) | YES  |     | NULL                |                             |
| wfid    | char(50)  | YES  |     | NULL                |                             |
| docid   | char(50)  | YES  |     | NULL                |                             |
| created | timestamp | NO   |     | 0000-00-00 00:00:00 |                             |
| updated | timestamp | NO   |     | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
+---------+-----------+------+-----+---------------------+-----------------------------+
6 rows in set (0.00 sec)

mysql> describe users;
+----------+----------+------+-----+---------+----------------+
| Field    | Type     | Null | Key | Default | Extra          |
+----------+----------+------+-----+---------+----------------+
| id       | int(11)  | NO   | PRI | NULL    | auto_increment |
| login    | char(30) | NO   |     | NULL    |                |
| password | char(50) | NO   |     | NULL    |                |
+----------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> describe permissions;
+--------+----------+------+-----+---------+----------------+
| Field  | Type     | Null | Key | Default | Extra          |
+--------+----------+------+-----+---------+----------------+
| id     | int(11)  | NO   | PRI | NULL    | auto_increment |
| wfId   | char(50) | NO   |     | NULL    |                |
| docId  | char(50) | NO   |     | NULL    |                |
| userId | int(11)  | NO   | MUL | NULL    |                |
+--------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> describe keywords;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| document_id | int(11)      | NO   | MUL | NULL    |                |
| keyword     | char(50)     | NO   |     | NULL    |                |
| value       | varchar(250) | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

Data:

mysql> select * from permissions;
+----+------------+-------+--------+
| id | wfId       | docId | userId |
+----+------------+-------+--------+
| 13 | documentos | doc   |      7 |
+----+------------+-------+--------+
1 row in set (0.00 sec)

mysql> select * from users;
+----+-------+----------+
| id | login | password |
+----+-------+----------+
|  7 | admin | a        |
+----+-------+----------+
1 row in set (0.00 sec)

mysql> select * from documents;
+------+------+------------+-------+---------------------+---------------------+
| id   | name | wfid       | docid | created             | updated             |
+------+------+------------+-------+---------------------+---------------------+
| 5263 | DOC1 | documentos | doc   | 2014-02-25 09:34:44 | 2014-02-25 09:38:09 |
| 5264 | DOC1 | documentos | doc   | 2014-02-25 09:34:50 | 2014-02-25 09:38:25 |
| 5265 | DOC1 | documentos | doc   | 2014-02-25 09:34:50 | 2014-02-25 09:38:37 |
| 5266 | DOC1 | documentos | doc   | 2014-02-25 09:34:50 | 2014-02-25 09:38:59 |
| 5267 | DOC1 | documentos | doc   | 2014-02-25 09:34:51 | 2014-02-25 09:39:10 |
| 5268 | DOC2 | documentos | doc   | 2014-02-25 09:34:56 | 2014-02-25 09:39:25 |
| 5269 | DOC2 | documentos | doc   | 2014-02-25 09:34:56 | 2014-02-25 09:39:51 |
| 5270 | DOC2 | documentos | doc   | 2014-02-25 09:34:57 | 2014-02-25 10:41:57 |
| 5271 | DOC2 | documentos | doc   | 2014-02-25 09:34:57 | 2014-02-25 10:42:20 |
| 5272 | DOC2 | documentos | doc   | 2014-02-25 09:34:58 | 2014-02-25 10:43:04 |
+------+------+------------+-------+---------------------+---------------------+
10 rows in set (0.00 sec)

mysql> select * from keywords;
+-------+-------------+-----------+----------+
| id    | document_id | keyword   | value    |
+-------+-------------+-----------+----------+
| 15888 |        5263 | Nombre    | Cliente1 |
| 15889 |        5264 | Nombre    | Cliente1 |
| 15890 |        5265 | Nombre    | Cliente1 |
| 15891 |        5266 | Nombre    | Cliente2 |
| 15892 |        5267 | Nombre    | Cliente3 |
| 15893 |        5268 | Nombre    | Cliente3 |
| 15894 |        5269 | Nombre    | Cliente3 |
| 15895 |        5270 | Nombre    | Cliente4 |
| 15896 |        5271 | Nombre    | Cliente4 |
| 15897 |        5272 | Nombre    | Cliente4 |
| 15898 |        5263 | Periodo   | 201301   |
| 15899 |        5263 | OtroValor | 1        |
| 15900 |        5264 | Periodo   | 201301   |
| 15901 |        5264 | OtroValor | 1        |
| 15902 |        5265 | Periodo   | 201301   |
| 15903 |        5265 | OtroValor | 1        |
| 15904 |        5266 | Periodo   | 201302   |
| 15905 |        5266 | OtroValor | 2        |
| 15906 |        5267 | Periodo   | 201302   |
| 15907 |        5267 | OtroValor | 2        |
| 15908 |        5268 | Periodo   | 201302   |
| 15909 |        5268 | OtroValor | 2        |
| 15910 |        5269 | Periodo   | 201303   |
| 15911 |        5269 | OtroValor | 3        |
| 15912 |        5270 | Periodo   | 201303   |
| 15913 |        5270 | OtroValor | 3        |
| 15914 |        5271 | Periodo   | 201304   |
| 15915 |        5271 | OtroValor | 3        |
| 15916 |        5272 | Periodo   | 201304   |
+-------+-------------+-----------+----------+
29 rows in set (0.00 sec)

Answer 1

I think I did the trick, so there we go:

First, you must INNER JOIN docs and permissions table on the conditions you want (same docid and same wfid), to get only rows from docs that have permissions. And then, you filter by user to get only the user you want. I put the condition at the end, but you may include in the ON condition if you wish it, to improve performance if you're joining the table later. After that, you put the order condition you want.

The SQL I ended up with is:

SELECT  d.id, d.name, d.wfid, d.docid, d.created, d.updated 
FROM documents d
INNER JOIN permissions p
ON (d.wfid = p.wfId
    AND d.docid = p.docId)
WHERE p.userid = 7
ORDER BY d.created DESC

http://sqlfiddle.com/#!2/3b49e8/14/0

It could be nice if you included an index for created, wfid and docid fields in docs to improve performance, as you use them as a key in the inner join and for ordering.

UPDATE

Check this: http://sqlfiddle.com/#!2/3b49e8/19/0

I've added the other tables to the query, making twice the join over keywords:

SELECT  d.id, d.name, d.wfid, d.docid, d.created, d.updated, k1.keyword
FROM documents d
INNER JOIN permissions p
ON (d.wfid = p.wfId
    AND d.docid = p.docId)
INNER JOIN keywords k1
ON (k1.document_id = d.id
   AND k1.keyword = 'Periodo' )

INNER JOIN keywords k2
ON (k2.document_id = d.id
   AND k2.keyword = 'Nombre' )

WHERE p.userid = 7
AND k2.value = 'Cliente1'

ORDER BY d.created DESC

As you can see, I added the keyword joined to show it's "Periodo"