Development discussion for the Yesod Web Framework. ()

Arthur Clemens | 5 Sep 2012 22:00

Re: Help needed to fill a selectFieldList with entries from database

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 * /* ?? */ \

results in:

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.

Arthur

headers

Felipe Almeida Lessa | 5 Sep 2012 22:12

Re: Help needed to fill a selectFieldList with entries from database

On Wed, Sep 5, 2012 at 5:00 PM, Arthur Clemens
<arthurclemens@...> wrote:
> 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 you changed your type signature to [(Entity Product, Entity
Category)] and your SQL statement to "SELECT ??, ?? ...", I guess it
would work.  Does this work?

However, you will have to group the items yourself.  Currently there's
no explicit support for grouping queries on rawSql.

> If I remove ?? then the tokens get inserted at the end of the query string,
> resulting in an error of course.

Removing the ?? should result in a runtime error saying that there are
more entities on the result than ?? wildcards on the query.

> > But you don't need to use "??", you may use just Singles (although it's
> > painful to do so).
>
> Is this documented somewhere?

I thought there was something on rawSql's docs, but there isn't.  But
there's some text on Single itself [1] and you may see the instances
of the RawSql class [2] (although just the latest persistent exports
it, so if you were looking to an old doc you wouldn't know which
instances exist).  But, anyway, I don't think you should use Single
here since I guess Entity would work.

Cheers, =)

[1] http://hackage.haskell.org/packages/archive/persistent/1.0.1/doc/html/Database-Persist-GenericSql.html#t:Single
[2] http://hackage.haskell.org/packages/archive/persistent/1.0.1/doc/html/Database-Persist-GenericSql.html#t:RawSql

--

-- 
Felipe.