View support

[Pilskalns]

Hi,

Are you considering adding support for views? 😃

[ikkez]

Hi, thanks for your support and this idea. Well creating views can include a lot of complex queries, depending on what the view should actually consist of (virutal fields, calculations, joins and where conditions) and altering views is limited and could need super user privileges and I'm not sure if that's something that could easiely fit into an abstracted object interface. What do you expect to do with "View support"?

[Pilskalns]

Yeah... SQL syntax can be *itchy, especially to compliment different environments and limitations... This is why I turned to this plugin to implement incremental DB versioning upgrades for internal statistics tool I maintain.

And in this case I use view to join two tables. Let's assume we have table_users (id, name, orgid) and table_orgs (orgid, name) and quite simple, but important view I use is to add org name to users.

CREATE OR REPLACE VIEW `view_users` AS
SELECT users.*,orgs.name AS orgname
FROM users
LEFT JOIN orgs ON users.orgid = orgs.orgid
ORDER BY orgname DESC;

Had to use AS, because initially used same column names between tables... At that point I didn't know anything about SQL Views and thought will be joining this info in app code. Local SQLite was happy about that, but deployed on prod MySQL broke off everything...

So this is my use case:

1. Take two tables
2. Add aliases for columns with same names
3. Join on where ID columns matches
4. Append with ordering

[ikkez]

Well usually I use cortex for that matter to mash-up data from multiple tables. Sure, simple Views like in your sample here are fine too, but unfortunately I can hardly imagine how that would fit into the schema builder at the moment, since joins, aggregations and ordering are actually part of cortex already (just without the views). Do you already know what exactly broke when going from SQlite to MySQL? Maybe it was just the backticks ` to quote the table name?

[Pilskalns]

Thing was that SQLite didn't show any issues when joining a view where both tables had a column with same name. Developed that thing, pushed on prod (so from SQLite to MySQL) and nothing was working because MySQL does not like that. Then spent rest of the evening figuring out what alias is (orgs.name AS orgname in example above) and how to use in my case.

Again, this was first time me using views. As well, at that point managed syntax differences with few helper variables like if SQLite then use AUTOINCREMENT, else AUTO_INCREMENT etc.

Whole point of this issue was simply to point out idea not demanding or something. Thanks for explaining and extra info, maybe on next project try Cortex. Appreciate.

P.S. Feel like this topic is exhausting itself and you can close the issue.

[ikkez]

Sure, and thanks for the idea anyways.. it's always good to intestigate new things. Perhaps at some other point, it'll come back onto table ;)