How do I make this complex query in laravel

i have these data

Course id 1 , program id 2, code#112, start date 2023-1-11, end date 2023-2-11
Course id 2, program id 2, code#112, start date 2023-2-12, end date 2023-3-13

The result should be

course id 1, program id 2, code #112 ,start_date 2023-1-11, end_date 2023-3-13, program:{..}, course:{...}

the query should get 1 row of each same code data for example #112 it should get minimum start date, maximum end date, and "course id, program id from the minimum start date row" , and then i want to use eloquent relationship for program and course.

thanks

Tray2

2 years ago

Level 73

Something like

SELECT
	program_id,
	code,
	min(start_date),
	max(end_date)
FROM
	slask
GROUP BY
	program_id,
	code;

demonz

2 years ago

Level 2

@Tray2 thanks tray for replying i want to add also another columns which is status, and course_id which will be taken from the same model but from the row that has min(start_date)

Tray2

2 years ago

Best Answer

Level 73

@demonz as long as the course id is the same for the records it's not a problem to do that, but since your example data has two different course ids you need to do some subquery trickery to get the correct course id.

SELECT
	(SELECT MIN(course_id) FROM slask si where si.program_id = s.program_id) course_id,
	s.program_id,
	s.code,
	s.status,
	min(s.start_date),
	max(s.end_date)
FROM
	slask s
GROUP BY
	1,
	s.program_id,
	s.code,
	s.status;

1 like

demonz

2 years ago

Level 2

@Tray2 yeah thanks, that's exactly what I did , it's simple but i was having issue with pagination request that filters my data everytime i search so i had to use subqueries to get the minimum from the missing rows.

Please or to participate in this conversation.