search

auraphp / Aura.SqlQuery

Independent query builders for MySQL, PostgreSQL, SQLite, and Microsoft SQL Server.
MIT License
450 stars 83 forks source link

Option to hide columns when * is selected #98

Closed melvinchia closed 8 years ago

melvinchia commented 8 years ago

Is it feasible to have an option to hide columns when * is selected?

$queryfactory->newSelect()->cols(['*']->hideCols(['col1', 'col2', 'col3', 'col4']);

This request may sound strange, but I have a table with 50 columns and I want 5 columns hidden. I can either provide a 45-column array, or select * and specify the 5 columns I want hidden from the results. Is this a feasible feature request?

pmjones commented 8 years ago

I have a table with 50 columns and I want 5 columns hidden. I can either provide a 45-column array, or select * and specify the 5 columns I want hidden from the results

Ahh, that's a tough one. Let's work through it a bit. First question: if you were trying to implement that in plain SQL, how would you do it?

melvinchia commented 8 years ago

I was thinking it might be able to happen AFTER the sql query, as the object/array is being created the specified hidden columns could be dropped, then the final object/array returned without the hidden columns?

Should be much less painful that way?

harikt commented 8 years ago

@melvinchia the fact is Aura.SqlQuery don't read the table schema information. Probably you may want to use something easily via reading the tableschema and removing the unwanted columns yourself.

Thank you

melvinchia commented 8 years ago

@harikt Aura.SqlQuery doesn't need to read the table schema information. I could do a ->cols(['*']) and later run a foreach() loop in php to unset() the columns I don't want, or I could have it done within Aura.SqlQuery when it maps the query rows to objects, it just has to drop the specified column names.

I'm guessing my question now is, is it a feasible request for it to happen inside Aura.SqlQuery, or outside in a foreach() loop?

harikt commented 8 years ago

@melvinchia I guess the sort of hiding functionality is out of scope for the query builder. It sounds good for certain cases like Aura.Sql and SqlQuery is integrated. Eg : Atlas.Orm to hide things like password fields etc.

melvinchia commented 8 years ago

I am using Aura.Sql and Aura.SqlQuery in my build. I tried a few other ORM, but gave up after the complexity of having to build extensive datamaps or models for each and every database table I have to use...

pmjones commented 8 years ago

Hi @melvinchia,

Combining quotes from two of your responses:

I was thinking it might be able to happen AFTER the sql query ... I could have it done within Aura.SqlQuery when it maps the query rows to objects

One problem here is that SqlQuery does not map query rows to objects. It doesn't actually issue the query; you pass the query (as built) through something else to get back a result set.

Based on this discussion, it sounds to me like you need a "data mapper" layer of some sort. Maybe I'm missing something about your question?

melvinchia commented 8 years ago

Ahh.. Ok.. I get it now.. It should be in a layer above SqlQuery.. =)

Thanks all.. Closing.