D-Net

SELECT * FROM (

	SELECT

		(array_agg(DISTINCT o.country))[1]      AS country,

		(array_agg(DISTINCT o.legalname))[1]    AS organization,

		d.id                                    AS repoid,

		d.officialname                          AS reponame,

		d.englishname                           AS othername,

		d.namespaceprefix                       AS repoprefix,

		d.websiteurl                            AS repourl,

		a.id                                    AS id,

		a.active                                AS active,

		a.protocol                              AS protocol,

		a.typology                              AS type,

		a.compatibility                         AS compliance,

		CASE

			WHEN (a.last_aggregation_date IS NOT NULL) THEN to_char(a.last_aggregation_date, 'YYYY-MM-DD')

			WHEN (a.last_download_date IS NOT NULL) THEN to_char(a.last_download_date, 'YYYY-MM-DD')

			ELSE ''

		END AS aggrdate,

		CASE

			WHEN (a.last_aggregation_total IS NOT NULL) THEN to_char(a.last_aggregation_total, '')

			WHEN (a.last_download_total IS NOT NULL) THEN to_char(a.last_download_total, '')

			ELSE '0'

		END AS aggrtotal

	FROM

		api a

		LEFT OUTER JOIN datasources d               ON (a.datasource = d.id)

		LEFT OUTER JOIN datasource_organization dao ON (d.id = dao.datasource)

		LEFT OUTER JOIN organizations o             ON (dao.organization = o.id)

		GROUP BY

			d.id, d.officialname, d.englishname, d.namespaceprefix, d.websiteurl,

			a.id, a.active, a.protocol, a.typology, a.compatibility,

			a.last_aggregation_date, a.last_download_date,

			a.last_aggregation_total, a.last_download_total

) AS t WHERE

	$if(field)$

		$field$ = $delimeter$$value$$delimeter$

	$else$

		repoid ILIKE '%$value$%' 

		OR reponame ILIKE '%$value$%'

		OR othername ILIKE '%$value$%'

		OR repoprefix ILIKE '%$value$%' 

		OR repourl ILIKE '%$value$%' 

		OR organization ILIKE '%$value$%' 

	$endif$