Composing relations through many-to-many join about rom-sql HOT 18 CLOSED

@mrship it should work if you just do combine_children(many: charts) because it looks if there's a for_#{ds_name} view defined and calls it with needed args automatically.

from rom-sql.

That looks really nice. Thanks for following up. I hope to try 1.0.0 when it comes out of beta.

from rom-sql.

In rom-sql 1.0.0 you will be able to extend an association's relation with your own query logic, but for now you can define a custom view. To make it simpler for you, you can re-use association relation:

class Charts < ROM::Relation[:sql]
  schema(infer: true) do
    associations do
      has_many :questions_charts
      has_many :questions, through: :questions_charts
    end
  end

  view(:for_questions, %[list of attributes in resulting relation]) do |questions|
    for_combine(associations[:questions])
      .where(questions__id: questions.pluck(:id))
      .order(:questions__that_column_you_want_to_use)
  end
end

Combine will automatically use this view by convention, so it should just work.

It'll be much simpler in 1.0.0 though. I'm releasing first beta in the upcoming days so stay tuned :)

from rom-sql.

ps. just updated the example to be a bit nicer

from rom-sql.

Thanks for the help; I probably confused things a little using order as my example attribute, but that is actually a column on the questions_tables join table, so I want to be able to select all the columns from chart as well as the order column from the join table.

I'll see if I can figure that out from your example, but I'm still not 100% sure how to use the join_table as part of the for_combine.

from rom-sql.

@mrship the association used under the hood joins that table for you automatically, so you can just do select_append(:questions_tables__some_col_1, :questions_tables__some_col_2).

ps. this will be so much nicer in rom-sql 1.0.0 :)

from rom-sql.

Thanks for the help so far and apologies for the delay in coming back to you but I'm only just coming back around to this.

When I'm looking at the custom view that you wrote above, it appears to be selecting the question attributes, not chart attributes; e.g: if I strip this back to make Charts#for_questions just replicate the existing association (before adding in any other attributes)

      view(:for_questions, %i(id type created_at updated_at snapshot insight)) do |questions|
        q = for_combine(associations[:questions])
          .where(questions__id: questions.pluck(:id))
        require 'pry'; binding.pry
        q
      end

q (above) produces this relation SQL:

 "SELECT `questions`.`id`, `questions`.`topic_id`, `questions`.`title`, `questions`.`short_title`, `questions`.`methodology`, `questions`.`order`, `questions`.`created_at`, `questions`.`updated_at`, `chart_id` FROM `questions` INNER JOIN `questions_charts` ON (`questions_charts`.`question_id` = `questions`.`id`) INNER JOIN `charts` ON (`charts`.`id` = `questions_charts`.`chart_id`) ORDER BY `questions`.`id`"

implemented like that, I then end up with this error:

     ArgumentError:
       Relations::Questions#preload arity is 3 (2 args given)

when calling the combine_children (note I also have to call the view directly as it doesn't work automatically as you implied)

 questions
          .combine_children(
            many: {
              charts: charts.for_questions,
            }
          )

So, sorry the news isn't better but I think I'm missing something fundamental here.

from rom-sql.

I realised that I should have the custom view on questions, not charts, so I'm closer with this view on questions

      view(:for_charts, %i(id type created_at updated_at question_id order)) do |questions|
        for_combine(associations[:charts])
            .where(questions__id: questions.pluck(:id))
            .select_append(:questions_charts__order)
      end

but unfortunately I'm still getting:

ArgumentError:
       Relations::Questions#preload arity is 3 (2 args given)

when the view is called. The SQL it generates looks correct though, so that is definite (exciting!) progress.

Any thoughts on the arity error?

from rom-sql.

Unfortunately, the view doesn't get called automatically if I combine_children(many: charts) only if I am explicit about it with combine_children(many: questions.for_charts).

from rom-sql.

@mrship oh right I forgot that it needs keys to passed explicitly, damn :) see docs for #combine where it's explained

from rom-sql.

Ok, you mean something like, this?

questions.combine(many: { charts: [questions.for_charts, id: :chart_id] })

unfortunately, that gives a similar error but on charts.

    ArgumentError:
       Relations::Charts#preload arity is 3 (2 args given)

Apologies if I'm being dense here; I feel like I'm so close.

from rom-sql.

I thought you wanted questions.combine(many: { charts: [charts.for_questions, id: question_id])?

from rom-sql.

I've created a gist - https://gist.github.com/mrship/3f59b5ab6c40e7cacc0a03eba92742d0 - that shows the problem and hopefully makes it a little clearer what is happening.

The ultimate aim is to have a Question with many Charts, and the Charts have their order from the join table.

If you run the gist, you'll get the same arity issue that I reported above.

Thanks again for the extensive help.

from rom-sql.

from rom-sql.

@mrship pheeew, there we go: https://gist.github.com/46584d53fb1f6901b3d29381cec1ccd1

from rom-sql.

Wow! That works a treat. There's no way I'd ever have got to that point without your help though, so (a) thanks and (b) I'm looking forward to seeing how that becomes easier in v1.0.0!

The good news is that this approach is going to get used a lot throughout my current project, and simplify a lot of features, so thanks again for the help.

from rom-sql.

@mrship cool! I'll let you know once there's a nicer way, in 1.0.0.beta there's already support for defining associations extended with custom views (ie has_many :questions, view: :ordered) and the only missing part is having ability to extend views's schema with attributes from other relations (this order col in your case).

from rom-sql.

@mrship hey man, I added a couple of features to make this easier for you. Here's an updated gist: https://gist.github.com/solnic/46584d53fb1f6901b3d29381cec1ccd1

This will be available in rom 3.0.0 and rom-sql 1.0.0

from rom-sql.