outer join issue

[smatyas]

Hi, I have to use outer join, group_concat and group by in my query, and I think that this patch does not work with that currently.

My query is something like:

post = Post.includes(:comments).references(:tags)
    .select("posts.*, group_concat(distinct tags.name) as tags")
    .where( SOME_CONDITION )
    .group_by('posts.id')

I tried to use this gem, but I still get undefined method for "post.tags"

Could you please check this case if it's a valid issue? Thanks!

[alekseyl]

OK. I'll look to it. What version of rails/gem you use?

[smatyas]

I use activerecord ~> 4.2.7.1 and rails_select_on_includes ~> 0.4.3
Thanks for looking into it! :)

[alekseyl]

Meanwhile you may try latest '0.4.8'. I'm not sure it solves your problem but it surely fixes the issue with right attributes types.

Also it would be nice to see output of Post.includes(:comments).references(:tags)
.select("posts.*, group_concat(distinct tags.name) as tags")
.where( SOME_CONDITION )
.group_by('posts.id').TO_SQL

[smatyas]

OK, I just switched to the latest 0.4.9, that didn't solve my problem sadly.
Here is the generated sanitized SQL:

SELECT table0.*,
       group_concat(DISTINCT table1.name) AS tags,
       "table0"."id" AS t0_r0,
       "table0"."name" AS t0_r1,
       "table1"."id" AS t1_r0,
       "table1"."name" AS t1_r1
FROM "table0"
LEFT OUTER JOIN "table1" ON "table1"."t0_id" = "table0"."id"
GROUP BY table0.id

So as you can see, it actually doesn't even have a where part.

[alekseyl]

mmm, can you name it not tags, but tags_names? may be it conflicts with tags somehow?

[smatyas]

Tried that, didn't help.

[alekseyl]

Can you please give me relation structure between comments, tags and Post.
I can't reproduce such behavior with relation like such:

class Post < ..
has_many comments
has_many tags
end

I recreate much more complicated request with eager loading and outer join in different combinations it works well.

Does any of selected post has at least one tag? Cause it's not the INNER JOIN or OUTER JOIN behavior messing with result.

Is it MySQL special behavior? ( cause I'm using postgres and it works fine with string aggregation )

Can you please output: Post.connection.execute( Post.includes(:comments).references(:tags)
.select("posts.*, group_concat(distinct tags.name) as tags")
.where( SOME_CONDITION ).limit(1).
.group_by('posts.id').to_sql )[0]

[alekseyl]

Did you play with raw DB output?

[smatyas]

Hi, i didn't have time and sadly i won't have either this week. I was thinking that i just really misconfigured something very badly, so it could be a user error eventually. I'll get back to this issue next week hopefully, please don't put any more of your energy into this until, as it said, it might be my fault...

[smatyas]

Hi! I finally had time to sort out this issue. It turned out that it was my fault and your patch works perfectly in this case as well! :)
Thank you four your work! 👍