SQLite Forum

Using an alias in queries
Login

Using an alias in queries

(1) By Trudge on 2021-06-09 15:37:05 [link] [source]

I've noticed that using an alias in a query seems to behave different in a web edition of code compared to a CL version of the same code.

I am pulling data from a DB via Perl and trying to create a hash with dynamic keys. The database contains books, authors, and keywords. I am trying to get the top 5 keywords based on the number of times it appears in the DB.

I need the keys to be dynamic because as the DB grows the most popular keywords will change.

The web code: my $NumKWBooks=0; my @SubColors=("red","orange","yellow","green","blue"); $ndx=0; $sth=$dbh->prepare(qq{ select count(keyword) as NUM,keyword from keywords group by keyword order by NUM desc limit 5;}); $sth->execute(); while (@records=$sth->fetchrow_array()) { print qq{$records[0] $records[1] $SubColors[$ndx]
}; $NumKWBooks+=$records[0]; $KeyWords{keyword} = $records[1]; $KeyWords{numbooks} = $records[0], $ndx+=1; } $sth->finish(); print "
"; print qq{$NumKWBooks: $NumKWBooks
}; my $numKeys=keys %KeyWords; print "$numKeys: $numKeys
"; foreach $key(keys %KeyWords) { my $percentKW=$KeyWords{numbooks} / $NumKWBooks * 100; print qq{$KeyWords{keyword}: $tab }; printf "%.2f%%
",$percentKW; }
This renders in a browser: 64 Programming red 47 Mathematics orange 24 Perl yellow 22 Knots green 13 Calculus blue

$NumKWBooks: 170 $numKeys: 2 Calculus: 7.65% Calculus: 7.65%

However in CL I get the following: records[0] Programming red records[0] Mathematics orange records[0] Perl yellow records[0] Knots green records[0] Calculus blue $NumKWBooks: 170 $numKeys: 2 Calculus: 7.65% Calculus: 7.65%

Note the missing value for $records[0]. The only reason I can figure for this is the way the alias is rendered select count(keyword) NUM

And why are there only 2 keys in the hash, but both the same?

I would appreciate any advice on how to get that working.

(2) By Trudge on 2021-06-09 18:20:41 in reply to 1 [source]

On doing some research on using 'alias' I discovered that 'An alias only exists for the duration of that query.' Is this a fact for SQLite?

(3) By Ryan Smith (cuz) on 2021-06-09 19:55:32 in reply to 2 [link] [source]

Yes.

Do not confuse an Alias for a Variable.

If I say "Here is a tree, let's call him John" and then say "How tall is John?" one might get an answer of 25 meters. If I then say "Here is a cricket, let's call him John" and again ask "How tall is John?" I might now get an answer in millimeters. The word "John" carried no value in any circumstance. It is merely a pointer/label to a thing with value and it only points/labels correctly while in scope (duration of query in the case of SQL).

Other than this, the reason I think nobody is answering your question is that none of us really understand what you mean. (I should speak for myself of course, perhaps loads of clever people here know exactly what you are asking).

For me though, your code seems to hang together loosely and I don't know what you mean by "CL". Also we don't really speak "web".

Is there a way for you to make an SQL query (perhaps a schema and query) that demonstrates your problem so we do not have to spend hours guessing and recreating your code? If it's a query that doesn't work, the problem is with SQLite. If it's code that doesn't work (even when involving a query), it's likely the fault is with your code or the wrapper you use.

We can mostly only help with SQLite Query problems. Code/Wrapper problems should be directed to their respective forums, and to know whether it is a SQL error or not, we need to see a query/schema that doesn't work.

(4.1) By Trudge on 2021-06-10 14:32:50 edited from 4.0 in reply to 3 [link] [source]

Apologies for the confusion, and thank you for taking some time with this. CL is Command Line.

schema: CREATE TABLE sqlite_sequence(name,seq); CREATE TABLE IF NOT EXISTS "Keywords" ( id INTEGER, authorid INTEGER, bookid INTEGER, keyword TEXT, PRIMARY KEY(id AUTOINCREMENT) ); CREATE TABLE test ( text TEXT ); CREATE TABLE IF NOT EXISTS "Books" ( id INTEGER, authorid INTEGER, bookid INTEGER, pubyear INTEGER, title TEXT, notes TEXT, author TEXT, PRIMARY KEY(id AUTOINCREMENT) ); CREATE TABLE IF NOT EXISTS "Authors" ( id INTEGER, authorid INTEGER NOT NULL, author TEXT NOT NULL, PRIMARY KEY(id AUTOINCREMENT) );

Here is a typical bit of the Keywords table: id authorid bookid keyword


2122 1 330 Mathematics 2123 1 331 Mathematics 2124 1 323 Philosophy 2125 2 404 Knots 2126 5 82 Document Design 2127 5 152 Web Design 2128 6 550 Scalable Vector Graphics 2129 7 179 Physics
2130 7 178 Quantum Theory 2131 8 294 bash

A CL version of the code: @SubColors=("red","orange","yellow","green","blue"); $ndx=0;

$sth=$dbh->prepare(qq{ select count(keyword) as NUM,keyword from keywords group by keyword order by count(keyword) desc limit 5;}); $sth->execute(); while (@records=$sth->fetchrow_array()) { print qq{records[0] $records[1] $SubColors[$ndx]n}; $NumKWBooks+=$records[0]; $KeyWords{keyword} = $records[1]; # dynamic hash $KeyWords{numbooks} = $records[0], # dynamic hash $ndx+=1; } $sth->finish();

print qq{$NumKWBooks: $NumKWBooksn}; my $numKeys=keys %KeyWords; print "$numKeys: $numKeysn"; foreach $key(keys %KeyWords) { $percentKW=$KeyWords{numbooks} / $NumKWBooks * 100; print qq{$KeyWords{keyword}: $tab }; printf "%.2f%%n",$percentKW; }

which results in: records[0] Programming red records[0] Mathematics orange records[0] Perl yellow records[0] Knots green records[0] Calculus blue $NumKWBooks: 5 $numKeys: 2 Calculus: 20.00% Calculus: 20.00%

As you can see $records[0] is not getting populated since it is an alias in the query loop. Also the number of keywords in the 5 chosen is wrong - it should be over 100.

This is the bit that is crucial for my purposes since I am using these values to build an SVG donut chart of the top 5 keywords in this library. No joy so far.

(5) By Ryan Smith (cuz) on 2021-06-10 21:55:42 in reply to 4.1 [link] [source]

You show the full schema - which helps.

You do not however show a query that doesn't work, but in stead some Command-Line thing (I assume since that what you said you mean with "CL") that looks a lot like PHP/Pearl code to me and nothing like any command line I know (but I don't know all of them).

Do you have a normal query that doesn't work? Executed inside the sqlite CLI?

I can run your schema no problem but then I do not have the SQL that populates the data, though I could make that from the sample you sent, if I had the spare time.

The more complete you send your environment, the better we can help.

I will say this - From your code, on this line:

$sth=$dbh->prepare(qq{ select count(keyword) as NUM,keyword from keywords group by keyword order by count(keyword) desc limit 5;});
You clearly limit the output to 5 rows with the "LIMIT 5" - why does it surprise you that only 5 rows are produced? and in this line:
print qq{records[0] $records[1] $SubColors[$ndx]n};
why do you say "$records[1]" but "records[0]" without the dollar sign? I don't know exactly what you are trying and that language may have nuances I am not familiar with, but my base understanding of programming leads me to believe the inconsistency would in fact cause different outputs.

Please give insert statements that put the same data in that DB than what you have (though it may well be less and perhaps obfuscated in case some of it is sensitive information), then a Query that you've tried that did not work. You have done well to explain what you expected to see from that query, though you have not stated exactly what you want to know from the data - perhaps another query might be better, but we can only help with that if we know what exactly you need to know from the data.

I know on many forums it is considered rude to be overly verbose, and it may be true of long-winded questions, but when it comes to the technical data, full code/schemata/queries are much more helpful and appreciated.

(6) By Trudge on 2021-06-11 14:38:56 in reply to 5 [link] [source]

Thank you for the in-depth analysis and apologies for the confusion. When I mentioned CL (Command Line) I was referring to Perl running in a shell window. Here is the actual SQL statement that I run: sqlite> select count(*) as NUM,keyword from keywords group by keyword order by count(keyword) desc limit 5; NUM keyword


67 Programming 47 Mathematics 24 Perl
22 Knots
13 Reference
sqlite>
That output is correct. I only want the top five keywords used in a library DB. There is obviously no Perl hash involved here.

Note that the results are within the query loop. But when I try to access the hash %KeyWords from a perl loop, I have problems. I am trying create a donut chart with SVG of the top 5 keywords. For that I need certain calculations, one of which is the percentage of each keyword to the total number of keywords.

But notice that in the SQL query I use an alias for the count(*). I have to use an alias because I don't know the keyword before hand - the library DB changes all the time. I could say count(keyword) as NUM but that would not make any difference - the key to the whole issue is using an alias.

Apparently in SQLite an alias is only available during the query. So when I try to access the hash outside of the query loop, it fails.

The line you refer to why do you say "$records[1]" but "records[0]" without the dollar sign? is wrong. I had pasted code in from a previous version. Many apologies.

I'm not sure if showing the insert statements would be any help to solving the issue. As mentioned the DB grows as I add / delete books etc. and that is all done in another Perl script. But if you're interested: $sth=$dbh->prepare(qq{insert into keywords(authorid,bookid,keyword) values ($AuthorID,$TitleID,"$kw")}); As further explanation, I am trying to create a dynamic Perl hash from an SQLite query using an alias. So not sure whether this is an SQLite issue or a Perl issue. I've posted on a Perl forum and still no joy.

(8) By Ryan Smith (cuz) on 2021-06-11 17:19:20 in reply to 6 [link] [source]

Alright, we getting closer. The insert statements would simply have helped me to produce a copy of your exact DB on my side - but since the DB file and query seems to produce output you expect, there is not much use in that.

The problem boils down to, if I understand your post correct, that while the query works, an alias in the query is somehow not propagated to your code, at least outside some scope.

I have to say that that is something that is completely up to your code or perhaps the wrapper that you use. In SQLite there is no scope "outside" the query, and anything that was transferred from SQLite to the application code was done so "within" the query call.

I estimate (but do not know for sure) that your PERL wrapper somehow "forgets" about the returned names (i.e. column Aliases) once the query completed, and even though you are calling it in a loop, that loop simply walks PERL memory, and the SQLite API has long since been put to rest (presumably).

Why do I estimate this?

In normal API usage, we would prepare the Query, set the parameters (if any), then keep calling sqlite3_step() and reading the bound values from the statement until it returns something other than SQLITE_ROW. During that time, at any point, you can get the column name from the prepared statement (See: sqlite.org/c3ref/column_name) which, if you read it, you would notice is exactly what you expected, namely the alias specified in the query.

You can therefore see that it is very hard for us to guess at WHY your PERL code or its wrapper (that does all this preparing and stepping for you) is somehow not knowing this information. All I can tell you is that on SQLite's side that information is definitely available and works exactly like how you expected.

Our knowledge and jurisdiction ends right at those API functions. Once the higher level application code has the value, we have no clue what/how/why stuff happens to it.

Maybe a PERL person or forum would be more help to you.

(7) By Trudge on 2021-06-11 16:52:03 in reply to 5 [link] [source]

SOLVED I've managed to get what I need by saving the data from the query into an array: Now the array ... @kwtop has 10 elements $NumKWBooks: 173 Programming 67 red 38.73% Mathematics 47 orange 27.17% Perl 24 yellow 13.87% Knots 22 green 12.72% Calculus 13 blue 7.51%

This is WITHOUT using an alias. Tried it with a hash (no alias) but still something wonky, maybe with the way I'm coding it. Either way I can now move forward with the SVG process.

Thank you to all who burrowed into this. Very much appreciated.