0

i'm new to laravel and wondering if this query can be done on Eloquent or Query Builder:

SELECT subjects.id, subjects.name, CASE WHEN grades.term = 1 THEN grades.grade END AS 'TERM_1', CASE WHEN grades.term = 2 THEN grades.grade END AS 'TERM_2', CASE WHEN grades.term = 3 THEN grades.grade END AS 'TERM_3', CASE WHEN grades.term = 4 THEN grades.grade END AS 'TERM_4' FROM users JOIN user_details ON users.id = user_details.user_id JOIN admissions ON user_details.admission_id = admissions.id JOIN grades ON users.id = grades.user_id RIGHT JOIN subjects ON grades.subject_id = subjects.id AND grades.user_id = 23 ORDER BY subjects.id ASC

**

my main problem here is the right join as it needs the AND condition to return all subjects, regardless of other subjects are null / no grades.

I used the DB::select( sql query ) but wondering if this can be done in Eloquent or query builder, based on my research online the DB::select is a security risk.

Pardon my english as it is not my mother tongue.

GriswoldNetbeans
  • 1
  • 1
    It can be done. Read the documentation. If you get stuck with your code, you can post a question here, and we will help you. – Gert B. Aug 02 '21 at 09:17
  • "RIGHT JOIN subjects ON grades.subject_id = subjects.id AND grades.user_id = 23" this is the part i am having trouble, i read the documentation, their are no AND condition and i am guessing it can be done using DB::raw, am i in the right track? – GriswoldNetbeans Aug 02 '21 at 09:49
  • does this help? https://stackoverflow.com/questions/16848987/a-join-with-additional-conditions-using-query-builder-or-eloquent . DB::raw is also an option, but I don't think its the best solution. – Gert B. Aug 02 '21 at 10:09
  • the grades.user_id = 23 should be a where clause, not in your ON. – Gert B. Aug 02 '21 at 11:19
  • ->rightJoin('subjects', function ($query) { $query->on('grades.subject_id', '=', 'subjects.id'); $query->on('grades.user_id', '=', DB::raw(auth()->id())); })->get(); – GriswoldNetbeans Aug 02 '21 at 11:32
  • i solved it based on the [link] (https://stackoverflow.com/questions/16848987/a-join-with-additional-conditions-using-query-builder-or-eloquent) thank you, as for the (where clause not in on) i needed it, so the query result will show all the subjects whether grades are null or not null, again thank you for helping me. – GriswoldNetbeans Aug 02 '21 at 11:40
  • you're welcome. The ON statement is uses to compare the keys in both tables. The check on user id has to be in the where statement. if done correctly that would not change the result – Gert B. Aug 02 '21 at 11:59

0 Answers0