Please refrain from posting animated GIFs, memes, joke videos and so on in discussions other than those in the off topic area.

Dismiss this message to confirm your acceptance of this additional forum term of use.
You must be 16 or over to participate in the Brickset Forum. Please read the announcements and rules before you join.

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

HartleySanHartleySan USAMember Posts: 20
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:

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

Thank you.

Comments

  • HuwHuw Brickset Towers, Hampshire, UKAdministrator Posts: 5,849
    No. It would be impossible to prevent nefarious queries if it was free-form!
    FollowsCloselyMaffyDsid3windr
  • HartleySanHartleySan USAMember 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 Maryland, USAMember Posts: 762
    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 USAMember 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 USAMember 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 USAMember 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 UKMember Posts: 2,765
    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 Brickset Towers, Hampshire, UKAdministrator Posts: 5,849
    edited October 24
    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 Maryland, USAMember Posts: 762
    @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 USAMember 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 Brickset Towers, Hampshire, UKAdministrator Posts: 5,849
    > 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 USAMember 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 Maryland, USAMember Posts: 762
    edited October 24
    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


  • PaperballparkPaperballpark UKMember Posts: 2,765
    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 USAMember 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 Brickset Towers, Hampshire, UKAdministrator Posts: 5,849
    edited October 24
    No, WISA -- Windows, IIS, SQL Server, ASP.net
  • HartleySanHartleySan USAMember 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 Brickset Towers, Hampshire, UKAdministrator Posts: 5,849
    Sole developer, and it's a full time job now, has been for the last 5 years.
    sid3windr
  • HartleySanHartleySan USAMember Posts: 20
    Very cool, man. Best of luck with the site going forward.
  • HartleySanHartleySan USAMember 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?
  • HartleySanHartleySan USAMember Posts: 20
    Cool. Will look into it.
Sign In or Register to comment.
Recent discussions Categories Privacy Policy