4

Resolved

Projector module in 1.7 throws error if no Sort Criteria is specified

description

Default sort is "Order by Id", which causes SqlException: Ambiguous column name 'Id' if tables are joined:

could not execute query

[ select distinct TOP (@p0) contentite0_.Id as col_0_0_ from Orchard_Framework_ContentItemVersionRecord contentite0_ inner join Orchard_Framework_ContentItemRecord contentite1_ on contentite0_.ContentItemRecord_id=contentite1_.Id inner join Orchard_Framework_ContentTypeRecord contenttyp2_ on contentite1_.ContentType_id=contenttyp2_.Id where (contenttyp2_.Name in ('BlogPost')) and contentite0_.Published=1 order by Id ]

[SQL: select distinct TOP (@p0) contentite0_.Id as col_0_0_ from Orchard_Framework_ContentItemVersionRecord contentite0_ inner join Orchard_Framework_ContentItemRecord contentite1_ on contentite0_.ContentItemRecord_id=contentite1_.Id inner join Orchard_Framework_ContentTypeRecord contenttyp2_ on contentite1_.ContentType_id=contenttyp2_.Id where (contenttyp2_.Name in ('BlogPost')) and contentite0_.Published=1 order by Id]

comments

Jetski5822 wrote Jul 1, 2013 at 4:04 PM

Hey interesting, do you by any chance have a small walk through repo I could use?

x0r wrote Jul 1, 2013 at 4:59 PM

Actually it's very easy to replicate. It's the latest 1.x orchard build
  1. Create a query in Queries (/admin/projector)
  2. Add new filter - Content Type: BlogPost
  3. hit Preview

x0r wrote Jul 1, 2013 at 4:59 PM

actually i got the same error with any content type...

x0r wrote Jul 1, 2013 at 5:02 PM

i was just thinking if i missed some migration. i get the same error ( Ambiguous column name 'Id'. ORDER BY items must appear in the select list if SELECT DISTINCT is specified) if i try to "Upgrade to 1.7." -> Fields (1.5)

jao28 wrote Jul 16, 2013 at 5:14 PM

I still had an issue with this (even when pulling from 1.x), had to change the following code:

Line 297 in this file Orchard.ContentManagement.DefaultHqlQuery.cs change from:

sb.Append("order by Id");

to:

sb.Append("order by civ.Id");

Otherwise it didn't know what the "Id" column was and threw the same error as up above. Have not yet seen other negative issues as a result of the change.

I may throw it under a new ticket so it get's attention for 1.7 release.

sebastienros wrote Jul 16, 2013 at 9:12 PM

Fixed in changeset 4b80f3ec6567

sfmskywalker wrote Mar 28 at 1:28 AM

Fixed in changeset 0249ded5d35efe6abf49d424aa135e36617a5052