json - sorting based in same multpile values

If I understand, you want to find all users which have the same language and hobbies as some other user. Stripping out all the extraneous joins to get more info about these users, we'd have...

select *
from tbl_user usr
where user_id != ?
  and user_language = (select user_language from tbl_user where user_id = ?)
  and user_hobbies = (select user_hobbies from tbl_user where user_id = ?)

But user_hobbies is a problem. It looks like a user can have many hobbies, and a hobby can have many users. That's a many-to-many relationship normally modelled as a join table. But you appear to have it as a single field with the values joined together which is going to make queries complicated. Not only because of the extra formatting and concatenation, but it will make finding intersections difficult.

For example...

user1.user_hobbies = "2,3,5"
user2.user_hobbies = "1,2,3,4,5"

user1 has a subset of user2's hobbies. But no simple string equality can figure that out. 2,3,5 is not a substring of 1,2,3,4,5.

Here's the opposite problem, a false positive.

user1.user_hobbies = "2,3"
user2.user_hobbies = "12,34"

2,3 is a substring of 12,34 so may easily get a false match.

And you have to be very careful user_hobbies is always ordered the same.

user1.user_hobbies = "1,2,3"
user2.user_hobbies = "3,2,1"

Even if user_hobbies is a JSON array and you use JSON operators, it doesn't change the fundamental problems.

The proper way to handle this is with a many-to-many table relationship.

users:
    id
    name

hobbies:
    id
    name

user_hobbies:
    user_id
    hobby_id

You'd get all a user's hobbies like so:

select u.name, h.name
from users u
join user_hobbies uh on u.id = uh.user_id
join hobbies h on h.id = uh.hobby_id
where u.id = ?

Then to find all other users who share any of a user's hobbies...

select u.id
from users u
join user_hobbies uh on u.id = uh.user_id
where uh.user_id != ?
  and uh.hobby_id in (select hobby_id from user_hobbies where user_id = ?)
group by user_id

Finding users who share all of a user's hobbies is a little more complicated. You do a group by of all user/hobby combinations that match, then check for the ones which had the same number of hobbies as the user. This is a bit easier using a with clause to avoid repeating the same subselect.

with my_hobbies as (
    select hobby_id from user_hobbies where user_id = ?
)
select uh.user_id
from my_hobbies mh
join user_hobbies uh on uh.hobby_id = mh.hobby_id
where uh.user_id != ?
group by uh.user_id
having count(*) = (select count(*) from my_hobbies);

This might seem complicated, and it is. And using a JSON array might seem simpler, and it is. But ultimately the effort to learn how to work with join tables is worth it. They will produce the right results and less headaches.

Change this:

AND usr.`user_language_id` IN (
    SELECT `user_language_id` 
    FROM tbl_user) 

To:

AND usr.`user_language_id` IN (
    SELECT `user_language_id` 
    FROM tbl_user
    WHERE user_id = ?) 

And If I understand what you want, it should do it.