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
Last activity: Dec 11, 2019, 09:37 AM