Aion-Query
7 results

 view release on metacpan or  search on metacpan

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

# Для слайса
#
#	query_slice word => "id", "SELECT word, id FROM word WHERE word in (1,2,3)" 	-> 	{ 1 => 10, 2 => 20 }
#
# 	query_slice word => {}, "SELECT word, id FROM word WHERE word in (1,2,3)" 		-> 	{ 1 => {id => 10, word => 1} }
#
#	query_slice word => ["id"], "SELECT word, id FROM word WHERE word in (1,2,3)" 	-> 	{ 1 => [10, 20], 2 => [30] }
#
# 	query_slice word => [], "SELECT word, id FROM word WHERE word in (1,2,3)" 		-> 	{ 1 => [{id => 10, word => 1}, {id => 20, word => 2}] }
#
# 	query_slice word => [[]], "SELECT word, id FROM word WHERE word in (1,2,3)" 		-> [ [{id => 10, word => 1}, {id => 20, word => 2}], ... ]
#
# 	TODO: query_slice [] => word, "SELECT word, id FROM word WHERE word in (1,2,3)" 		-> 	[{id => 10, word => 1}, {id => 20, word => 2}]
#
#   TODO: [ "id", "name", "jinni" ] -> [{ id=>1, items => [{ name => "hi!", items => [{ jinni=>2, items => [{...}] }] }] }]
#
sub query_slice(@);
sub query_slice(@) {
	my ($key, $val, @args) = @_;

	my $is_array = ref $val eq "ARRAY" && @$val && ref $val->[0] eq "ARRAY";

	return $is_array? [ query_slice @_ ]: +{ query_slice @_ } if !wantarray;

	my $rows = query_ref(@args);

	if($is_array) {
		my %x; my @x;
		for(@$rows) {
			my $k = $_->{$key};
			push @x, $x{$k} = [] if !exists $x{$k};
			push @{$x{$k}}, $_;
		}
		@x
	}
	elsif(ref $val eq "HASH") {
		map { $_->{$key} => $_ } @$rows
	}
	elsif(ref $val eq "ARRAY") {
		if(@$val) {
			my $col = $val->[0];
			my %x;
			push @{$x{$_->{$key}}}, $_->{$col} for @$rows;
			%x
		} else {
			my %x;
			push @{$x{$_->{$key}}}, $_ for @$rows;
			%x
		}
	}
	else {
		map { $_->{$key} => $_->{$val} } @$rows
	}
}

# Подсоединить в результат запроса результат другого запроса
# 
# $authors = query "SELECT id, name FROM author";
# # $authors as [{id => 1, name => "..."}, ...];
#
# query_attach $authors => 'books:id:author_id' => "SELECT author_id, title FROM book"
#
sub query_attach {
	my ($rows, $attach, $query, %kw) = @_;
	
	($attach, my $key1, my $key2) = split /:/, $attach;

	my %row1 = map { $_->{$attach} = []; ($_->{$key1} => $_) } @$rows;

	my $rows2 = query $query, %kw;

	for my $row2 (@$rows2) {
		my $id = $row2->{$key2} // die "Not $key2 in query!";
		my $row1 = $row1{$id} // die "Not $key1=$id in main rows!";
		push @{$row1->{$attach}}, $row2;
	}

	wantarray? @$rows2: $rows2
}

# Выбрать один колумн
#
#   query_col "SELECT id FROM word WHERE word in (1,2,3)" 	-> 	[1,2,3]
#
sub query_col(@);
sub query_col(@) {
	return [query_col @_] if !wantarray;

	my $rows = query_ref(@_);
	die "Only one column is acceptable!" if @$rows and 1 != keys %{$rows->[0]};

	map { my ($k, $v) = %$_; $v } @$rows
}

# Выбрать строку
#
#   query_row_ref "SELECT id, word FROM word WHERE word = 1" 	-> 	{id=>1, word=>"серебро"}
#
sub query_row_ref(@) {
	my $rows = query_ref(@_);
	die "A few lines!" if @$rows>1;
	$rows->[0]
}

# Выбрать строку
#
#   ($id, $word) = query_row_ref "SELECT id, word FROM word WHERE word = 1"
#
sub query_row(@) {
	return query_row_ref(@_) unless wantarray;
	my $sql = query_prepare(@_);
	my $rows  = query_do($sql, my $columns);
	die "A few lines!" if @$rows > 1;
	my $row = $rows->[0];
	map $row->{$_}, @$columns
}

# Выбрать значение
#
#   query_scalar "SELECT word FROM word WHERE id = 1" 	-> 	"золото"
#
sub query_scalar(@) {
	my $rows = query_ref(@_);
	die "A few lines!" if @$rows>1;
	die "Only one column is acceptable! " . keys %{$rows->[0]} if @$rows and 1 != keys %{$rows->[0]};
	my ($k, $v) = %{$rows->[0]};
	$v
}

# Создаёт части sql-запроса для сортировки по условию, а не лимиту
#
# ("concat(size,',',likes)", "(size < 10 OR size = 10 AND likes >= 12)", ["size", "likes"]) = make_query_for_order "size desc, likes", "10,12"
#
# ("concat(size,',',likes)", 1) = make_query_for_order "size desc, likes", ""
#

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

=head2 query_slice ($key, $val, $query, %kw)

Like query, plus converts the result into the desired data structure.

If you need a hash of the form identifier - value:

	my %author = query_slice name => "id", "SELECT id, name FROM author";
	\%author  # --> {"Pushkin A.S." => 1, "Pushkin A." => 2, "Alice" => 3}

If you need a hash of the form identifier - string:

	my %author = query_slice id => {}, "SELECT id, name FROM author";
	
	my $rows = {
	    1 => {name => "Pushkin A.S.", id => 1},
	    2 => {name => "Pushkin A.",   id => 2},
	    3 => {name => "Alice",        id => 3},
	};
	
	\%author  # --> $rows

If several lines correspond to one identifier, then it is logical to collect them into arrays:

	query "CREATE TABLE book (
		id SERIAL PRIMARY KEY,
	    author_id INT NOT NULL REFERENCES author(id),
	    title TEXT NOT NULL
	)";
	
	stores book => [
	    {author_id => 1, title => "Mir"},
	    {author_id => 1, title => "Kiss in night"},
	    {author_id => 3, title => "Mips as cpu"},
	];
	
	my %author = query_slice author_id => ["title"], "SELECT author_id, title FROM book ORDER BY title";
	
	my $rows = {
	    1 => ["Kiss in night", "Mir"],
	    3 => ["Mips as cpu"],
	};
	
	\%author  # --> $rows

Well, the lines with all the fields:

	my %author = query_slice author_id => [], "SELECT author_id, title FROM book ORDER BY title";
	
	my $rows = {
	    1 => [
	        {title => "Kiss in night", author_id => 1},
	        {title => "Mir",           author_id => 1},
	    ],
	    3 => [
	        {title => "Mips as cpu",   author_id => 3}
	    ],
	};
	
	\%author  # --> $rows

=head2 query_attach ($rows, $attach, $query, %kw)

Includes the result of another query into the result of a query.

C<$attach> contains three keys separated by a colon: the key for the data to be attached, a column from C<$rows> and a column from C<$query>. Rows are merged across columns.

The function returns an array with the result of the query (C<$query>), into which you can attach something else.

	my $authors = query "SELECT id, name FROM author";
	
	my $res = [
	    {name => "Pushkin A.S.", id => 1},
	    {name => "Pushkin A.",   id => 2},
	    {name => "Alice",        id => 3},
	];
	
	$authors # --> $res
	
	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;



( run in 0.593 second using v1.01-cache-2.11-cpan-e1769b4cff6 )