projects.sql

-- name: CountListProjects :one
SELECT COUNT(*)::int AS count FROM projects
WHERE projects.account_id = @account_id
	AND (projects.name ILIKE @name OR @name = '')
	AND (projects.client_id = @client_id OR @client_id = 0)
	AND (ARRAY_LENGTH(@status::text[], 1) IS NULL OR projects.status = ANY(@status::project_status[]))
	AND (@label_id::int IN (
		SELECT label_id FROM labelables
		WHERE projects.id = labelables.labelable_id
			AND labelables.labelable_type = 'project'
	) OR @label_id = 0)
	AND (projects.is_archived = false OR @include_archived::bool)
	AND (projects.is_invoiced = false OR NOT @only_uninvoiced::bool)
	AND (projects.status IN ('created', 'in_progress') OR NOT @only_unfinished::bool)
;

-- name: ListProjects :many
SELECT sqlc.embed(projects), clients.name AS client_name FROM projects
INNER JOIN clients
	ON projects.client_id = clients.id
WHERE projects.account_id = @account_id
	AND (projects.name ILIKE @name OR @name = '')
	AND (projects.client_id = @client_id OR @client_id = 0)
	AND (ARRAY_LENGTH(@status::text[], 1) IS NULL OR projects.status = ANY(@status::project_status[]))
	AND (@label_id::int IN (
		SELECT label_id FROM labelables
		WHERE projects.id = labelables.labelable_id
			AND labelables.labelable_type = 'project'
	) OR @label_id = 0)
	AND (projects.is_archived = false OR @include_archived::bool)
	AND (projects.is_invoiced = false OR NOT @only_uninvoiced::bool)
	AND (projects.status IN ('created', 'in_progress') OR NOT @only_unfinished::bool)
ORDER BY
	CASE WHEN @sort_name::text = 'asc' THEN projects.name END ASC,
	CASE WHEN @sort_name::text = 'desc' THEN projects.name END DESC,
	CASE WHEN @sort_status::text = 'asc' THEN status END ASC,
	CASE WHEN @sort_status::text = 'desc' THEN status END DESC,
	CASE WHEN @sort_deadline_at::text = 'asc' THEN projects.deadline_at END ASC NULLS LAST,
	CASE WHEN @sort_deadline_at::text = 'desc' THEN projects.deadline_at END DESC NULLS LAST,
	CASE WHEN @sort_start_at::text = 'asc' THEN projects.start_at END ASC NULLS LAST,
	CASE WHEN @sort_start_at::text = 'desc' THEN projects.start_at END DESC NULLS LAST,
	CASE WHEN @sort_total_price::text = 'asc' THEN projects.total_price_amount END ASC,
	CASE WHEN @sort_total_price::text = 'desc' THEN projects.total_price_amount END DESC,
	CASE WHEN @sort_client_name::text = 'asc' THEN clients.name END ASC,
	CASE WHEN @sort_client_name::text = 'desc' THEN clients.name END DESC,
	COALESCE(projects.deadline_at, projects.start_at) DESC NULLS LAST
LIMIT $1
OFFSET $2;