SQL syntax error when executing the query to get n random content items

[aminmohammadi05]

Hi, would you please do a favor to me? I want to run this query on Orchard that return 3 random products:
SELECT TOP (3) * FROM ContentItemIndex WHERE (ContentType = 'Product') AND (Published = 1) ORDER BY newid()
The query works on SQL server very good but in Orchard gives this error:
Syntax error, expected: OVER, AS, id_simple, ,, HAVING, ORDER, LIMIT, OFFSET, ), UNION, ;, SELECT, FROM, WHERE, GROUP at line:0, col:15

[hishamco]

Which version of OC you are using?

[aminmohammadi05]

Orchard 1.4

[hishamco]

Could you please try the latest bits? FYI there was a change happen in the SQL Parser recently I thought it was the issue, for that please try with what I suggest and let us know

[aminmohammadi05]

I am using the nuget package already and I believe it is the latest one. do you mean that I should use OrchardCMS/OrchardCore in this repository?

[lampersky]

@aminmohammadi05 there are few limitations in current grammar/parser as a workaround please use sth like this:

SELECT newid() as randomId, cii.ContentType FROM ContentItemIndex cii
WHERE (ContentType = 'Product') AND (Published = 1) 
ORDER BY randomId limit 3

• TOP clause is SQL Server specific please use limit instead,
• you can't use function in order by clause,
• you can't combine * with specified column list, you need to list them all.

[aminmohammadi05]

I think the dashes of the randomId should be removed before conversion to varchar

[lampersky]

@aminmohammadi05 try to get rid of that additional randomId column (by moving it to having clause which allows to use functions there), this query is a bit tricky and I would not use it in prod, but prepared it for you just for testing purpose, if this will work, then I would suggest to use latest orchard version and rewrite query to use subquery or cte

SELECT cii.ContentType, cii.ContentItemId
FROM ContentItemIndex cii
WHERE (ContentType = 'Product') AND (Published = 1)
GROUP by cii.ContentType, cii.ContentItemId
HAVING cii.ContentItemId > convert(varchar(36), newid())
ORDER BY ContentItemId limit 3

please note, you need to specify column list twice, one in select clause and then in group by clause

when you will upgrade to latest version, use this query instead

with test as (
	select newid() as randId, ContentItemId, DisplayText
	from ContentItemIndex
        order by randId limit 3
)
select ContentItemId, DisplayText from test 

[aminmohammadi05]

Again when I removed the dashes I got the same error.
The goal of this piece of code is returning 3 random products. If other approaches are there to do so I will appreciate.

[aminmohammadi05]

The error is gone but no result!!

[aminmohammadi05]

I changed the Schema to this:

{
    "type": "object",
    "properties": {  
        "Name" : {
            "type" : "string",
            "description" : "This is the name of the product."
        },
      "Id" : {
            "type" : "Integer",
            "description" : "This is the Id of the product."
        },
       "ContentItemId" : {
            "type" : "string",
            "description" : "This is the ContentItemId of the product."
        }
    }
}

But again the same result