Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
SELECT
c.container_id AS id
FROM container c
JOIN container_tree ct ON ct.child_container_id = c.container_id
WHERE ct.parent_container_id = 'dec521e1-432e-4304-8ba8-6cf59590d99b'
ORDER BY
CASE COALESCE( 'index_asc', _compute_container_sort_key('dec521e1-432e-4304-8ba8-6cf59590d99b') )
WHEN 'index_asc' THEN c.sort_number ASC NULLS LAST, collkey(c.title, 'nb_NO') ASC
WHEN 'index_desc' THEN c.sort_number DESC NULLS FIRST, collkey(c.title, 'nb_NO') DESC
WHEN 'size_asc' THEN c.sort_number ASC NULLS LAST, collkey(c.title, 'nb_NO') ASC -- same as index_asc
WHEN 'size_desc' THEN c.sort_number DESC NULLS FIRST, collkey(c.title, 'nb_NO') DESC -- same as index_desc
WHEN 'title_asc' THEN collkey(c.title, 'nb_NO') ASC
WHEN 'title_desc' THEN collkey(c.title, 'nb_NO') DESC
WHEN 'updated_asc' THEN c.updated ASC NULLS FIRST, collkey(c.title, 'nb_NO') ASC
WHEN 'updated_desc' THEN c.updated DESC NULLS LAST, collkey(c.title, 'nb_NO') ASC
WHEN 'created_asc' THEN c.created ASC NULLS FIRST, collkey(c.title, 'nb_NO') ASC
WHEN 'created_desc' THEN c.created DESC NULLS LAST, collkey(c.title, 'nb_NO') ASC
END
CREATE OR REPLACE FUNCTION public.list_container_by_parent (
parent_id uuid,
locale text,
sort_key text,
skip_custom_init text
)
RETURNS SETOF public.node AS
$body$
my ($id, $locale, $sort_key, $skip_custom_init) = @_;
# If sort_key specified, just use it
unless ($sort_key) {
# If parent specified, get sort_key for it, else get default
my $rv=spi_exec_query(
"SELECT container_sort_key("
. ( $id ? "'$id'" : "null" )
. ") AS sort_key"
,1);
$sort_key=$rv->{'rows'}[0]->{'sort_key'};
}
# index_asc (default)
my $order_by=<<"EOM";
ORDER BY c.sort_number ASC NULLS LAST, collkey(c.title, '$locale') ASC
EOM
# index_desc
$order_by=<<"EOM" if $sort_key eq 'index_desc';
ORDER BY c.sort_number DESC NULLS FIRST, collkey(c.title, '$locale') DESC
EOM
# title_asc
$order_by=<<"EOM" if $sort_key eq 'title_asc';
ORDER BY collkey(c.title, '$locale') ASC
EOM
# title_desc
$order_by=<<"EOM" if $sort_key eq 'title_desc';
ORDER BY collkey(c.title, '$locale') DESC
EOM
# updated_asc
$order_by=<<"EOM" if $sort_key eq 'updated_asc';
ORDER BY c.updated ASC NULLS FIRST, collkey(c.title, '$locale') ASC
EOM
# updated_desc
$order_by=<<"EOM" if $sort_key eq 'updated_desc';
ORDER BY c.updated DESC NULLS LAST, collkey(c.title, '$locale') ASC
EOM
# created_asc
$order_by=<<"EOM" if $sort_key eq 'created_asc';
ORDER BY c.created ASC NULLS FIRST, collkey(c.title, '$locale') ASC
EOM
# created_desc
$order_by=<<"EOM" if $sort_key eq 'created_desc';
ORDER BY c.created DESC NULLS LAST, collkey(c.title, '$locale') ASC
EOM
# Handle skipping by custom_init field
my $skip_column;
if ( $skip_custom_init ) {
my $skip_field = quote_literal($skip_custom_init);
$skip_column = "extract_custom_init_value(c.custom_init, $skip_field)";
}
else {
$skip_column = "NULL";
}
# Fetch and return child containers
my $sth;
if ( $id ) {
my $id_literal = quote_literal($id);
$sth = spi_query(<<"EOM");
SELECT c.container_id AS id, $skip_column AS skip
FROM container c JOIN container_tree ct ON ct.child_container_id = c.container_id
WHERE ct.parent_container_id = $id_literal
$order_by
EOM
}
else {
$sth = spi_query(<<"EOM");
SELECT c.container_id AS id, $skip_column AS skip
FROM container c LEFT JOIN container_tree ct ON ct.child_container_id = c.container_id
WHERE ct.parent_container_id IS NULL
$order_by
EOM
}
my $counter = 0;
while ( defined( $row = spi_fetchrow($sth) ) ) {
next if $row->{'skip'};
return_next({
node_id => $row->{'id'},
node_type => 'container',
node_number => $counter,
node_level => undef,
});
$counter++;
}
return;
$body$
LANGUAGE 'plperl'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100 ROWS 1000;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.