How to get the relations of an item in mysql?

Question

So, here is my database:

Item
+---------+-------+
| item_id | name  |
+---------+-------+
|       1 | item1 |
|       2 | item2 |
|       3 | item3 |
|       4 | item4 |
+---------+-------+
Type
+---------+-------+
| type_id | name  |
+---------+-------+
|       1 | type1 |
|       2 | type2 |
|       3 | type3 |
+---------+-------+
Relation
+-----------+-----------+---------+
| a_item_id | b_item_id | type_id |
+-----------+-----------+---------+
|         1 |         2 |       1 |
|         2 |         3 |       1 |
|         1 |         2 |       2 |
|         4 |         1 |       3 |
+-----------+-----------+---------+

I would like to get the relations for a specific item. For example SELECT ... WHERE item_id = 1 should output:

+-----------+-------------+-----------+-------------+---------+
| a_item_id | a_item_name | b_item_id | b_item_name | type_id |
+-----------+-------------+-----------+-------------+---------+
|         1 | item1       |         2 | item2       |       1 |
|         1 | item1       |         2 | item2       |       2 |
|         4 | item4       |         1 | item1       |       3 |
+-----------+-------------+-----------+-------------+---------+

• The items' position (a/b) is important.
• For the same type_id, there is only one relation between two items. (reversed position is not possible)

A query would be fine but if you know how to use medoo (http://medoo.in/api/select), I would really appreciate its conversion.

Answer 1

The solution was easier than I expected, The query requires two joins for the foreign keys in the relation table. The table aliases are mandatory to prevent ambiguous column names.

SELECT a_item_id, a_item.name AS a_item_name, b_item_id, b_item.name AS b_item_name, type_id
FROM relation
INNER JOIN item a_item ON a_item.item_id = a_item_id
INNER JOIN item b_item ON b_item.item_id = b_item_id
WHERE a_item_id = 1 OR b_item_id = 1

I did not find a way to create table aliases with medoo so I had to use the query method.