I’m doing a project and is very limited in time; now there’s just no time to sit and study the SQL translation in Eloquent, especially since the SQL query is not trivial:

SELECT user_id FROM user_movies GROUP BY user_id ORDER BY COUNT( movie_id IN (SELECT movie_id FROM user_movies WHERE user_id = 305344) ) DESC LIMIT 50

But the matter is not even in the request itself, the question is simpler:
How to use SQL queries as they are, but in the Laravel controller?

If interested, the query does the following: searches all records in the general table (each record has a user id and a movie id repeatedly found in the table), groups them by user id, then sorts the number of identical films in descending order compared to the current user, id which - 305344. To translate this into Eloquent, my week will be gone.

  • ORDER BY COUNT(логическое выражение) is on the verge of genius ... was it not easier to take 2 copies of the table at once, related by movie_id equality and NOT user_id equality? And also - formally, the query idea implicitly assumes that the user, whose id = 305344, will be the first to be issued, but if there is a user who has ALL movies that a given user has, then it is unknown who will be the first in the final set .. - Akina
  • Thanks for the comment and analysis, I certainly will consider this for the future. But now I need to solve a specific problem - running raw SQL. By the way, the current user comes first, even if another has the same set of movies, but not the fact that in some other circumstances this will not change. - Vitaly Komarov
  • I hope to find the answer here: laravel.ru/docs/v5/database - Vitaly Komarov
  • one
    I hope to find the answer here. Yes, there he is. And in open form ... not the fact that in some other circumstances it does not change. It has the full right. Put in the sort expression ORDER BY user_id = 305344 DESC, остальные выражения (for MySQL, or similarly through CASE for other dialects) - then you can guarantee the required order. Here you only have to pass the parameter twice - to the condition of selection and to the condition of sorting, but this is hardly a problem. - Akina
  • one
    AND user_id != 305344 not it easier to stupidly in WHERE add AND user_id != 305344 ? before sorting it will not enter the set ... - Akina

1 answer 1

Perhaps the method of raw expressions will help DB :: raw () Link