Geek asks and answers

MySQL order by value in EAV table?

How can I sort by value here? I need to get the id of the documents in order of magnitude, if any.

Example query result:

+------+------+------------+-------+---------------------+---------------------+
| 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 do not know how to achieve it. It works with a very ugly query, but without the possibility of ordering. For example, I would like to have all the documents that are in Client1and order it Period. The user must have permission to view the document, permission is drawn up wfid, docidand userid.

Is it possible?

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

Scheme:

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)
+3
source share
1 answer

I think I did the trick, so we go:

-, INNER JOIN docs permissions , ( docid wfid), docs, . , , , . , ON, , , . , .

SQL :

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

, created, wfid docid docs , .

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

, :

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

, join, "Periodo"

+1

More articles:


All Articles