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 |
+-----------+-------------+-----------+-------------+---------+
A query would be fine but if you know how to use medoo (http://medoo.in/api/select), I would really appreciate its conversion.
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.