Supporting deselect/select *?

[mamcx]

I deal with query execution at runtime without apriori (full) knowledge of the schemas. I connect to many ERPs and they could change part of the tables. This means that support of SELECT * is important. Also, for when if you wanna do your own SQL Repl and another kind of projects.

Also, one functionality that is part of the relational model but not present on SQL is the inverse of SELECT. Think, for example, you have a table with 20 fields and do not want 1. You need to list all that fields, instead, the relational model allows you to do something like:

DESELECT pwd FROM (
    SELECT username, pwd FROM users
)  --shows username only

BTW, the lack of DESELECT is one of the reasons using SELECT * happens

[tyt2y3]

I have no objection to adding a select *

But I guess it is not too easy to get back and process the results in Rust

I think if you want to 'select * but X', you can probe the schema and list the columns first, then filter out columns you don't want.

In this case, the other tool called SeaSchema will be helpful.

[mamcx]

Yeah, I understand the easy way is to add all columns then remove them. Having it on the query allows to build it dynamically in a chain (instead of at the first moment when the query was built), so that is the reason of the suggestion. Manipulating the columns of the query is limited now (for example, I can add many times the same column with the same name without a way to clean it) and this is the easiest way to allow to manipulate them.

[tyt2y3]

I don't understand how DESELECT could be implemented without knowing the schema first. Can you illustrate a bit?

[mamcx]

Yes, is necessary to know the schema or have already added the columns:

let q = query.columns(Zone::iter()); //Currently, all columns.
// Later. Note you can add columns, but not remove..
let q = q.column(Zone::name);

[tyt2y3]

Well I think that is certainly doable. But not within SeaQuery, because SeaQuery does not manage the connection, it's only a SQL builder. I think you can achieve this by using SeaSchema together with SeaQuery. If you come up with some ideas to make that more ergonomic, it'd be great!

Otherwise I am not sure what we can do for now.

[mamcx]

The issue is that the internal list of columns only supports adding. You can't introspect them* (only inside query builder) nor remove them (that will enable this idea).

• For example, I try to support named parameters but without knowing which columns are present can't.

[RomainMazB]

@mamcx I've submitted a SELECT * and SELECT `table`.* implementation here: https://github.com/SeaQL/sea-query/pull/219

Maybe this would this fix your needs?

[mamcx]

It helps, but is not the issue of deselect

[RomainMazB]

Never heard about this into SQL standard and I can't find something accurate. Could you provide a documentation on it? I also made another PR which adds a unary operator improvement to support EXISTS. This could ease the implementation of DESELECT to only a piece of bit.

[mamcx]

This is not part of SQL, is of the relational model. Is also very convenient to have.

[ikrivosheev]

@mamcx Do you want method: query.remove_column ?

[mamcx]

@ikrivosheev I think so.

[ikrivosheev]

@tyt2y3 @billy1624 Do we want to add a new API for remove columns?

[tyt2y3]

Sorry but I think it's a strange idea from API design point of view. A practical concern is that we cannot remove an Expr from the select list.

[ikrivosheev]

@tyt2y3 We can remove by index

[billy1624]

Or, we provide a mutator for pub(crate) selects: Vec<SelectExpr> field in SelectStatement. The mutator will take a closure that with &mut access to the selects field.