Complex query with multiple joins

[Linus Pettersson]

Hi!

I have an app that manages products. I import the products from several resellers and they all name their categories different. Because of this I have resellercategories that are mapped to my own subcategories.

Categories

- Subcategories (belongs_to Category)

Resellercategories (belongs_to Subcategory)

Products (belongs_to Resellercategory)

Now I want to show the categories in a special way. If the user filters products by gender, let's say 'female', then I only want to show the categories and subcategories which have products for females. This is where I'm stuck!

I created a query like this that actually gets the correct "Categories". But it doesn't seem restrict the subcategories.

The query: http://snipt.net/Linuus/categories-1?key=2d5d54fd573f0afe60eaa3c47a23fd4d

Any ideas?

Regards

Linus

[Linus Pettersson]

Man, I'm beating my head against this one...

The query above is probably not the best. Any other suggestions?

Regards

Linus

[Peter Vandenabeele]

How did you actually _retrieve_ the subcategories.

By using the "joins" there, there is a chance your query only retrieves the categories

(as in "SELECT "categories".* FROM "categories" INNER JOIN "subcategories" ...).

So maybe you have a second query for the subcategories (that is not correctly filtered then)

Maybe you do `category.subcategories`

@menu_categories = Category.joins(:subcategories)

                          .joins("INNER JOIN resellercategories AS r ON subcategories.id = r.subcategory_id")

                          .joins("INNER JOIN products AS p ON r.id = p.resellercategory_id")

                          .group("categories.id")

                          .order("categories.name ASC")

                          .where("p.gender = 'unisex' OR p.gender = ?", session[:gender])

@menu_categories.each do |mc|

  mc.subcategories

  ..

end

This `mc.subcategories` executes a new query that is not taking into account the filtering.

So, you are doing 1+n queries (and the n queries are not what you expect).

Maybe you need

@menu_categories = Category.includes(:subcategories)  # INCLUDES here

                          .joins("INNER JOIN resellercategories AS r ON subcategories.id = r.subcategory_id")

                          .joins("INNER JOIN products AS p ON r.id = p.resellercategory_id")

                          .group("categories.id")

                          .order("categories.name ASC")

                          .where("p.gender = 'unisex' OR p.gender = ?", session[:gender])

That will get the subcategories from the db in 1 (more complex) query with results that are

filtered. If you then do

@menu_categories.each do |mc|

  mc.subcategories # this should not trigger new SQL queries

  ..

end

Do this rails console and read the SQL carefully (or look in your development log

carefully which SQL is executed).

HTH,

Peter

--
Peter Vandenabeele
http://twitter.com/peter_v

http://rails.vandenabeele.com

[Max Schubert]

With any ORM there will be instances where pure object relationships
make for either very complex code or code that does not scale well
performance-wise as the data set involved grows when compared to the
efficiency of the underlying database's SQL engine.

In those circumstances remember that views and stored procedures are
available - schema_plus is a gem that let's you define views as a part
of your schema.r b file and while stored procedures violate the
ActiveRecord design pattern they can be used with AR and are sometimes
necessary in order to create a system that scales and performs well.

Max

> --
> You received this message because you are subscribed to the Google Groups
> "Ruby on Rails: Talk" group.
> To post to this group, send email to rubyonra...@googlegroups.com.
> To unsubscribe from this group, send email to
> rubyonrails-ta...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/rubyonrails-talk?hl=en.
>
>

[Linus Pettersson]

Thank you Peter. I also found the related section in the rails guides about this:
http://guides.rubyonrails.org/active_record_querying.html#eager-loading-associations

However, I haven't gotten it to work just yet. The above example you wrote doesn't work because that subcategories.id is not present when I try to join the next table:

@menu_categories = Category.includes(:subcategories)  # INCLUDES here

                          .joins("INNER JOIN resellercategories AS r ON subcategories.id = r.subcategory_id") # no such column: subcategories.id

[Linus Pettersson]

Also, just to be clear. You are totally right that I just do "mc.subcategories.each"...

This is what the view looks like:

http://snipt.net/Linuus/categories-view-1?key=8f1be321f6253bd74847066a719490ce

Regards

Linus

[Peter Vandenabeele]

Well, if you look at the generated SQL, there must be

LEFT OUTER JOIN "subcategories" ... 

is there an "AS xyz" then you need to use that xyz.

You say you have the "belongs_to" relationships. Did you actually write the has_many relationships ?

Maybe show us some of the generated SQL in the log/development.log or in the rails console.

HTH,

Peter

[Linus Pettersson]

(I wrote an answer before but it seems to not have been published so here we go again :) )

No, there is no LEFT OUTER JOIN subcategories... I guess this is because I don't have any condition on the subcategories. Then it will execute two queries instead of using the LEFT OUTER JOIN.

I just ran this:

Category.includes(:subcategories)

             .joins("INNER JOIN resellercategories AS r ON subcategories.id = r.subcategory_id")

             .joins("INNER JOIN products AS p ON r.id = p.resellercategory_id")
             .group("categories.id")
             .order("categories.name ASC")

             .where("p.gender = 'unisex' OR p.gender = 'male'")

And it generated this SQL query:

"SELECT "categories".* FROM "categories" INNER JOIN resellercategories AS r ON subcategories.id = 

r.subcategory_id INNER JOIN products AS p ON r.id = p.resellercategory_id WHERE (p.gender = 'unisex' OR p.gender = 'male')

 GROUP BY categories.id ORDER BY categories.name ASC"

[Peter Vandenabeele]

You are correct ... I just retested here and the single query is only triggered when

there is a condition on the associated table in the :includes

So, just to test that hypothesis ... what happens if you add add a fake condition on

the subcategories.

Category.includes(:subcategories)

             .joins("INNER JOIN resellercategories AS r ON subcategories.id = r.subcategory_id")

             .joins("INNER JOIN products AS p ON r.id = p.resellercategory_id")

             .group("categories.id")

             .order("categories.name ASC")

             .where("p.gender = 'unisex' OR p.gender = 'male'")

             .where("subcategories.id > 0") # ADDED A FAKE CONDITION

[Linus Pettersson]

Still doesn't work :(

Generated SQL:

" SELECT "categories".* FROM "categories" INNER JOIN resellercategories AS r ON subcategories.id = 

r.subcategory_id INNER JOIN products AS p ON r.id = p.resellercategory_id WHERE (p.gender = 'unisex' OR p.gender = 'male')

 AND (subcategories.id > 0) GROUP BY categories.id ORDER BY categories.name ASC"

Error:

"ActiveRecord::StatementInvalid: SQLite3::SQLException: no such column: subcategories.id: SELECT "categories".* FROM "categ

ories" INNER JOIN resellercategories AS r ON subcategories.id = r.subcategory_id INNER JOIN products AS p ON r.id = p.rese

llercategory_id WHERE (p.gender = 'unisex' OR p.gender = 'male') AND (subcategories.id > 0) GROUP BY categories.id ORDER B

Y categories.name ASC"

[Colin Law]

Can you show us the class definitions for Category and Subcategory
(snip the methods).

Colin

[Linus Pettersson]

Hi Colin!

Absolutely. You mean for the models?

Here they are:

http://snipt.net/Linuus/category-and-subcategory?key=38ba590408ac4233927a06046eeca30d

Best Regards

Linus

[Colin Law]

On 6 January 2012 20:57, Linus Pettersson <linus.pe...@gmail.com> wrote:
> Hi Colin!
>
> Absolutely. You mean for the models?
>
> Here they are:
> http://snipt.net/Linuus/category-and-subcategory?key=38ba590408ac4233927a06046eeca30d

That looks ok.
I think the group and order should be category.id and .name rather
than categories but I don't see how that would cause the problem you
are seeing.

Colin

[Peter Vandenabeele]

OK, let's try over and keep it as simple as possible.

Rails 3.1.3.

This is a rails console session that reproduces the original problem and

shows the fix. Maybe Linus can try to reproduce this exactly and then

add more complexity until it breaks.

These are my models:

 peterv@ASUS:~/data/backed_up/rails-apps/apps/base_app/app/models$ cat user.rb 

class User < ActiveRecord::Base

  belongs_to :account

end

peterv@ASUS:~/data/backed_up/rails-apps/apps/base_app/app/models$ cat account.rb 

class Account < ActiveRecord::Base

  has_many :users

end

The data is:

Account 1

=> user 1 "Peter"

=> user 2 "Sarah" (one of my kids)

Account 2

> user 3 "Thomas"

This is the console with my comments:

$ rails console

...

016:0> a1 = Account.joins(:users).where("users.name LIKE 'peter'")

  Account Load (1.0ms)  SELECT "accounts".* FROM "accounts" INNER JOIN "users" ON "users"."account_id" = "accounts"."id" WHERE (users.name LIKE 'peter')

=> [#<Account id: 1, number: "123", created_at: "2011-12-01 22:24:16", updated_at: "2011-12-01 22:24:16">]

This is the Relation with JOINS. It only loads the first SQL query and only

SELECTs the accounts (not the users). We find the correct Account 1 with

an associated user "Peter".

017:0> a1.size

=> 1

There is 1 account found.

The "first" below is to take that 1 account from the Relation

018:0> a1.first.users

  User Load (0.8ms)  SELECT "users".* FROM "users" WHERE "users"."account_id" = 1

=> [#<User id: 1, name: "peter", account_id: 1, created_at: "2011-12-01 22:24:16", updated_at: "2011-12-01 22:24:16">, #<User id: 3, name: "Sarah", account_id: 1, created_at: "2012-01-06 21:23:22", updated_at: "2012-01-06 21:23:22">]

Only now we do the second SQL query for the users from account 1.

NOT filtered on users.name, so we find ALL users for account 1:

* Peter (OK)

* Sarah (not OK)

that was your original problem.

019:0> a1 = Account.includes(:users).where("users.name LIKE 'peter'")

  SQL (1.1ms)  SELECT "accounts"."id" AS t0_r0, "accounts"."number" AS t0_r1, "accounts"."created_at" AS t0_r2, "accounts"."updated_at" AS t0_r3, "users"."id" AS t1_r0, "users"."name" AS t1_r1, "users"."account_id" AS t1_r2, "users"."created_at" AS t1_r3, "users"."updated_at" AS t1_r4 FROM "accounts" LEFT OUTER JOIN "users" ON "users"."account_id" = "accounts"."id" WHERE (users.name LIKE 'peter')

=> [#<Account id: 1, number: "123", created_at: "2011-12-01 22:24:16", updated_at: "2011-12-01 22:24:16">]

Now I get the "single" , "complex" query that loads the accounts columns and the associated

users columns in 1 query; and now only the users are included that also match the filter

conditions (name LIKE 'peter').

020:0> a1.size

=> 1

Again, 1 account is found

021:0> a1.first.users

=> [#<User id: 1, name: "peter", account_id: 1, created_at: "2011-12-01 22:24:16", updated_at: "2011-12-01 22:24:16">]

But now the users are not loaded from a second query but are already in memory from the first

"complex" query.

Could you try to literally reproduce this behavior, so we have a common ground to start ?

Or maybe you can simplify your code to the point of only having categories and subcategories

in the query? That should work ...

These are the migrations:

peterv@ASUS:~/data/backed_up/rails-apps/apps/base_app/db/migrate$ cat *

class CreateAccounts < ActiveRecord::Migration

  def change

    create_table :accounts do |t|

      t.string :number

      t.timestamps

    end

  end

end

class CreateUsers < ActiveRecord::Migration

  def change

    create_table :users do |t|

      t.string :name

      t.integer :account_id

      t.timestamps

    end

  end

end

HTH,

Peter

[Linus Pettersson]

Wow! Thank you for all the help Peter! I really appreciate it. I will test that code tomorrow as it's getting quite late here.

I did a small test now though. It seems that you can't mix joins and includes.

This doesn't work:

Category.includes(:subcategories).joins("INNER JOIN resellercategories AS r ON subcategories.id = r.s

ubcategory_id").where("subcategories.id > 0")

"SELECT "categories".* FROM "categories" INNER JOIN resellercategories AS r ON subcategories.id = 

r.subcategory_id WHERE (subcategories.id > 0)

ActiveRecord::StatementInvalid: SQLite3::SQLException: no such column: subcategories.id: SELECT "categories".* FROM "categ

ories" INNER JOIN resellercategories AS r ON subcategories.id = r.subcategory_id WHERE (subcategories.id > 0)"

But this do work:

Category.includes(:subcategories).where("subcategories.id > 0")

"SELECT "categories"."id" AS t0_r0, "categories"."name" AS t0_r1, "categories"."created_at" AS t0_r2, "categ

ories"."updated_at" AS t0_r3, "categories"."permalink" AS t0_r4, "subcategories"."id" AS t1_r0, "subcategories"."name" AS 

t1_r1, "subcategories"."category_id" AS t1_r2, "subcategories"."created_at" AS t1_r3, "subcategories"."updated_at" AS t1_r

4, "subcategories"."permalink" AS t1_r5 FROM "categories" LEFT OUTER JOIN "subcategories" ON "subcategories"."category_id"

 = "categories"."id" WHERE (subcategories.id > 0)"

Quite odd I think...

Regards

Linus

[Peter Vandenabeele]

On Sat, Jan 7, 2012 at 12:20 AM, Linus Pettersson <linus.pe...@gmail.com> wrote:

Wow! Thank you for all the help Peter! I really appreciate it.

No prob, thank you for getting closer to the root cause.

 

I will test that code tomorrow as it's getting quite late here.

I did a small test now though. It seems that you can't mix joins and includes.

Indeed, strange ...

 

This doesn't work:

Category.includes(:subcategories).joins("INNER JOIN resellercategories AS r ON subcategories.id = r.s

ubcategory_id").where("subcategories.id > 0")

"SELECT "categories".* FROM "categories" INNER JOIN resellercategories AS r ON subcategories.id = 

r.subcategory_id WHERE (subcategories.id > 0)

ActiveRecord::StatementInvalid: SQLite3::SQLException: no such column: subcategories.id: SELECT "categories".* FROM "categ

ories" INNER JOIN resellercategories AS r ON subcategories.id = r.subcategory_id WHERE (subcategories.id > 0)"

But this do work:

Category.includes(:subcategories).where("subcategories.id > 0")

"SELECT "categories"."id" AS t0_r0, "categories"."name" AS t0_r1, "categories"."created_at" AS t0_r2, "categ

ories"."updated_at" AS t0_r3, "categories"."permalink" AS t0_r4, "subcategories"."id" AS t1_r0, "subcategories"."name" AS 

t1_r1, "subcategories"."category_id" AS t1_r2, "subcategories"."created_at" AS t1_r3, "subcategories"."updated_at" AS t1_r

4, "subcategories"."permalink" AS t1_r5 FROM "categories" LEFT OUTER JOIN "subcategories" ON "subcategories"."category_id"

 = "categories"."id" WHERE (subcategories.id > 0)"

Indeed. I added a Project class with User has_many :projects to my test project and can confirm:

010:0> a = Account.includes(:users).where("users.name like 'peter'")

  SQL (1.1ms)  SELECT "accounts"."id" AS t0_r0, "accounts"."number" AS t0_r1, "accounts"."created_at" AS t0_r2, "accounts"."updated_at" AS t0_r3, "users"."id" AS t1_r0, "users"."name" AS t1_r1, "users"."account_id" AS t1_r2, "users"."created_at" AS t1_r3, "users"."updated_at" AS t1_r4 FROM "accounts" LEFT OUTER JOIN "users" ON "users"."account_id" = "accounts"."id" WHERE (users.name like 'peter')

=> [#<Account id: 1, number: "123", created_at: "2011-12-01 22:24:16", updated_at: "2011-12-01 22:24:16">]

011:0> a1 = a.first

=> #<Account id: 1, number: "123", created_at: "2011-12-01 22:24:16", updated_at: "2011-12-01 22:24:16">

012:0> a1.users.first

=> #<User id: 1, name: "peter", account_id: 1, created_at: "2011-12-01 22:24:16", updated_at: "2011-12-01 22:24:16">

This works as expected. 1 large query for accounts and associated users.

013:0> a1.users.first.projects

  Project Load (1.0ms)  SELECT "projects".* FROM "projects" WHERE "projects"."user_id" = 1

=> [#<Project id: 1, name: "project_of_peter", user_id: 1, created_at: "2012-01-07 10:51:51", updated_at: "2012-01-07 10:51:51">]

Also as expected. Since "projects" was not joined or included in the original query,

we need a second query here (which is then not filtered and thus not the results we want).

014:0> a = Account.includes(:users).where("users.name like 'peter'").joins(:users => :projects)

  Account Load (1.3ms)  SELECT "accounts".* FROM "accounts" INNER JOIN "users" ON "users"."account_id" = "accounts"."id" INNER JOIN "projects" ON "projects"."user_id" = "users"."id" WHERE (users.name like 'peter')

  User Load (0.9ms)  SELECT "users".* FROM "users" WHERE "users"."account_id" IN (1)

=> [#<Account id: 1, number: "123", created_at: "2011-12-01 22:24:16", updated_at: "2011-12-01 22:24:16">]

This is the getting closer to the root cause as you discovered now. As soon as there is a "joins" added,

the :includes fails to Eagerly load only the _filtered_ users in a single complex query, but goes to a second

query (that is not filtered on users; that would be "not filtered on subcategories" in your code). 

015:0> a = Account.includes(:users).where("users.name like 'peter'").includes(:users => :projects)

  SQL (1.5ms)  SELECT "accounts"."id" AS t0_r0, "accounts"."number" AS t0_r1, "accounts"."created_at" AS t0_r2, "accounts"."updated_at" AS t0_r3, "users"."id" AS t1_r0, "users"."name" AS t1_r1, "users"."account_id" AS t1_r2, "users"."created_at" AS t1_r3, "users"."updated_at" AS t1_r4, "projects"."id" AS t2_r0, "projects"."name" AS t2_r1, "projects"."user_id" AS t2_r2, "projects"."created_at" AS t2_r3, "projects"."updated_at" AS t2_r4 FROM "accounts" LEFT OUTER JOIN "users" ON "users"."account_id" = "accounts"."id" LEFT OUTER JOIN "projects" ON "projects"."user_id" = "users"."id" WHERE (users.name like 'peter')

=> [#<Account id: 1, number: "123", created_at: "2011-12-01 22:24:16", updated_at: "2011-12-01 22:24:16">]

016:0> a.size

=> 1

017:0> a.first.users

=> [#<User id: 1, name: "peter", account_id: 1, created_at: "2011-12-01 22:24:16", updated_at: "2011-12-01 22:24:16">]

018:0> a.first.users.first.projects

=> [#<Project id: 1, name: "project_of_peter", user_id: 1, created_at: "2012-01-07 10:51:51", updated_at: "2012-01-07 10:51:51">]

So, using :includes everywhere seems to not trigger this problem.

I don't know if that is an acceptable fix for your project (depending on much

unneeded data that loads for you)?

I don't know if this is a "bug" or just misunderstanding the behavior of :joins

and :includes in Eager loading from our side?

If I assume the latter (us not understanding it well enough), then conclusions

could be:

* :includes is just a convenience for eager loading (performance improvement)

  and not designed to work together with joins in the way we expected

* trying to use has_many relationships with conditions may be a solution ...

First added a "lang" column to projects and trying a condition on the projects table

(fails in the same way):

012:0> a = Account.includes(:users).joins(:users => :projects).where(['projects.lang = ?', 'ruby'])

  Account Load (1.2ms)  SELECT "accounts".* FROM "accounts" INNER JOIN "users" ON "users"."account_id" = "accounts"."id" INNER JOIN "projects" ON "projects"."user_id" = "users"."id" WHERE (projects.lang = 'ruby')

  User Load (0.5ms)  SELECT "users".* FROM "users" WHERE "users"."account_id" IN (1)

=> [#<Account id: 1, number: "123", created_at: "2011-12-01 22:24:16", updated_at: "2011-12-01 22:24:16">]

This is still correct. Only 1 account with id 1 has a user (peter) with a project with lang "ruby".

013:0> a1 = a.first

=> #<Account id: 1, number: "123", created_at: "2011-12-01 22:24:16", updated_at: "2011-12-01 22:24:16">

Getting that first (and only) account from the array.

014:0> a1.users

=> [#<User id: 1, name: "peter", account_id: 1, created_at: "2011-12-01 22:24:16", updated_at: "2012-01-07 11:28:20", gender: "M">, #<User id: 3, name: "Sarah", account_id: 1, created_at: "2012-01-06 21:23:22", updated_at: "2012-01-07 11:28:24", gender: "F">]

But this is not what we want. We get all users from account 1, not just the users with a "ruby" project.

015:0> a1.users.first.projects

  Project Load (0.9ms)  SELECT "projects".* FROM "projects" WHERE "projects"."user_id" = 1

=> [#<Project id: 1, name: "project_of_peter", user_id: 1, created_at: "2012-01-07 10:51:51", updated_at: "2012-01-07 11:38:15", lang: "ruby">, #<Project id: 2, name: "coffeescript_project", user_id: 1, created_at: "2012-01-07 11:39:10", updated_at: "2012-01-07 11:39:10", lang: "coffee">]

And similar problem, for user 'peter' we get all projects, also the ones in a different language.

016:0> a1.users[1].projects

  Project Load (0.8ms)  SELECT "projects".* FROM "projects" WHERE "projects"."user_id" = 3

=> []

And user 'sarah' has not projects at all, but still in the list of users.

OK, trying another solution ...

class Account < ActiveRecord::Base

  has_many :users

  attr_accessor :lang

  has_many :users_by_project_lang,

           :class_name => "User",

           :include => :projects,

           :conditions => Proc.new { ["projects.lang = ?", lang] }

end

I add a special has_many relationship on the Account class

(that would be your Category class). Now using that to

find the "users" LIMITED to a certain project_lang

(in your case that would be subcategories, limited to a certain

product.gender).

018:0> a1.lang='ruby'  

=> "ruby"

The conditions will be late evaluated against the "self" (that is a1 here). So

we need to set it on a1.

019:0> a1.users_by_project_lang

  SQL (1.5ms)  SELECT "users"."id" AS t0_r0, "users"."name" AS t0_r1, "users"."account_id" AS t0_r2, "users"."created_at" AS t0_r3, "users"."updated_at" AS t0_r4, "users"."gender" AS t0_r5, "projects"."id" AS t1_r0, "projects"."name" AS t1_r1, "projects"."user_id" AS t1_r2, "projects"."created_at" AS t1_r3, "projects"."updated_at" AS t1_r4, "projects"."lang" AS t1_r5 FROM "users" LEFT OUTER JOIN "projects" ON "projects"."user_id" = "users"."id" WHERE "users"."account_id" = 1 AND (projects.lang = 'ruby')

=> [#<User id: 1, name: "peter", account_id: 1, created_at: "2011-12-01 22:24:16", updated_at: "2012-01-07 11:28:20", gender: "M">]

Now we get back our "complex" query, filtered on a parameter of the the joined products table.

021:0> a1.users_by_project_lang.each{|user| puts "user = #{user} with projects #{user.projects}"}

user = #<User:0x94cd7c8> with projects [#<Project id: 1, name: "project_of_peter", user_id: 1, created_at: "2012-01-07 10:51:51", updated_at: "2012-01-07 11:38:15", lang: "ruby">]

=> [#<User id: 1, name: "peter", account_id: 1, created_at: "2011-12-01 22:24:16", updated_at: "2012-01-07 11:28:20", gender: "M">]

And now, only the correct users ('peter') with the filtered projects (with lang 'ruby') are returned.

So, I presume, if you make a similar has_many in Category

has_many :subcategories_by_product_gender

this would work.

But ... since the has_many only has a :include (and not a :joins) options, this seems

equivalent to the first option of using all :includes for the entire chain (and

no :joins). So, in reality, it does not seem to be better than that first option

(which has the disavantage of loading all the associated info, even if you

so not need it).

An alternative might be to make a dedicated has_many and use :finder_sql

to hand-code the SQL ...

http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html

":finder_sql

Specify a complete SQL statement to fetch the association. This is a good way

to go for complex associations that depend on multiple tables."

It seems this feature request seems complex enough to warrant that.

I would certainly be interested in a "proper" solution for this issue (using a

mix of :includes and :joins, where the :includes are eagerly loaded in

the SELECT part in the first query (so these results are filtered) and the

:joins are only used to eveluate conditions or ordering, etc. but not have

to be included in the SELECT part). 

I looked into scopes, but I failed to find a proper way to apply a scope to

a has_many relationship ... (maybe with_scope {}, but that seems to be

depricated ?). 

HTH,

Peter

[Linus Pettersson]

Hi again!

I posted a question about this issue on StackOverflow and got an interesting response. To force Rails to join the included table you can use eager_load() instead of includes(). Using eager_load() and removing the group() seems to make my query work perfect.

Correct version:

Category.eager_load(:subcategories)
             .joins("INNER JOIN resellercategories AS r ON subcategories.id = r.subcategory_id")
             .joins("INNER JOIN products AS p ON r.id = p.resellercategory_id")
             .order("categories.name ASC")
             .where("p.gender = 'unisex' OR p.gender = 'male'")

Here's the discussion at SO:
http://stackoverflow.com/questions/8771304/complex-query-use-includes-and-joins-at-the-same-time

Thanks for all the help!

Best Regards
Linus

[Peter Vandenabeele]

On Sat, Jan 7, 2012 at 7:07 PM, Linus Pettersson <linus.pe...@gmail.com> wrote:

Hi again!

I posted a question about this issue on StackOverflow and got an interesting response. To force Rails to join the included table you can use eager_load() instead of includes(). Using eager_load() and removing the group() seems to make my query work perfect.

Thanks, good to know.

It seems not extensively documented ...

Google ("ActiveRecord eager_load") results:

1. http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html

no match for eager_load

2. http://guides.rubyonrails.org/active_record_querying.html

no match for eager_load

3. http://apidock.com/rails/v3.1.0/ActiveRecord/QueryMethods/eager_load

a match :-) => This method has no description. You can help the Ruby on Rails community by adding new notes.

etc ...

I will see if I can contribute some doc for this,

Peter