Brickset forum
Howdy, Stranger!
It looks like you're new here. If you want to get involved, click one of these buttons!
Categories
You must be 16 or over to participate in the Brickset forum. Please read the announcements and rules before you join.
Advanced query builder
in Brickset.com
I've mentioned that I was working on an 'advanced' means of querying the database in several threads. I now have something to look at and test, although it doesn't actually do any searching yet.
The user interface for creating ad-hoc queries is just about done and I'd like your feedback.
http://www.brickset.com/search/advanced/
You can create queries and see the resultant SQL that will, eventually, be executed (using a r/o database account, I hasten to add, to prevent SQL injection vulnerabilities)
You can specify queries to do things like
"show me all polybag sets released since 2007 that I don't own, order by year released, then number".
"Show me all sets with between 5 and 10 minifigs, order by number of minifigs, descending"
"Show me all sets in the themes Aquazone and Aquaraiders that cost less than $10, and order by year released"
"Show me all sets with an X in the name that were available from shop.LEGO.com in 2008" (useful, eh!)
that sort of thing, which can't of course be done at the moment.
Not every query will be possible because, to keep things simple, you can't specify how the terms should be bracketed. It assumes that you want OR terms bracketed together which in 95% of cases I believe you will do.
So, what you're testing is the UI for specifying the query and whether the SQL generated is what you're expecting. When that's done, I'll move on to saving and executing the query, then editing and sharing them with others.
There's a couple of things still to do, including providing guidance as to what type of data is valid in the text box (date, number, anything) and date validation.
LMKWYT...
The user interface for creating ad-hoc queries is just about done and I'd like your feedback.
http://www.brickset.com/search/advanced/
You can create queries and see the resultant SQL that will, eventually, be executed (using a r/o database account, I hasten to add, to prevent SQL injection vulnerabilities)
You can specify queries to do things like
"show me all polybag sets released since 2007 that I don't own, order by year released, then number".
"Show me all sets with between 5 and 10 minifigs, order by number of minifigs, descending"
"Show me all sets in the themes Aquazone and Aquaraiders that cost less than $10, and order by year released"
"Show me all sets with an X in the name that were available from shop.LEGO.com in 2008" (useful, eh!)
that sort of thing, which can't of course be done at the moment.
Not every query will be possible because, to keep things simple, you can't specify how the terms should be bracketed. It assumes that you want OR terms bracketed together which in 95% of cases I believe you will do.
So, what you're testing is the UI for specifying the query and whether the SQL generated is what you're expecting. When that's done, I'll move on to saving and executing the query, then editing and sharing them with others.
There's a couple of things still to do, including providing guidance as to what type of data is valid in the text box (date, number, anything) and date validation.
LMKWYT...
Comments
1. Remove the second sort from default visibility and add "+" to add multiple sort levels. It may be confusing to see "select..." in the second row when its not required that you actually select anything.
2. The "remove" criteria is missing from the last entry line.
3. If you can't code the SQL for case insensitivity, you may want to add a disclaimer to users that they woiuld need an OR to check case variations (e.g. "Lego" vs "lego").
Looking forward to seeing it run!
- Spam
- Abuse
0 • Like •- Spam
- Abuse
0 • Like •1. Good idea
2. It's supposed to be, otherwise you could remove everything and not be able to add anything more.
3. The SQL Server is case-insensitive anyway, but it might be worth adding a note to that effect in the intro text.
- Spam
- Abuse
0 • Like •- Spam
- Abuse
0 • Like •- Spam
- Abuse
0 • Like •Despite the wonderful number of queries available, I'd like to ask about another :o) Is it possible to add in the existing Amazon Watch % discount queries?
- Spam
- Abuse
0 • Like •- Spam
- Abuse
0 • Like •-You can now delete all entries, except the first
-Multiple sort level suggestion above implemented
-'help text' appears to tell you what sort of input is expected
-strings, numbers and dates should now be validated.
Have another play and LMKWYT...
http://www.brickset.com/search/advanced/
Thanks
- Spam
- Abuse
0 • Like •- Spam
- Abuse
0 • Like •WHERE (SetName LIKE '%bat%' AND YearFrom < 2010 OR Pieces >= 1000) ORDER BY YearFrom ASC
Shouldn't it be:
WHERE
(SetName LIKE '%bat%' AND (YearFrom < 2010 OR Pieces >= 1000) ORDER BY YearFrom ASC- Spam
- Abuse
0 • Like •- Spam
- Abuse
0 • Like •- Spam
- Abuse
0 • Like •- Spam
- Abuse
0 • Like •- Spam
- Abuse
0 • Like •http://www.brickset.com/search/advanced/
LMKWYT...
I'll work on the execution bit when back from AFOLCON next week.
- Spam
- Abuse
0 • Like •- Spam
- Abuse
0 • Like •I think it's pretty much complete:
- You can create, name and save queries
- Queries are now actually executed and results are shown in the normal way.
Things still to do
- The run button on the query builder page doesn't do anything yet, you need to save the query then return to your query list to execute it. As a result, you must be logged in to run queries, and you can't test it as you build it easily.
- A means to mark your queries as sharable and a means for admins to highlight universally useful ones, the list of which will ultimately replace the 'data mining' page.
Anyway, have a play and LMK if it works as you expect it to.
- Spam
- Abuse
0 • Like •When entering UK price per piece, is this in pence or pounds e.g. 0.07 or 7? I'm getting weird results and can't figure out if it's me or the scripts :O)
- Spam
- Abuse
0 • Like •SELECT * FROM SETS
WHERE (UKPricePerPiece < 10) AND (YearFrom > 2010)
ORDER BY UKPricePerPiece DESC
returns 174 matching records, but nothing displayed apart from the pagination.
SELECT * FROM SETS
WHERE (UKPricePerPiece < 0.10) AND (YearFrom > 2010)
ORDER BY UKPricePerPiece DESC
returns 8 sets that are free.
- Spam
- Abuse
0 • Like •- Spam
- Abuse
0 • Like •I should be able to sort it though...
- Spam
- Abuse
0 • Like •I'll continue to investigate...
- Spam
- Abuse
0 • Like •http://www.brickset.com/search/advanced/run/?q=18
which is
SELECT * FROM Sets
WHERE (UKPricePerPiece < 10) AND (UKPricePerPiece > 0) AND (YearFrom >= 2010)
ORDER BY UKPricePerPiece ASC
- Spam
- Abuse
0 • Like •- Spam
- Abuse
0 • Like •- Spam
- Abuse
0 • Like •One idea though, when doing 'equals' for string fields, maybe do a like without the % characters - this will force a case insensitive compare (= may do this, but it's implementation dependent IIRC).
eg,
SELECT * FROM Sets
WHERE (SetName LIKE 'test')
If so, then <> would also need changing to NOT LIKE.
Looks like you're also assuming that OR and AND are always parsed as (A OR B) AND C - ie, it doesn't appear possible to do A AND (B OR C). Not sure how to offer this functionality to the user though and it might be confusing to new users. Maybe have the option to edit the generated SQL (maybe just the WHERE clause portion to prevent people trying to query things they shouldn't) by hand?
- Spam
- Abuse
0 • Like •It also seems to me that you can see everyone's queries by simply guessing the query number:
http://www.brickset.com/search/advanced/run/?q=20
- Spam
- Abuse
0 • Like •You can execute any query by guessing a number, but I don't think that's an issue, unless I'm missing something? Even if you include 'I own/want' in it, it applies to the person running the query rather than the person who created it.
- Spam
- Abuse
0 • Like •- Spam
- Abuse
0 • Like •- Spam
- Abuse
0 • Like •Almost there now, I think...
- Spam
- Abuse
0 • Like •- Spam
- Abuse
0 • Like •All 'data mining' queries have been recreated using this, and in time I plan to add more general purpse ones to the list, that I, and others, create.
Any problems, LMK...
- Spam
- Abuse
0 • Like •- Spam
- Abuse
0 • Like •- Spam
- Abuse
0 • Like •- Spam
- Abuse
0 • Like •You should add that!
- Spam
- Abuse
0 • Like •- Spam
- Abuse
0 • Like •- Spam
- Abuse
0 • Like •- Spam
- Abuse
0 • Like •So, you could build something to show all sets you own, ordered by 'order added to your collection' descending to see those you'd added recently.
The 'Collections' table that holds who owns and wants what currently contains 5.9 million records. Each row consists of 4 integers and 2 bits (total 18). If I were to add a datetime field to record the date items were added it would increase the row length by 8, almost a 50% increase, which is obviously undesirable on such a large table, for a feature that would receive relelatively little use.
Maybe I can do something in the ACM table, though...
HTH, a bit...
- Spam
- Abuse
0 • Like •- Spam
- Abuse
0 • Like •I'll add it to the to-do list :)
- Spam
- Abuse
0 • Like •