#25 ✓resolved
nikwest

nested order_by_link fails with postgres

Reported by nikwest | November 13th, 2008 @ 09:14 AM

Asume a n;1 relationship from movie to supplier

Using

order_by_link :supplier => :name

on movie results in an postgres error:

ActiveRecord::ActiveRecordError: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list: SELECT DISTINCT "movies".* FROM "movies" LEFT OUTER JOIN "suppliers" ON "suppliers".id = "movies".supplier_id ORDER BY "suppliers"."name" ASC LIMIT 25

I think the query should look something like this:

SELECT DISTINCT "suppliers"."name", "movies".* FROM "movies" LEFT OUTER JOIN "suppliers" ON "suppliers".id = "movies".supplier_id ORDER BY "suppliers"."name" ASC LIMIT 25

or

SELECT DISTINCT "suppliers".*, "movies".* FROM "movies" LEFT OUTER JOIN "suppliers" ON "suppliers".id = "movies".supplier_id ORDER BY "suppliers"."name" ASC LIMIT 25

Comments and changes to this ticket

  • Ben Johnson

    Ben Johnson November 13th, 2008 @ 11:05 AM

    • State changed from “new” to “open”

    Yeah, I wish Postgres followed SQL standards. I have limited experience with Postgres but the idea here is that any joins need to also be in the select statement?

  • Ben Johnson

    Ben Johnson November 13th, 2008 @ 11:31 AM

    Also, just to clarify, in the above statement, postgres will only return supplier records, correct? What that statement is saying in MySQL or SQLite is that you want both supplier and movie records returned. If I ran that statement using mysql I would get Supplier objects initialized with Movie data, which doesnt make sense.

  • Ben Johnson

    Ben Johnson November 13th, 2008 @ 12:01 PM

    Right, but that is not what you want to happen when executing a search, you want ONLY Supplier records if you are searching suppliers. Not both suppliers and movies.

    Basically in order to perform a join and search it I have to also include it in the select? Which doesn't make sense, because what if I don't want movie records?

  • Ben Johnson

    Ben Johnson November 13th, 2008 @ 12:19 PM

    Right, I understand that, but when order you are also running a search that returns records, you ONLY want movie records, right?

    If I added in suppliers.* in the select statement you will get movies AND suppliers, correct?

    This is not what you want, unless PostgreSQL does not return supplier records.

  • Ben Johnson

    Ben Johnson November 13th, 2008 @ 12:41 PM

    Yeah ActiveRecord is tricky when its comes to this. Here is what I mean:

    This will return duplicate records:

    User.all(:joins => :orders)
    

    This will not return duplicate records:

    User.all(:include => :orders)
    

    The expected behavior when using Searchlogic is to not return duplicate records. will_paginate does this as well.

    The reason I went with DISTINCT is for calculation operations. Otherwise I could just do a result.uniq! statement.

    I guess my only option is to remove the DISTINCT for PostgreSQL connections.

    Let me think about this a little more, I will try to come up with a nice solution.

  • Ben Johnson

    Ben Johnson November 14th, 2008 @ 12:48 AM

    • State changed from “open” to “resolved”

    Alright, update from the repository, I believe this should work for you. Let me know if you have any problems.

  • Ben Johnson

    Ben Johnson November 14th, 2008 @ 01:07 PM

    Awesome, hope you enjoy it. Thanks for letting me know about this.

  • yolo

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

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

Provides common named scopes and object based searching.

People watching this ticket

Tags

Pages