#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?

  • nikwest

    nikwest November 13th, 2008 @ 11:13 AM

    well, I don't know the exact sql standard, but usually postges is pretty close.

    As far as I know the column one uses in order by (and also group by) needs to be in the select statement. That's just my experience.

  • 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.

  • nikwest

    nikwest November 13th, 2008 @ 11:58 AM

    well, the status right now is that it won't work with postgres.

    The statements I included returns both supplier and movie data (in the first case only the supplier name). I guess mysql and sqllite should return also both records.

    At least for sqllite it works the same:

    benq:moviedb dominik$ sqlite3 db/development.sqlite3 SQLite version 3.4.0 Enter ".help" for instructions sqlite> SELECT DISTINCT "suppliers"."name", "movies".* FROM "movies" LEFT OUTER JOIN "suppliers" ON "suppliers".id = "movies".supplier_id ORDER BY "suppliers"."name" ASC LIMIT 25; Supplier 1|2|dvd2|||Received Movie 2||received|1||DVD|2008-02-29 01:00:00|2008-11-12 14:59:06|2008-11-07 14:56:11||unknown Supplier 1|3|dvd3|||Received Movie 3||received|1||DVD|2008-07-04 02:00:00|2008-11-12 14:59:06|2008-11-07 14:56:11||unknown Supplier 1|4|dvd4|||Received Movie 4||received|1||DVD|2008-06-06 02:00:00|2008-11-12 14:59:06|2008-11-07 14:56:11||unknown Supplier 1|5|dvd5|||Received Movie 5||received|1||DVD|2008-12-05 01:00:00|2008-11-12 14:59:07|2008-11-07 14:56:11||unknown

    I don't know how rails is doing the mapping of the raw data returned by the query. I tried to include the supplier in the fetch using :include => :supplier, but that did not work as expected (i am quite a rails newbie)...

    Hope that helps ...

  • 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?

  • nikwest

    nikwest November 13th, 2008 @ 12:11 PM

    well, sorry, hard to explain for me ;-)

    I have a web page with a table of movies. I use the order_by_link for each column to be able to sort it. In that table I have a column supplier name, which references the supplier name through the relationship movie <<-> supplier

    When I click on the supplier column to order by supplier name I get the error mentioned above. So actually I want movies back, but sorted by its supplier's name.

    Hope that clears it up a bit ;-)

  • 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.

  • nikwest

    nikwest November 13th, 2008 @ 12:35 PM

    Yes, you are correct. I only want movies back ;-)

    If you added suppliers.* to the select statement you get movies and suppliers back, which is not what I want, but what postgres mandates.

    I played around a little bit with the query. Actually the DISTINCT operation requires the inclusion of the supplier.name.

    If I do

    SELECT "movies".* FROM "movies" LEFT OUTER JOIN "suppliers" ON "suppliers".id = "movies".supplier_id ORDER BY "suppliers"."name" ASC LIMIT 5;

    that will work fine with postgres. Is there a particular reason for using DISTINCT? It shouldn't be necessary, at least in my simple case. From the other tools I know besides Rails DISTINCT is usually an additional option one can set for a query, as it could be quite expensive, too.

  • 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.

  • nikwest

    nikwest November 14th, 2008 @ 05:45 AM

    HI Ben,

    I updated searchlogic from trunk. Now it works perfectly. Thanks for developing searchlogic and for that ultra fast fix. Very much appreciated.

  • Ben Johnson

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

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

  • Pak24tv

    Pak24tv December 9th, 2020 @ 01:51 AM

    I am happy to find your distinguished way of writing the post. Now you make it easy for me to understand and implement the concept. Thank you for the post. abtak news live city 41 live city 42 live

  • Faizan Hussain

    Faizan Hussain January 20th, 2021 @ 07:00 AM

    An idea is like a Gold if someone is sincere and gives you good idea then it Gold for you

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