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

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.

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

On Thu, Aug 30, 2012 at 12:23 PM, Arthur Clemens

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.

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

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.

I have added this to the wiki:
https://github.com/yesodweb/yesod/wiki/Select-field-populated-from-da...

Arthur

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))    

Do you have any tips to make this more efficient?

Arthur

Op 3 sep. 2012 om 01:16 heeft "Peter.Beischmidt" <peterbeischm...@gmail.com> het volgende geschreven:

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...
[2] http://hackage.haskell.org/packages/archive/persistent/1.0.0/doc/html...
[3] http://hackage.haskell.org/packages/archive/persistent/1.0.0/doc/html...
[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...

--
Felipe.

Thanks for your elaborate explanation.

Arthur

On Sun, Sep 2, 2012 at 10:27 PM, Felipe Almeida Lessa

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/cdf36de477d32e2b7a4d0b4...

--
Felipe.

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

Arthur

Op 4 sep. 2012 om 01:27 heeft Felipe Almeida Lessa <felipe.le...@gmail.com> het volgende geschreven:

Reinstalling yesod fixed the compile error.
I followed this handy
description: http://meadowstalk.com/post/upgrade-to-yesod-1-1

Arthur

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

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.
Em 05/09/2012 07:08, "Arthur Clemens" <arthurclem...@gmail.com> escreveu:

On 5 sep. 2012, at 15:31, Felipe Almeida Lessa 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 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).

Is this documented somewhere?

That would be wonderful.

Arthur

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.

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

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...
[2] http://hackage.haskell.org/packages/archive/persistent/1.0.1/doc/html...

--
Felipe.

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

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

Something you can share as best practice / cookbook?

Arthur

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.