On 5 sep. 2012, at 15:31, Felipe Almeida Lessa wrote:
You may use whatever SQL you want when using rawSql, it only cares about table names when you use "??" (it must be the same as your entity name, which means no self-joins).
I was convinced that the errors I got were due to the limited capabilities of the rawSQL => SQL syntax.
I see now that it is more powerful. But I don't fully understand the magic going on.
productsAndCategories :: GHandler App App [Entity Product]
productsAndCategories = runDB $ rawSql
"SELECT ?? \
\FROM Product, Category \
\LEFT OUTER JOIN ProductCategory \
\ON Product.id = ProductCategory.product \
\AND Category.id = ProductCategory.category" 
results in a long list of [Entity Product] items, as expected.
But I need to get [(Entity Product, [Entity Category])].
Just changing the ?? doesn't work, this will result in all kinds of errors.
Normally I would use DISTINCT but that involves removing the ??.
If I remove ?? then the tokens get inserted at the end of the query string, resulting in an error of course.
And trying to outsmart doesn't work either. Writing
SELECT * /* ?? */ \
rawSql: wrong number of columns, got 7 but expected 2 (2 columns for an 'Entity' data type).
But you don't need to use "??", you may use just Singles (although it's painful to do so).
Is this documented somewhere?
I'll release in the few days a library that'll hopefully solve most of our raw SQL problems, so stay tuned =). I hope it'll solve yours, too, and I'm eager for feedback.
That would be wonderful.