Help needed to fill a selectFieldList with entries from database

[Arthur Clemens]

I have the feeling I am closing in on the solution, but I don't have it quite right yet.

The idea is that in a "new/edit product" form, the content manager can optionally choose the category the product belongs to.

So the model has:

Product

    ident Text

    category CategoryId Maybe

    ...

And in my applicative form I have:

    <*> aopt (selectFieldList categories) "Category" (productCategory <$> mproduct)

So I need "categories" to fetch all categories from the database and generate a list of (Text, CategoryId) tuples. This would be something along the lines of:

getCategories :: GHandler App App [(Text, CategoryId)]                             

getCategories = do

    entities <- runDB $ selectList [] [Asc CategoryTitle]

    return $ map (\cat -> (categoryTitle $ entityVal cat, entityKey cat)) entities

I am not entirely sure if the categoryId is indeed the same as entityKey, so any corrections are welcome.

Then, where do I retrieve the tuples? I must do

    categories <- getCategories

so I assume I cannot put it inside a form function. Should I do this in a Handler, and then pass the categories to the form function? For instance:

getAdminAddProductR :: Handler RepHtml

getAdminAddProductR = do

    categories <- getCategories

    ((result, formWidget), enctype) <- runFormGet $ productForm Nothing categories

Any help to get me to understand this better is appreciated.

Arthur

[Felipe Almeida Lessa]

selectFieldList is actually a simplified API for selectField when you
have a simple list of items. However, that's not your case =).

Instead of selectFieldList, you want to use selectField. Note that it
takes as argument a GHandler that produces an OptionList. In order to
create this GHandler, you may take your already existing function and
change it into

getCategories :: GHandler App App (OptionList CategoryId)

getCategories = do
entities <- runDB $ selectList [] [Asc CategoryTitle]

optionPairs $ map (\cat -> (categoryTitle $ entityVal cat,
entityKey cat)) entities

or you may just use optionsPersist

getCategories :: GHandler App App (OptionList Category)
getCategories = optionsPersist [] [Asc CategoryTitle] categoryTitle

This definition is so simple that I recommend putting on a let or
where clause of your form definition.

Does this solve your problem?

Cheers, =)

--
Felipe.

[Arthur Clemens]

The short version of getCategories does not work out of the box, I will need to investigate why.
But the longer version helps me out now.

Thanks a million.

Arthur

[Felipe Almeida Lessa]

On Thu, Aug 30, 2012 at 12:23 PM, Arthur Clemens
<arthur...@gmail.com> wrote:
> The short version of getCategories does not work out of the box, I will need to investigate why.
> But the longer version helps me out now.

Actually, I wrote the wrong type signature, it should read

getCategories :: GHandler App App (OptionList (Entity Category))

and you'll need to use something like

<*> (entityKey <$> aopt (selectField getCategories) "Category"
(productCategory <$> mproduct))

in order to get just the CategoryId as you wanted before.

Cheers,

--
Felipe.

[Arthur Clemens]

The product listing page (hamlet template) now has a category id for each product. If I want to show the categoryTitle (instead of the id), do I need to do a DB call to fetch the Category for each cateory id?

Arthur

[Felipe Almeida Lessa]

On Thu, Aug 30, 2012 at 8:32 PM, Arthur Clemens <arthur...@gmail.com> wrote:
> The product listing page (hamlet template) now has a category id for each product. If I want to show the categoryTitle (instead of the id), do I need to do a DB call to fetch the Category for each cateory id?

On your original code you were taking just the CategoryId, which is
fine if that's all you want. In general, however, you want the value
as weel (the Category), and that's why optionPairs works with "Entity
Category".

So, if you want to use the Category as well, just forget about the
"entityKey <$>" thing on my last e-mail and then you'll get a "Field
App App (Entity Category)". Then you may normally use "entityKey" to
get the CategoryId as before, and "entityVal" to get the corresponding
Category.

Cheers, =)

--
Felipe.

[Arthur Clemens]

I have added this to the wiki:

https://github.com/yesodweb/yesod/wiki/Select-field-populated-from-database

Arthur

[Arthur Clemens]

On 2 sep. 2012, at 16:49, Arthur Clemens wrote:

I have added this to the wiki:

https://github.com/yesodweb/yesod/wiki/Select-field-populated-from-database

and also a multi select variant

https://github.com/yesodweb/yesod/wiki/Multi-select-field-populated-from-database

This probably needs some optimization - I find some parts quite verbose.

Arthur

[Peter.Beischmidt]

The definition of productsAndCategories will be rather inefficient because it runs a query for every product that is associated with a category.  Or do they get bundled?

productsAndCategories :: GHandler App App [(Product, Maybe Category)]
productsAndCategories = runDB $ selectList [] [Asc ProductName] >>= mapM (\(Entity _ p) -> do
    category <- case (productCategory p) of
        Just c -> do
            get c
        Nothing -> return Nothing
    return (p, category))    

[Arthur Clemens]

Do you have any tips to make this more efficient?

Arthur

[Felipe Almeida Lessa]

On Sun, Sep 2, 2012 at 8:24 PM, Arthur Clemens <arthur...@gmail.com> wrote:
> Do you have any tips to make this more efficient?

This is persistent's greatest weakness: it's difficult to do joins.


There's a completely generic way of doing joins on
Database.Persist.Query.Join [1]. However, the runJoin from this
module will run 2 SELECTs -- way better than your example but still
suboptimal. (And it also has the same drawbacks as the following
alternative.)


There's a SQL-specific way of doing joins on
Database.Persist.Query.Join.Sql. This runJoin will actually do a
JOIN. However there are many drawbacks. First of all, it's a PITA to
use. For your example, you'd need to do something like (completely
untested -- actually, I don't know if runJoin applies to your example)

runDB $ runJoin $ SelectOneMany [] [] [] [Asc ProductName]
(ProductCategory <-.) productCategory False

and even then you'll have to massage the resulting data type. The
second biggest drawback is that it works only for simple joins on two
tables. For example, it won't work at all for a many-to-many
relationship.

So, these are the only two ways of safely doing JOINs on persistent
without resorting to doing the JOIN by yourself (like you did).


There's a third, unsafe way: using rawSql [3]. Like the name implies,
you are able to directly write SQL. This comes with all the caveats
of writing raw SQL queries that we're used to seeing. Also, it
wouldn't work for your particular example: you're running an OUTER
JOIN and currently rawSql does not have support for Maybes [4].


And then there's the fourth, hardest and unsafest way: using withStmt
[5]. It's actually not hard to use, but completely manual. There are
so many ways of shooting yourself in the foot that I won't even go
into details.


So that's it: there's no good, recommend way of doing JOINs with
persistent right now. However, we're not giving up yet! There *are*
ways of getting the job done and we do get the job done, but we're
searching for nice solutions! For example, there has been some
renewed interest on HaskellDB and how to use it with persistent. So
keep tuned and don't be afraid to hack =D.


Cheers, =)


[1] http://hackage.haskell.org/packages/archive/persistent/1.0.0/doc/html/Database-Persist-Query-Join.html
[2] http://hackage.haskell.org/packages/archive/persistent/1.0.0/doc/html/Database-Persist-Query-Join-Sql.html
[3] http://hackage.haskell.org/packages/archive/persistent/1.0.0/doc/html/Database-Persist-GenericSql.html#v:rawSql
[4] However, I may fix this bug soon since I may need this feature myself.
[5] http://hackage.haskell.org/packages/archive/persistent/1.0.0/doc/html/Database-Persist-GenericSql-Raw.html#v:withStmt

--
Felipe.

[Arthur Clemens]

Thanks for your elaborate explanation.

Arthur

[Felipe Almeida Lessa]

On Sun, Sep 2, 2012 at 10:27 PM, Felipe Almeida Lessa
<felipe...@gmail.com> wrote:
> There's a third, unsafe way: using rawSql [3]. Like the name implies,
> you are able to directly write SQL. This comes with all the caveats
> of writing raw SQL queries that we're used to seeing. Also, it
> wouldn't work for your particular example: you're running an OUTER
> JOIN and currently rawSql does not have support for Maybes [4].
>

> [4] However, I may fix this bug soon since I may need this feature myself.

FWIW, I've released persistent-1.0.1 which does have rawSql support
for OUTER JOINs [1].

Cheers, =)

[1] https://github.com/yesodweb/persistent/commit/cdf36de477d32e2b7a4d0b49e74620d750ceb6d1

--
Felipe.

[Arthur Clemens]

I now get compile errors "Ambiguous occurrence 'selectList'" etcetera. Are there more updates I should install?

Arthur

[Arthur Clemens]

Reinstalling yesod fixed the compile error.

I followed this handy description: http://meadowstalk.com/post/upgrade-to-yesod-1-1

Arthur

[Arthur Clemens]

I still have a hard time building the table using sql commands, due to the limited support in rawSql.

It would be wonderful if you also could build INNER JOIN and nested JOIN clauses.

Arthur

[Felipe Almeida Lessa]

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). But you don't need to use "??", you may use just Singles (although it's painful to do so). Why weren't you able to do explicit joins?

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.

Cheers!

--
Felipe – enviado do meu Galaxy Tab.

[Arthur Clemens]

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

[Felipe Almeida Lessa]

On Wed, Sep 5, 2012 at 5:00 PM, Arthur Clemens <arthur...@gmail.com> 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.

[Robert Lee]

I've taken to building views in Postgres to solve some of these sticky
problems. Type safe (models) and works like a charm.

[Arthur Clemens]

On 5 sep. 2012, at 22:20, Robert Lee wrote:

> I've taken to building views in Postgres to solve some of these sticky
> problems. Type safe (models) and works like a charm.

Something you can share as best practice / cookbook?

Arthur

[Robert Lee]

I can put something together. I would like to encourage a discussion
regarding best practices for applying Yesod to applications that
significantly rely on SQL. We can toss some ideas around
comparing/contrasting Yesod and Postgres for those cases where Yesod
code may be the preferred choice and other cases where the programmer
may want to employ Postgres to do some heavy lifting.

Fortunately this is a subject where there exists a body of good general
advice for the development of SQL reliant applications independent of
language. However Yesod has its own set of unique issues given its
approach to database boundaries. It's along these lines that we might
consider writing a Yesod specific "manual of SQL best practice" with due
reference to relevant generic material.