Home› The Database
Shopping at LEGO or Amazon?
Please use our links: LEGO.com • Amazon
As an Amazon Associate we earn from qualifying purchases.

Is there a way to write custom queries directly in SQL?

HartleySanHartleySan Member Posts: 20
in The Database
I'm trying to build a fairly complex query from the the https://brickset.com/queries/add page, but I can't seem to do it using the interface. As such, is it possible for me to write the query directly in SQL and then run it?

Specifically, I want to run a query like the following:

<div>SELECT * FROM Sets </div><div>WHERE (NumericSetNumber >= num-1 AND NumericSetNumber <= num-2) 
OR (NumericSetNumber >= num-3 AND NumericSetNumber <= num-4) </div>OR (NumericSetNumber >= num-5 AND NumericSetNumber <= num-6) 
... 
<div>ORDER BY SortKey ASC;</div>

Thank you.

Comments

  • HuwHuw Administrator Posts: 7,088
    No. It would be impossible to prevent nefarious queries if it was free-form!
    FollowsCloselyMaffyDsid3windr
  • HartleySanHartleySan Member Posts: 20
    Thanks for the reply.

    If you limited the DB user that makes the queries to only making SELECTs as well as verify the submitted query and make sure it it returns valid and expected results (that is, only queries on the Sets table), you'd be fine, no?

    Either way, if I can't make a direct query, fine, but is there a way to do what I want to do through the query interface then?

    Thanks.
  • ricecakericecake Member Posts: 878
    HartleySan said:
    If you limited the DB user that makes the queries to only making SELECTs as well as verify the submitted query and make sure it it returns valid and expected results (that is, only queries on the Sets table), you'd be fine, no?
    There would still be a risk of a carefully (or carelessly!) crafted query that could cause the database to crunch through too much data and cause a DoS.
    MaffyDsid3windr
  • HartleySanHartleySan Member Posts: 20
    I suppose so, yes, but you could easily do things like only allow direct query modification for the WHERE clause and put a LIMIT clause on the query, etc.

    Either way, I saw a direct query as the best way to get what I wanted. Ultimately, if I can get what I want through the interface, then I'm happy.

    Given what I'm trying to do as outlined in my original post, is there a way to create that kind of query through the interface?

    Thank you.
  • HartleySanHartleySan Member Posts: 20
    To provide a concrete example, I generated the following query from the query builder:

    SELECT * FROM Sets
    WHERE (NumericSetNumber >= 79100) AND (NumericSetNumber <= 79105 OR NumericSetNumber >= 79115) AND (NumericSetNumber <= 79122 OR NumericSetNumber >= 70800) AND (NumericSetNumber <= 70819)
    ORDER BY SortKey ASC

    However, the parentheses are in the wrong location (and I can't move them around). What I want is the following:

    SELECT * FROM Sets
    WHERE (NumericSetNumber >= 79100 AND NumericSetNumber <= 79105) OR (NumericSetNumber >= 79115 AND NumericSetNumber <= 79122) OR (NumericSetNumber >= 70800 AND NumericSetNumber <= 70819)
    ORDER BY SortKey ASC
  • HartleySanHartleySan Member Posts: 20
    Sorry for the third post in a row, but what might be a cool addition is the ability to write out all of the set numbers you explicitly want to see in one input, as opposed to having to build out the same conditions over and over again to build out the query.

    Similarly, you could have a "Clone Condition" button or something that would clone a line and reproduce it.

    Long story short, the query builder is a nice idea, but it's very limiting/cumbersome for building out complex or useful queries.
  • PaperballparkPaperballpark Member Posts: 4,268
    I'm really not sure I understand what you're trying to search for.

    As far as I can see, you want to list the following:

    Sets 79100, 79101, 79102, 79103, 79104, 79105.

    OR

    Sets 79115, 79116, 79117, 79118, 79119, 79120, 79121, 79122.

    OR

    Sets 70800 through to 70819.

    The problem I see with this is that it's simply an odd query. Which one of those three do you want to list? You're not asking it to list all of them, just one batch of the three.

    I suspect you're wanting all of them, but in that case wouldn't you be better using the AND statement?

    To be clear, I do see your problem with the brackets. I didn't quite see that until I tried to do a similar search myself, but it is a problem in this case.

    I do think though, that even if the brackets were in the correct places, you'd still need to use AND instead of OR. :)
  • HuwHuw Administrator Posts: 7,088
    edited October 2017
    You can search for a range of sets on the URL. e.g. 
    https://brickset.com/sets?query=79100-79105 

    You can also specify a comma delimited list, e.g.
    https://brickset.com/sets?query=79115,79116,79117,79118,79119,79120,79121,79122

    but not combine the two

  • ricecakericecake Member Posts: 878
    @Paperballpark No, I think his query is correct. If he had ANDs instead of ORs, then it would return no results, because there is no NumericSetNumber that is simultaneously in all three ranges (79100-79105, 79115-79122, and 70800-70819). Therefore, he wants to return all sets that are in any of these ranges. So, it's either in the first range, OR the second range, OR the third range.
  • HartleySanHartleySan Member Posts: 20
    Paperballpark, I appreciate the response, but I don't think my query is that "odd".

    I'm simply trying to list out all the sets that I own so that when my daughters want to do one, instead of having to move all the boxes around on the shelves searching for which one they want to do, that can quickly look at a more visual and organized list from a computer screen / iPad and pick one out.

    And really, regardless of the use case, a query builder should be designed to be flexible enough to handle what I consider to be a relatively simple query.

    Also, I would only use an AND statement if I was listing each set number out separately. For multiple ranges, you have to use ORs between them. For example, "The set is within this range OR this range OR this range."

    Huw, thanks for your response. The ability to add all the sets directly to the URL does help as it's a lot, lot quicker way to get what I want than clicking through the query builder to add each set separately. Still, what would be ideal is if you could combine the commas and the hyphens in the URL to include multiple ranges. For example:

    https://brickset.com/sets?query=79100-79105,79115-79122,70800-70819

    That seems like some pretty basic URL parsing to me (which you're already doing separately), and it would add a lot of power to searching on the site.

    Thanks.
  • HuwHuw Administrator Posts: 7,088
    > i'm simply trying to list out all the sets that I own

    In that case there's a much easier way!

    https://brickset.com/sets/mycollection-owned
    ricecake
  • HartleySanHartleySan Member Posts: 20
    Ah, gotcha. That's what I wanted! I still think that the query builder additions I suggested would still be awesome if you have the time.

    If I click on "I want this set", where is that list stored? (Sorry, still learning all of this.)
  • ricecakericecake Member Posts: 878
    edited October 2017
    Your wanted list will appear here:
    https://brickset.com/sets/mycollection-wanted

    Here is a list of useful tips on how to use/navigate the site:
    https://brickset.com/faq


  • HartleySanHartleySan Member Posts: 20
    Thanks a lot.
  • PaperballparkPaperballpark Member Posts: 4,268
    ricecake said:
    @Paperballpark No, I think his query is correct. If he had ANDs instead of ORs, then it would return no results, because there is no NumericSetNumber that is simultaneously in all three ranges (79100-79105, 79115-79122, and 70800-70819). Therefore, he wants to return all sets that are in any of these ranges. So, it's either in the first range, OR the second range, OR the third range.
    Ahh yes I see! I was thinking of it the other way around - 'list all sets in *this query* OR *this query*', which clearly is a bit odd.

    I wasn't thinking of it in terms of 'add a set to the list if it appears in *this query* OR *this query*'.
  • HartleySanHartleySan Member Posts: 20
    That's interesting. ricecake must have responded to your post around the same time I did because when I responded, his/her response wasn't there.

    Anyway, thanks for the help, guys.
    Just out of curiosity, what're the back-end technologies used for this site? Is it a standard LAMP config?
  • HuwHuw Administrator Posts: 7,088
    edited October 2017
    No, WISA -- Windows, IIS, SQL Server, ASP.net
  • HartleySanHartleySan Member Posts: 20
    Interesting to know. Thanks.
    Huw, are you the sole developer and is this a side hobby or a full-time job?

    Just wanted to let you know that this is a very cool site. Just came across it recently, but it's great for searching. All of the cross-linking and tagging is very, very helpful.
    Thanks.
  • HuwHuw Administrator Posts: 7,088
    Sole developer, and it's a full time job now, has been for the last 5 years.
    sid3windr
  • HartleySanHartleySan Member Posts: 20
    Very cool, man. Best of luck with the site going forward.
  • HartleySanHartleySan Member Posts: 20
    Huw, just out of curiosity, you ever consider an API for the site for allowing to publish the information elsewhere and on other sites?
  • ricecakericecake Member Posts: 878
    There already is an API:
    https://brickset.com/tools/webservices

  • HartleySanHartleySan Member Posts: 20
    Cool. Will look into it.
Sign In or Register to comment.

Shopping at LEGO.com or Amazon?

Please use our links: LEGO.com • Amazon

Recent discussions • Categories • Privacy Policy • Brickset.com

Howdy, Stranger!

It looks like you're new here. Sign in or register to get started.

Sign In Register

Categories

Brickset.com is a participant in the Amazon Services LLC Associates Program, the Amazon.com.ca, Inc. Associates Program and the Amazon EU Associates Programme, which are affiliate advertising programs designed to provide a means for sites to earn advertising fees by advertising and linking to Amazon.

As an Amazon Associate we earn from qualifying purchases.