MySQL and DB facade query difference

I have a laravel db facade statement

DB::table("internal_marks AS InternalMark")->select('Student.registerno', 'Student.name', 'Subject.subject_code', 'Subject.subject_name')
            ->whereNotIn('InternalMark.id', function ($q) {
                $q->select('internalmarks_id')->from('internal_mark_entries')->whereNotIn('internal_mark_entries.entry_id', function ($q1) {
                    $q1->select('id')->from('internal_mark_entry_nos');
                });
            })->where('exammonth_id', '=', session('ExamID'))->where('InternalMark.subject_id', '=', ' Subject.id')->where('InternalMark.student_id', '=', 'Student.id')
            ->get();

This generates the query like this

SELECT
	`Student`.`registerno`,
	`Student`.`name`,
	`Subject`.`subject_code`,
	`Subject`.`subject_name`
FROM
	`internal_marks` AS `InternalMark`
WHERE
	`InternalMark`.`id` NOT IN (
		SELECT
			`internalmarks_id`
		FROM
			`internal_mark_entries`
		WHERE
			`internal_mark_entries`.`entry_id` NOT IN (
				SELECT
					`id`
				FROM
					`internal_mark_entry_nos`
			)
	)
	AND `exammonth_id` = 3
	AND `InternalMark`.`subject_id` = Subject.id
	AND `InternalMark`.`student_id` = Student.id

but i want query like this

SELECT
	Student.registerno,
	Student.name,
	Subject.subject_code,
	Subject.subject_name
FROM
	internal_marks AS InternalMark,
	subjects AS Subject,
	students AS Student
WHERE
	InternalMark.id NOT IN (
		SELECT
			internalmarks_id
		FROM
			internal_mark_entries
		WHERE
			internal_mark_entries.entry_id NOT IN (
				SELECT
					id
				FROM
					internal_mark_entry_nos
			)
	)
	AND InternalMark.subject_id = Subject.id
	AND InternalMark.student_id = Student.id

What modification i need to do to obtain the above query.

LaryAI

3 years ago

Best Answer

Level 58

You can use the join method to join the subjects and students tables to the internal_marks table. You can also use the whereRaw method to format the query as desired.

DB::table("internal_marks AS InternalMark")
    ->select('Student.registerno', 'Student.name', 'Subject.subject_code', 'Subject.subject_name')
    ->join('subjects AS Subject', 'InternalMark.subject_id', '=', 'Subject.id')
    ->join('students AS Student', 'InternalMark.student_id', '=', 'Student.id')
    ->whereNotIn('InternalMark.id', function ($q) {
        $q->select('internalmarks_id')
            ->from('internal_mark_entries')
            ->whereNotIn('internal_mark_entries.entry_id', function ($q1) {
                $q1->select('id')
                    ->from('internal_mark_entry_nos');
            });
    })
    ->where('exammonth_id', '=', session('ExamID'))
    ->whereRaw('InternalMark.subject_id = Subject.id AND InternalMark.student_id = Student.id')
    ->get();

Please or to participate in this conversation.