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.