In this article we discuss the docs_id option which provides an easier manipulation of CALL PQ result set.
Let’s consider the following PQ batch call:
mysql> CALL PQ ('pq', ('{"title":"butter is good as", "id":3}',
'{"title":"was butter","id":4}',
'{"title":"sas was butter","id":5}',
'{"title":"bas was butter", "id":6}',
'{"title":"butter is good as","id":7}'),
1 as docs_json ,1 as docs,1 as query);
+------+-----------+------------+------+---------+
| UID | Documents | Query | Tags | Filters |
+------+-----------+------------+------+---------+
| 1 | 1,5 | butter is | | |
| 2 | 2,3,4 | butter was | | |
+------+-----------+------------+------+---------+
2 rows in set (0.00 sec)
What is wrong with this output?
We receive list of pairs between queries and positions of documents in the input array, however our documents have an identifier - the id attribute.
If we want to pass the result to a next process, we would need to do a post-processing work to extract the document ids from the input array using the positions.
To avoid this post-processing in 2.7.0 we added a new option called docs_id which allows replacing the document positions with the value of a document attribute.
mysql> CALL PQ ('pq', ('{"title":"butter is good as", "id":3}',
'{"title":"was butter","id":4}',
'{"title":"sas was butter","id":5}',
'{"title":"bas was butter", "id":6}',
'{"title":"butter is good as","id":7}'),
1 as docs_json ,1 as docs,1 as query, 'id' as docs_id);
+------+-----------+------------+------+---------+
| UID | Documents | Query | Tags | Filters |
+------+-----------+------------+------+---------+
| 1 | 3,7 | butter is | | |
| 2 | 4,5,6 | butter was | | |
+------+-----------+------------+------+---------+
2 rows in set (0.01 sec)
Now we have the document ids (or other identified at choice) in the PQ result set.
The chosen docs_id attribute must be an integer attribute. It’s values don’t necessary need to be unique. For example the queries could have a logical grouping by another integer attribute. If we want to find out just the groups that match criteria we can use that attribute instead of the document id:
mysql> CALL PQ ('pq', ('{"title":"butter is good as", "id":3,"gid":10}',
'{"title":"was butter","id":4,"gid":20}',
'{"title":"sas was butter","id":5,"gid":10}',
'{"title":"bas was butter", "id":6,"gid":20}',
'{"title":"butter is good as","id":7,"gid":10}'),
1 as docs_json ,1 as docs,1 as query, 'gid' as docs_id);
+------+-----------+------------+------+---------+
| UID | Documents | Query | Tags | Filters |
+------+-----------+------------+------+---------+
| 1 | 10 | butter is | | |
| 2 | 10,20 | butter was | | |
+------+-----------+------------+------+---------+
2 rows in set (0.00 sec)