On Wednesday, May 23, 2012 9:47:33 AM UTC-7, PatrickLef wrote:
>
> Hi,
>
> I understand the problem. Do you think I can solve this in my application
> code without changing the db structure an without getting temp table?
>
> It's a quite large application and this is becoming a quite big problem
> when we are fetching bloggers based on info in the blog table.
>
> Would be great if you have any tips!
>
If you really need the non-subselect query to work around MySQL's lousy
optimizer, you'll probably have to patch Sequel's Dataset#graph method not
to use the subselect and to handle the column aliasing differently. My
recommendation would be adding a method that, before adding any graph, you
could call with the initial column aliases:
Blogger.set_initial_graph_aliases(:user_id=>:users,
:blogger_custom_field=>:bloggers).eager_graph(:blogs)
This method would return a clone of the dataset with the necessary metadata
set. When initiating a graph, if this metadata has been set, it won't use
a subselect automatically, and will use the metadata instead of setting up
the initial graph aliases using the default code. One tricky area is that
you'll need to be able to specify that the blogger_custom_field is aliased
to bloggers in the SQL, but still belongs to users when retrieving the
graph.
Alternatively, you could just not use eager graphing for this. You could
just use your custom SQL, and either not use associations at all, or setup
the associations manually. That may be easier. Blogger.one_to_one :blog,
so you shouldn't need to worry about combining multiple output rows into a
single object. You should just be able to do:
bloggers = Blogger.with_sql(your_sql).map do |blogger|
h = {}
{:blog_id=>:blog_id, :blog_user_id=>:user_id,
:blog_custom_field=>:blog_custom_field}.each do |k, v|
h[v] = blogger.values.delete(k)
end
blogger.associations[:blog] = Blog.call(h)
blogger
end
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sequel-talk/-/6o5uVS5ZUlIJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
