Eloquent - Join a Subquery with select distinct

so i had a query with a left join sub query with select distinct. but im confused how to convert it into eloquent. any helper ?

my query

SELECT a.poid, b.aks FROM trpohd as a LEFT JOIN ( SELECT b.poid, STRING_AGG( b.epurno, '#' ) as aks FROM (SELECT DISTINCT * FROM (SELECT hd.poid, so.epurno FROM trpohd hd JOIN trpodt dt ON hd.poid = dt.poid JOIN trsalesorder so ON dt.sohdid = so.sono ORDER BY so.sono ) as a ) as b GROUP BY poid) AS b ON b.poid = a.poid

Tray2

3 years ago

Level 73

Always wrap any and all code blocks in three back ticks ` to make it readable

SELECT a.poid, b.aks 
FROM trpohd as a 
LEFT JOIN ( SELECT b.poid, STRING_AGG( b.epurno, '#' ) as aks 
				FROM (SELECT DISTINCT * 
						FROM (SELECT hd.poid, so.epurno 
								FROM trpohd hd 
								JOIN trpodt dt ON hd.poid = dt.poid 
								JOIN trsalesorder so ON dt.sohdid = so.sono 
								ORDER BY so.sono ) as a ) as b 
								GROUP BY poid) AS b ON b.poid = a.poid

Not even sure that is valid SQL, it looks a bit of to me.

However to make this work I would use either the query builder, eloquent + the query builder or since its a complex query, push into a database view.

Please or to participate in this conversation.