view release on metacpan or  search on metacpan

lib/Aion/Query.pm  view on Meta::CPAN

	my @books = query_attach $authors => "books:id:author_id" => "SELECT author_id, title FROM book ORDER BY title";
	
	my $attaches = [
	    {name => "Pushkin A.S.", id => 1, books => [
	        {title => "Kiss in night", author_id => 1},
	        {title => "Mir",           author_id => 1},
	    ]},
	    {name => "Pushkin A.",   id => 2, books => []},
	    {name => "Alice",        id => 3, books => [
	        {title => "Mips as cpu", author_id => 3},
	    ]},
	];
	
	$authors # --> $attaches
	
	my $books = [
	    {title => "Kiss in night", author_id => 1},
	    {title => "Mips as cpu",   author_id => 3},
	    {title => "Mir",           author_id => 1},
	];
	
	\@books  # --> $books

=head2 query_col ($query, %params)

Returns one column.

	query_col "SELECT name FROM author ORDER BY name" # --> ["Alice", "Pushkin A.", "Pushkin A.S."]
	
	eval {query_col "SELECT id, name FROM author"}; $@  # ~> Only one column is acceptable!

=head2 query_row ($query, %params)

Returns one row.

	query_row "SELECT name FROM author WHERE id=2" # --> {name => "Pushkin A."}
	
	my ($id, $name) = query_row "SELECT id, name FROM author WHERE id=2";
	$id    # -> 2
	$name  # => Pushkin A.
	
	eval { query_row "SELECT id, name FROM author" }; $@ # ~> A few lines! 

=head2 query_row_ref ($query, %params)

Like C<query_row>, but always returns a scalar.

	my @x = query_row_ref "SELECT name FROM author WHERE id=2";
	\@x # --> [{name => "Pushkin A."}]
	
	eval {query_row_ref "SELECT name FROM author"}; $@  # ~> A few lines!

=head2 query_scalar ($query, %params)

Returns the first value. The query must return one row, otherwise it throws an exception.

	query_scalar "SELECT name FROM author WHERE id=2" # => Pushkin A.

=head2 make_query_for_order ($order, $next)

Creates a page request condition not by offset, but by B<cursor pagination>.

To do this, it receives C<$order> of the SQL query and C<$next> - a link to the next page.

	my ($select, $where, $order_sel) = make_query_for_order "name DESC, id ASC", undef;
	
	$select     # => name || ',' || id
	$where      # -> 1
	$order_sel  # -> undef
	
	my @rows = query "SELECT $select as next FROM author WHERE $where LIMIT 2";
	
	my $last = pop @rows;
	
	($select, $where, $order_sel) = make_query_for_order "name DESC, id ASC", $last->{next};
	$select     # => name || ',' || id
	$where      # => (name < 'Pushkin A.'\nOR name = 'Pushkin A.' AND id >= '2')
	$order_sel  # --> [qw/name id/]

See also:

=over

=item 1. Article [Paging pages on social networks
sch(https://habr.com/ru/articles/674714/).

=item 2. LLL<https://metacpan.org/dist/SQL-SimpleOps/view/lib/SQL/SimpleOps.pod#SelectCursor>

=back

=head2 settings ($id, $value)

Sets or returns a key from the C<settings> table.

	query "CREATE TABLE settings(
	    id TEXT PRIMARY KEY,
		value TEXT NOT NULL
	)";
	
	settings "x1"       # -> undef
	settings "x1", 10   # -> 1
	settings "x1"       # -> 10

=head2 load_by_id ($tab, $pk, $fields, @options)

Returns a record by its ID.

	load_by_id author => 2  # --> {id=>2, name=>"Pushkin A."}
	load_by_id author => 2, "name as n"  # --> {n=>"Pushkin A."}
	load_by_id author => 2, "id+:x as n", x => 10  # --> {n=>12}

=head2 insert ($tab, %x)

Adds an entry and returns its ID.

	insert 'author', name => 'Masha'  # -> 4

=head2 update ($tab, $id, %params)

Updates a record by its ID and returns that ID.