Sample Header Ad - 728x90

ORDER from 2 tables to achieve a specific order returned by an API

0 votes
1 answer
102 views
There's an external API that returns image similarities, based on an "image ID" and a "image version" (a same image can have multiple versions). The similarity ORDER is given by the API. The API returns something like this:
+---------+---------+
|   id    | version |
+---------+---------+
|  37967  |    2    |
|    236  |    1    |
|  37967  |    1    |
|   1413  |    2    |
+---------+---------+
Then I need to retrieve entries in a MySQL database (containing 2 tables), and keep the same ORDER as the one returned by the API. That's where I'm having a problem. Here are the 2 tables:
"img" MySQL table:
+---------+-----------------------+
|   id    | lots of other columns |
+---------+-----------------------+
|    236  |         data          |
|   1413  |         data          |
|  37967  |         data          |
+---------+-----------------------+
"vers" MySQL table:
+---------+---------+
|   id    | version |
+---------+---------+
|    236  |    1    |
|   1413  |    1    |
|   1413  |    2    |
|  37967  |    1    |
|  37967  |    2    |
|  37967  |    3    |
+---------+---------+
the closest result I can get is by using ORDER BY FIELD, but it's still not the same ORDER as the one returned by the API.
My query:

SELECT i.id, v.version
FROM img i
LEFT JOIN vers v ON i.id=v.id 
WHERE ((i.id=37967 AND v.version=2) 
OR (i.id=236 AND v.version=1) 
OR (i.id=37967 AND v.version=1) 
OR (i.id=1413 AND v.version=2)) 
ORDER BY FIELD(i.id, 37967,236,37967,1413), FIELD(v.version,2,1,1,2)
Results:
+---------+---------+
|   id    | version |
+---------+---------+
|  37967  |    2    |
|  37967  |    1    |
|    236  |    1    |
|   1413  |    2    |
+---------+---------+
As you can see, the order is not exactly the one returned by the API :( Any help would be appreciated, thank you all in advance.
Asked by Daaaaa (3 rep)
Dec 10, 2019, 12:44 PM
Last activity: Dec 11, 2019, 09:37 AM