limitations listed

[vltr]

hello @benlau! sorry for opening an issue, but i don't know where else to put this. you say that "join select" is a limitation of dquest. can you explain me why? i'm having a great time with dquest and would make a lot of usage of join selects :)

best regards, richard.

[benlau]

Hi @vltr ,

Thanks for your interest in DQuest!

When I design DQuest, I would like to make it as a very fast solution for CRUD operation to a single model. Since it is the most common operation for many desktop applications. Therefore , most of the time was spent to optimize the API for CRUD operation. So the reason to not support "join" , is simply because I don't have time to design.

[vltr]

Thank you @benlau! I was looking at your implementation, and wondering here because I'm making a SQL API for Qt/C++. It is not ready, and I don't even have the API working as a whole (TDD), but it would be something like:

    // -------------------------------------------------------------------------
    // multiple joins with select AS a table
    stmt = QString("SELECT Track.'Name', MediaType.'Name', Genre.'Name', Track.'Composer', Track.'UnitPrice', album.'artist', album.'album' FROM Track JOIN MediaType ON MediaType.'MediaTypeId' = Track.'MediaTypeId' JOIN Genre ON Genre.'GenreId' = Track.'GenreId' JOIN (SELECT al.'Title' AS album, ar.'Name' AS artist, al.'AlbumId' AS album_id FROM Album AS al JOIN Artist AS ar ON ar.'ArtistId' = al.'ArtistId') AS album ON album.'album_id' = Track.'AlbumId'");

    UnTable track = db.table("Track");
    UnTable mediaType = db.table("MediaType");
    UnTable genre = db.table("Genre");
    UnStatement joinAlbum = db.select(album_title, artist_name).from(album.join(artist, artist.column("ArtistId") == album.column("ArtistId"))).selectable().as("album");
    select = db.select(track.column<QString>("Name"), mediaType.column<QString>("Name"), track.column<QString>("Composer"), track.column<double>("UnitPrice"), joinAlbum.column<QString>("artist"), joinAlbum.column<QString>("album")).from(track.join(mediaType, mediaType.column("MediaTypeId") == track.column("MediaTypeId")).join(genre, genre.column("GenreId") == track.column("GenreId")).join(joinAlbum, joinAlbum.column("album_id") == track.column("AlbumId")));

    QTest::newRow("multiple joins with select AS a table") << stmt << select.expression().toQString();

And for me, it is the base for developing a higher level API (like CRUD). I thought I was reinventing the wheel. It seems that I am, but I didn't found any good SQL writer that complies to the Qt opensource license (LGPL). I don't even know if this project is going to see the light of day ... Time is also my problem :)

Best regards, Richard.