131–140 of 166 posts
Earlier quoted context omitted.
hopefully you'll forgive my pedantry - "union all" is vertical concat - "union" without the "all" gives you the distinct list
One of the reasons why SQL is crap: there should be no distinction between the two in relational algebra. A set of {A, B, C, B, C} is the same as {A, B, C}.
Earlier quoted context omitted.
It's not straightforward to do polymorphic joins: one common pattern is to have child tables for each case of the union, but there's no integrity constraint such that each parent must only have one child, e.g. CREATE TABLE schools (id SERIAL PRIMARY KEY); CREATE TABLE colleges (id INTEGER NOT NULL REFERENCES schools (id)); CREATE TABLE high_schools (id INTEGER NOT NULL REFERENCES schools (id)); How can you ensure tha…
> How can you ensure that a school is either a college or high_school but not both? Do you have any real world scenarios where you've faced this problem? In your example, you wouldn't model it like that. A school just needs an attribute that identifies the type of school (high school or college), and other attributes that would be common to both. I'm sure there's lots of examples but it's late and I'm struggling to t…
Here's one suggestion: SQL tediously requires specifying the equality condition on joins, when 90% of the time you just want to join on the fk defined between the tables. from a join b should implicitly join on the FK if no condition is given. It would require knowledge of the schema. I don't know if this is possible in PRQL, or if the transpilation to SQL has to be stateless.
Hello another contributor here! Compilation does have to be stateless (for performance reasons), but we are planning to add some kind of schema definitions which could also specify foreign keys. So joins without conditions would be possible, we'll look into it! What do you think should happen if there are multiple foreign keys connecting the two tables? Should this also work for many-to-many relations with an interme…
create table users (id int primary key, name text);
create table things (id int primary key, creator int references users);
from things select [id, creator.name];I've thought about building a better query language too. I'd love the ability to model sum types in databases, something like: enum SchoolType { College { degrees: Vec }, HighSchool } It's such a common pattern and yet it's so annoying to model in a normal relational database. I wouldn't be surprised if the rise of NoSQL is tied to the inability of relational databases to model basic patterns like this. Part of me ha…
Earlier quoted context omitted.
"What do you think should happen if there are multiple foreign keys connecting the two tables? Should this also work for many-to-many relations with an intermediate table?" If it's not ambiguous, then let me do it. If I rely on ambiguity then throw an exception. In the case of multiple foreign keys, throw an exception, as there's no way to know which one I mean. It'd be nice if I could disambiguate the situation thou…
Unambiguous things can become ambiguous at later points. As soon as you add a second relation between the tables, what once was unambiguous now is, and because of something which may be entirely unrelated to the specifics of the original query. This is where many conveniences that use implicit data run into problems. A small convenience now for the possibility of accidentally breaking because of mostly unrelated chan…
I don't have a specific syntax in mind yet; for illustrative purposes:
defjoin r,m,a = %prejoin_roles() -> { # define a common join path between three relations r,m,a:
from r=ROLES # can hard-code table names or use parameters (which may refer to other parameters)
join m=MOVIES [r.movie_id = m.movie_id]
join a=ACTORS [r.actor_id = a.actor_id]
}
from r,m,a = %prejoin_roles()
select m.title, a.character_name
Here's one suggestion: SQL tediously requires specifying the equality condition on joins, when 90% of the time you just want to join on the fk defined between the tables. from a join b should implicitly join on the FK if no condition is given. It would require knowledge of the schema. I don't know if this is possible in PRQL, or if the transpilation to SQL has to be stateless.
Hello another contributor here! Compilation does have to be stateless (for performance reasons), but we are planning to add some kind of schema definitions which could also specify foreign keys. So joins without conditions would be possible, we'll look into it! What do you think should happen if there are multiple foreign keys connecting the two tables? Should this also work for many-to-many relations with an interme…
Compilation should fail and require you to explicitly specify what key to use. Please don’t do anything magic.
Earlier quoted context omitted.
It's not straightforward to do polymorphic joins: one common pattern is to have child tables for each case of the union, but there's no integrity constraint such that each parent must only have one child, e.g. CREATE TABLE schools (id SERIAL PRIMARY KEY); CREATE TABLE colleges (id INTEGER NOT NULL REFERENCES schools (id)); CREATE TABLE high_schools (id INTEGER NOT NULL REFERENCES schools (id)); How can you ensure tha…
As go_prodev indicated, the former just isn't how you reason about data modeled in a relational form. The latter makes little sense. What constraints are you placing on a college that do not also apply to a highschool, given they're both schools? > The other thing in the grandparent's comment that's a constant pain in SQL is representing an ordered list: how do you insert items into the middle of the list? Depending…
I've thought about building a better query language too. I'd love the ability to model sum types in databases, something like: enum SchoolType { College { degrees: Vec }, HighSchool } It's such a common pattern and yet it's so annoying to model in a normal relational database. I wouldn't be surprised if the rise of NoSQL is tied to the inability of relational databases to model basic patterns like this. Part of me ha…
Doesn’t Postgres support this with table inheritance https://www.postgresql.org/docs/current/tutorial-inheritance... I don’t know if they’re recommended, but they are an option.
Earlier quoted context omitted.
Great point, we'll add that. I don't think we do joins that much better than SQL does. We're thinking whether there's potential there, maybe through understanding foreign keys — but we're being conservative about introducing change without value.
I looked at the book after this and have to say, I'd heavily recommend spending the next dew months just improving joins (and complex joins especially). Like GP says, relational modelling is the interesting bit about SQL and I don't feel exaggerative in saying the only reason I use SQL are joins, and so the only reason I'd introduce the complexity of your project into my stack would be if it makes handling joins, vie…
This seems to have resolved a problem with SQL, you can't read it in a linear fashion.
When we start getting query optimizers for Pandas, much of the benefit of SQL will go away.