view release on metacpan or  search on metacpan

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

	my $res = << 'END';
	SELECT *
	FROM author
	    JOIN word1
	    JOIN word2
	    JOIN word3
	WHERE 1
	    AND name like '%Alice%'
	END
	
	$query # -> $res

=head2 query_do ($query)

Executes a request and returns its result.

	query_do "SELECT count(*) as n FROM author"  # --> [{n=>3}]
	query_do "SELECT id FROM author WHERE id=2"  # --> [{id=>2}]

=head2 query_ref ($query, %kw)

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

	my @res = query_ref "SELECT id FROM author WHERE id=:id", id => 2;
	\@res  # --> [[ {id=>2} ]]

=head2 query_sth ($query, %kw)

Like C<query>, but returns C<$sth>.

	my $sth = query_sth "SELECT * FROM author";
	my @rows;
	while(my $row = $sth->fetchrow_arrayref) {
	    push @rows, $row;
	}
	$sth->finish;
	
	0+@rows  # -> 3

=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 => [