Get latest pod version with spec

pods.sql

-- Instructions: Run Q1 and Q2 independently, then run the join at the bottom to understand all parts in this query.


-- Q1  Gives us the latest version for each pod with its version_id and the corresponding pod_id
-- Reference: https://stackoverflow.com/a/19602031/1904232
select 
latest_version,
pod_versions.id as version_id,
latest_pods.pod_id

from pod_versions
join

(

	select max(pod_versions.name) as latest_version , 
	        pod_id 
	        
	from pod_versions
	group by pod_id

) as latest_pods

on
pod_versions.pod_id = latest_pods.pod_id
and
pod_versions.name = latest_pods.latest_version


limit 50




--- Q2 - Gives us version and JSON from all pods
select pod_versions.name as version, 
	   commits.specification_data,
	   pod_versions.pod_id,
	   pod_versions.id as version_id
from commits
join pod_versions
on commits.pod_version_id = pod_versions.id


limit 50

--------------------------------------------
---- NOW BOTH COMBINED IN A SUPER QUERY -----
--------------------------------------------

--Reference for left join lateral: https://medium.com/kkempin/postgresqls-lateral-join-bfd6bd0199df


select

latest_version_per_pod.pod_id,
all_pods.specification_data

from

				-- Q1
				(
				
					select 
					latest_version,
					pod_versions.id as version_id,
					latest_pods.pod_id
					
					from pod_versions
					join
					
					(
					
						select max(pod_versions.name) as latest_version , 
						        pod_id 
						        
						from pod_versions
						group by pod_id
					
					) as latest_pods
					
					on
					pod_versions.pod_id = latest_pods.pod_id
					and
					pod_versions.name = latest_pods.latest_version
				
				
				) as latest_version_per_pod

left join lateral 

			--Q2
			(
			
						select pod_versions.name as version, 
							   commits.specification_data,
							   pod_versions.pod_id,
							   pod_versions.id as version_id
						from commits
						join pod_versions
						on commits.pod_version_id = pod_versions.id
						where 
						pod_versions.pod_id = latest_version_per_pod.pod_id
						and 
						version_id = latest_version_per_pod.version_id
						limit 1
			
			)  as all_pods


on true 



limit 50