0

I'm an absolute newbie at Laravel and I'm trying to port my website into this new framework. I have this query that is giving me a headache that I'd love to convert into Eloquent format so that I can use it on my controller.

// It makes sure that you don't get as result yourself nor the people you already follow. 
//I dont even know how much time I spent on this...
 $query = mysqli_query($conn , "  SELECT username, avatar_url FROM users 
                                  WHERE username LIKE '%".$username."%' AND username <>'".$user."'
                                  except SELECT u.username, u.avatar_url FROM users as U JOIN followers AS F 
                                  ON U.id = F.followed_user_id
                                  where following_user_id = ( select id from users where username='".$user."' );");

EDIT: I've made the first part like this: DB::table("users")->where("username", "LIKE", "%$search_people%")->where("username", '<>', $user->username)->get(); and it works, but I can't handle the except part. I have a variable called $user where I store the user id so I can simplify the last WHERE. I wrote something like this but it's totally off:

DB::table("users")->where("username", "LIKE", "%$search_people%")
                ->where("username", '<>', $user->username) /*next part is wrong*/
                ->except("$user->username", "$user->avatar_url")
                ->join("followers", "followers->followed_user_id", "=", "$user->id")
                ->where("following_user_id", "=", "$user->id");

EDIT2: I've tried to split this into two queries. Is there any way to intersect the two?

I need to have A minus B.

$result_a = DB::table("users")->where("username", "LIKE", "%$search_people%")
                ->where("username", '<>', $user->username);

$result_b = DB::table("users")->select("username", "avatar_url")
                    ->join("followers", "followers.followed_user_id", "=", "users.id" )
                    ->where("following_user_id", "=", "$user->id")->get();
Davide Santonocito
  • 3
  • 4
  • Watch SQL injection on `$username`. A simpler equivalent query would be `SELECT u.username, u.avatar_url FROM users as U LEFT JOIN followers AS F ON U.id = F.followed_user_id WHERE following_user_id IS NULL AND username LIKE ... AND username <> ....` which might be easier to eloquentify. – danblack Jun 12 '20 at 01:08
  • I notice that there's an EXCEPT clause in there. Are you sure this is a MySQL query? – maiorano84 Jun 12 '20 at 01:10
  • I ran this on a .php page through the mysqli_query function on my previous website – Davide Santonocito Jun 12 '20 at 01:12
  • @danblack you've a fair point, althought my doubts about how to get the operator LIKE remains – Davide Santonocito Jun 12 '20 at 01:14
  • It looks like MariaDb supported EXCEPT since 10.3. Never knew that. I don't think vanilla MySQL supports it, though. Anyway, I would first start with Laravel's [Query Builder](https://laravel.com/docs/7.x/queries) to build out each of your SELECT statements. Since there's no EXCEPT method in either Laravel's Query Builder or Eloquent, you may have to convert it to a NOT IN statement. See: https://stackoverflow.com/questions/25849015/laravel-eloquent-where-not-in – maiorano84 Jun 12 '20 at 01:20
  • try to use Models and relations to get the maximal of Eloquent, you may use conditions like `whereHas` or `whereExists` – Pedro Sanção Jun 12 '20 at 02:57
  • Terminology correction, in case it helps your search terms - using [Eloquent](https://laravel.com/docs/7.x/queries) means setting up models, and queries like `User::where('username', 'like', "%$search_people%")`. The queries you've shown so far are not using Eloquent, but Laravel's [query builder](https://laravel.com/docs/7.x/queries). – Don't Panic Jun 12 '20 at 03:49
  • Oh, my bad sorry as I said I'm very new. Thanks for the correction @Don'tPanic – Davide Santonocito Jun 13 '20 at 19:43
  • Is there a way to left join two collections? I've managed to make two queries and I need to subtract the elements of one to the other – Davide Santonocito Jun 13 '20 at 22:08

0 Answers0