Having Issues getting data back from join

[unclemiltyb]

Describe the bug
After adding a join to the find I cannot get the return to return the columns from the join part.

Error stacktrace
There is no error it just returns only the find columns

To reproduce
Create a find with join:

o = [SQLOn( SQLColumn("settlements.id"),SQLColumn("join_settlement_aoi_terrains.settlement_id"))]
j = [SQLJoin(Settlement, columns = [SQLColumn("settlements.name")], o, where = SQLWhereEntity[SQLWhereExpression("join_settlement_aoi_terrains.settlement_id = ?", [1])])]
v = Vector{SQLJoin}(j)
find(Join_settlement_aoi_terrain,SQLQuery(columns = [SQLColumn("id"),SQLColumn("settlement_id"),SQLColumn("terrain_id")]), v)

The return will not include "settlements.name"

[ Info: 2021-10-01 21:51:21 SELECT join_settlement_aoi_terrains.id AS join_settlement_aoi_terrains_id, join_settlement_aoi_terrains.settlement_id AS join_settlement_aoi_terrains_settlement_id, join_settlement_aoi_terrains.terrain_id AS join_settlement_aoi_terrains_terrain_id, settlements.name AS settlements_name FROM join_settlement_aoi_terrains INNER JOIN settlements ON settlements.id = join_settlement_aoi_terrains.settlement_id WHERE join_settlement_aoi_terrains.settlement_id = 1
3-element Vector{Join_settlement_aoi_terrain}:
Join_settlement_aoi_terrain

KEY	VALUE
areaofinterest_id::DbId	0
id::DbId	1
settlement_id::DbId	1
terrain_id::DbId	5

Join_settlement_aoi_terrain

KEY	VALUE
areaofinterest_id::DbId	0
id::DbId	2
settlement_id::DbId	1
terrain_id::DbId	6

Join_settlement_aoi_terrain

KEY	VALUE
areaofinterest_id::DbId	0
id::DbId	3
settlement_id::DbId	1
terrain_id::DbId	5

Expected behavior
output includes settlements.name

Join_settlement_aoi_terrain

KEY	VALUE
areaofinterest_id::DbId	0
id::DbId	3
settlement_id::DbId	1
terrain_id::DbId	5
settlement_name::String	rudibar

Additional context
Please include the output of
julia> versioninfo()
Julia Version 1.6.3
Commit ae8452a9e0 (2021-09-23 17:34 UTC)
Platform Info:
OS: macOS (x86_64-apple-darwin19.5.0)
CPU: Intel(R) Core(TM) i7-4770HQ CPU @ 2.20GHz
WORD_SIZE: 64
LIBM: libopenlibm
LLVM: libLLVM-11.0.1 (ORCJIT, haswell)
Environment:
JULIA_REVISE = auto

and
pkg> st
Project Pwe v0.1.0
Status ~/Projects/verse/Project.toml
[79c8b4cd] AMQPClient v0.4.2
[336ed68f] CSV v0.8.5
[a93c6f00] DataFrames v1.2.2
[c43c736e] Genie v3.0.0
[e115e502] GenieAuthentication v1.0.1
[682c06a0] JSON v0.21.2
[e6f89c97] LoggingExtras v0.4.7
[739be429] MbedTLS v1.0.3
[340e8cb6] SearchLight v1.0.2
[1297d576] SearchLightMySQL v1.0.0
[ade2ca70] Dates
[56ddb016] Logging

[essenciary]

@unclemiltyb Thanks for reporting this and apologies for the late follow up.
What is Join_settlement_aoi_terrain ?

[essenciary]

For complex scenarios and failures like this, you can use DataFrames.DataFrame(Join_settlement_aoi_terrain,SQLQuery(columns = [SQLColumn("id"),SQLColumn("settlement_id"),SQLColumn("terrain_id")]), v) to retrieve the result as DataFrame (make sure to add DataFrames to your app)

[jonathanBieler]

Seems to be a design issue rather than a bug ; find(m::T, q, j) returns a vector of T<:AbstractModel, but when doing a join the output doesn't fit in the model T, so that won't ever work.

join could return a tuple of (T[], joindata) when you have a join, or a more flexible output for that case (NamedTuple would do the job). E.g.

function find(m::Type{T}, q::SQLQuery)::Vector{T} where {T<:AbstractModel}
  to_models(m, DataFrame(m, q, j))
end

function find(m::Type{T}, q::SQLQuery, j::Vector{SQLJoin}) where {T<:AbstractModel}
     data = DataFrame(m, q, j)
    [copy(r) for r in eachrow(data)]
end

Although the issue with this design is that the names change compared to normal find, so views need to be changed.