How to implement "used with"?

Question

MySQL

Hello. Help solve the issue. Let's say there is a post with elements associated with this post. We can get all posts by element and in turn elements by post. (Many-to-many relationship.).
Actually the question is how to make a selection and sorting .. Suppose we go to element1 and display all the elements with which it is most often used in posts. While I have no ideas at all, throw it in ... Thank you in advance.

//структура таблиц
посты
id
...
элементы
id
...
пост_элемент
id
пост_id
элемент_id

Answer 1

Something like this, there may be errors, we need real data for the test

SELECT
  пэ.элемент_ид,
  COUNT(пэ.пост_ид) AS "количество"
FROM пост_элемент
  INNER JOIN пост_элемент пэ ON пе.пост_ид = пост_элемент.пост_ид
WHERE пост_элемент.элемент_ид = <какой_то_ид> AND пэ.элемент_ид != <этот_какой_то_ид>
GROUP BY пэ.элемент_ид
ORDER BY количество