This project is archived and is in readonly mode.

#2631 ✓stale
Andrew Selder

Problem with find with :joins and :include in same call

Reported by Andrew Selder | May 10th, 2009 @ 08:47 PM | in 3.x

In a model I have the following named scope: named_scope :valid, {:include => :broker, :joins => 'INNER JOIN payers ON payers.customer_id = brokers.id AND payers.customer_type = 'Broker'', :conditions => "payers.status = 'active' and ticket_sets.delete_flag = 0 and ticket_sets.qty > 0 and (brokers.ei_id is NULL or (brokers.ei_id is not null and ticket_sets.ei_id is not null)) "}

This worked perfectly in Rails 2.2.2, but in Rails 2.3.2 this blows up.

Looking at the log file, it appears that the :include clause isn't being parsed.

SELECT ticket_sets.* FROM ticket_sets INNER JOIN payers ON payers.customer_id = brokers.id AND payers.customer_type = 'Broker' WHERE (payers.status = 'active' and ticket_sets.delete_flag = 0 and ticket_sets.qty > 0 and (brokers.ei_id is NULL or (brokers.ei_id is not null and ticket_sets.ei_id is not null)) )

If I remove the the joins clause, the brokers get included like they should be, but it doesn't have payers referenced.

Comments and changes to this ticket

  • Frederick Cheung

    Frederick Cheung May 10th, 2009 @ 09:00 PM

    • Assigned user set to “Frederick Cheung”
  • mueller182 (at gmail)

    mueller182 (at gmail) May 15th, 2009 @ 01:36 PM

    Well, the same problem happens with a find using :include and :joins

  • James Le Cuirot

    James Le Cuirot September 13th, 2009 @ 09:49 PM

    Ugh I'm behind on my project, the end finally looks near and then I run into this, with no workaround that I can see. :( Any ideas yet? I'm gonna start digging now.

  • James Le Cuirot

    James Le Cuirot September 14th, 2009 @ 12:11 AM

    I've figured it out. It was caused by the fix for ticket #528. This fix tries to determine which tables you have explicitly joined so that you can force preloading (instead of regular eager loading) if you want to. The problem is that it does a bad job of this since it will also pick up tables named in the join condition. In my case and the case above, this table has to be explicitly named to prevent ambiguity. As far as I can see, it can only be resolved by replacing the somewhat nasty regex with an even nastier one.

    /([.a-zA-Z_]+).?./

    Becomes...

    /\bJOIN\s+["]?([\.a-zA-Z_]+)["]?\s/i

    This could possibly be improved. Here's a patch against 2-3-stable anyway. I may add tests and a patch for master later. Really tired right now. :(

  • Frederick Cheung

    Frederick Cheung September 14th, 2009 @ 10:51 AM

    Does someone familiar with several databases want to comment on whether joins always look like this (seems to me like they do but databases never cease to surprise me)? I seem to recall the some database uses [] to quote table names - i think the existing regexes just use . as a catchall

    Other than that looks fine.

    Separate from this, you should avoid all this if you use the form of joins where you name associations (since then rails doesn't have to parse a blob of sql to work out what is being joined

  • James Le Cuirot

    James Le Cuirot September 14th, 2009 @ 06:47 PM

    I'm using PostgreSQL right now and I used MySQL before but those are the obvious cases. I checked the existing quote column/table methods and only found " and . Most databases don't force you to quote anyway though, right?

    I know you can name the associations but in my case, I was actually joining a sub-select.

  • Frederick Cheung

    Frederick Cheung September 14th, 2009 @ 06:50 PM

    They don't force you but you don't want it to break if you do choose to, which I believe why the other regexps just use . for the quoting character (if my memory of bugs past are correct)

  • James Le Cuirot

    James Le Cuirot September 14th, 2009 @ 07:01 PM

    Sorry, the markup is confusing my comments slightly. I meant " and backticks.

    What I should have said was that just . doesn't work for sub-selects before something like "LEFT OUTER JOIN (SELECT ..." will return SELECT as a table name. I considered [^(] but I think I dismissed it for some reason.

  • Jeremy Kemper

    Jeremy Kemper May 4th, 2010 @ 06:48 PM

    • Milestone changed from 2.x to 3.x
  • Rohit Arondekar

    Rohit Arondekar October 9th, 2010 @ 04:14 AM

    • State changed from “new” to “stale”
    • Importance changed from “” to “”

    Marking ticket as stale. If this is still an issue please leave a comment with suggested changes, creating a patch with tests, rebasing an existing patch or just confirming the issue on a latest release or master/branches.

  • Ryan Bigg

    Ryan Bigg October 9th, 2010 @ 10:05 PM

    • Tag cleared.

    Automatic cleanup of spam.

  • Jeff Kreeftmeijer

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>

Attachments

Pages