What is the correct way to query data from an SQL Database?
Usually structured data is required in a program. To query a hierarchy of objects would require a loop of queries for every level of the data structure. But queries are slow. The native solution to this are joins. But joins will denormalize the data.
+---------------------+----------------------------------+-------------------------------+----------+--------+ | author | title | LEFT(quote, 25) | position | tag | +---------------------+----------------------------------+-------------------------------+----------+--------+ | Lewis Carroll | Alice's Adventures in Wonderland | In a minute or two the Ca | p.42 | animal | | Lewis Carroll | Alice's Adventures in Wonderland | “All right,” said the Cat | p.56 | animal | | Lewis Carroll | Alice's Adventures in Wonderland | “All right,” said the Cat | p.56 | grin | | Richard M. Stallman | Free Software, Free Society | A hacker is someone who e | p.96 | hacker | +---------------------+----------------------------------+-------------------------------+----------+--------+
A transformation to the hierarchical data structure would again require the previously mentioned loops.
>So what is the best way to query SQL data? >Would it be asked to much if SQL databases would return structured data instead of denormalized tables?
>The native solution to this are joins. But joins will denormalize the data. Since the joined data is only temporarily used to display the data, that is ok.
Jordan Torres
tl;dr just fucking use mongodb
instead of loops use subquerries
return arrays if you have to
postresql supports arrays and can also return json
Cameron Morgan
>Since the joined data is only temporarily used to display the data, that is ok. My question was not targeting at a problem in the database. My point is, that a denormalized table is chunky and hard to process in a program.
Christian Russell
>postresql supports arrays and can also return json I did not know that, that is a pretty good thing.
Sebastian Nelson
>My question was not targeting at a problem in the database. My point is, that a denormalized table is chunky and hard to process in a program. That's why you do filtering etc in the database.
Adrian Jones
>>My question was not targeting at a problem in the database. My point is, that a denormalized table is chunky and hard to process in a program. >That's why you do filtering etc in the database. yes but then you have to do a lot of queries to load all the data. This is actually the point of this thread.
Jose Cox
how would you load the data of the given example in OP?
Ian Gray
Unfortunately it is not entirely clear to me what OP wants to query for. OP says something about hierachy, but his table doesn't indicate it.
Kevin Wood
>yes but then you have to do a lot of queries a single query with subqueries > to load all the data. the database optimizes the query to minimize data loading. >This is actually the point of this thread. this makes this thread pointless
SQL as a language is based on an algebra this means that all operations are completely defined in their behavior and how they relate to each other the database engine then can safely rearrange the query and drop meaningless parts.
no this thread helps me a lot by finding out about subqueries
Eli Roberts
In the end I need a structure like this:
[ { "author": "Lewis Carrol", "books": [ { "title": "Alice's Adventures in Wonderland", "quotes": [ { "quote": "In a minute or two the Ca…", "position": "p.42", "tags": ["animal"] }, { "quote": "“All right,” said the Cat…", "position": "p.56", "tags": ["animal", "grin"] } ] } ] }, { "author": "Richard M. Stallman", "books": [ { "title": "Free Software, Free Society", "quotes": [ { "quote": "A hacker is someone who e…", "position": "p.96", "tags": ["hacker"] } ] } ] } ]
or anything similar as nested arrays or nested objects, or whatever
Jordan Campbell
Just use an ORM
Jaxon Edwards
>Just use an ORM Is that really the right thing to do? Every time I looked into an ORM I was not sure if it would do in the end what I wanted it to do. I also always feared the projects would get orphaned after a while.
Do you think it would be possible to maintain a ORM myself when push comes to shove?
Thomas Jackson
Use neo4j It's a graph database It can handle those more complex structures with a different syntax The syntax more resembles pattern matching
Gabriel White
if you fucking think you can find new and unsolved problems in 40 year old technology, this is the wrong field for you. Fucking JS Hipsters, brocoders and other scum. Learn your fucking craft.
what is a hierarchy of objects? is that they same thing as seperate tables? RDBMS have chains of related columns between tables.Joins work in such systems when you are after data in specific columns on specific tables. What am I missing?
Does OP know what a blob is? Structured and unstructured data can reside in blobs.
Christopher Hill
KYS
Carter Thomas
>A hacker is someone who eats foot cheese and smells really bad.
James Clark
>if you fucking think you can find new and unsolved problems in 40 year old technology, this is the wrong field for you. Fucking JS Hipsters, brocoders and other scum.
I created this thread to find out about the solution. I actually did not study informatics or computer science but physics and engineering, and now I came here to ask you how it is done properly. But you might be right. Possibly a lot of Onions Boys do not know about it, and that might be the reason why I was not able to google it so far.
