Get a list of some Entity field

[Conee]

Hi,

I need help to get values from some Entity,  to display these values in a

hamlet file in select form as options.

I have an Address entity that has a City field and I want to take all addresses from the database and place only unique cities from addresses in the list. I'm confused and I can't do it.

My address entity :

Address json

  state Text sqltype=varchar(255)

  city Text sqltype=varchar(255)

  street Text Maybe sqltype=varchar(255)

  number Text sqltype=varchar(255)

  deriving Show Typeable

Function to get from database :

getAllAddress :: DB [Entity Address]

getAllAddress = selectList [] [Desc AddressCity]

Now I want something like this :

cities :: [Entity Address] -> [T.Text]

  

and list [city1, city2 ...] must be unique, to not have duplicate values.

Thanks,

Nemanja

[jsch...@gmail.com]

Hi,

here is some code, I use for such "dynamic" select fields:

selectFieldSpecial = selectField roles
   where roles = optionsForData :: Handler (OptionList AOIRole)

-- More advanced code for generating the options. It also works simpler.
-- Now I can use .val() in jQuery to select an option...
optionsForData :: (MonadHandler m, Show a, Enum a, Bounded a) => m (OptionList a)
optionsForData = do
   (OptionList options _) <- optionsEnum -- library function to convert Enum to OptionList
   let fixedOptions = map (\o -> o { optionExternalValue = (pack.show.optionInternalValue) o }) options
   return $ mkOptionList fixedOptions

And you can use this special selectField like this:

myForm = TheForm
   <$> …
   <*> …
   <*> areq selectFieldSpecial (withSmallInput $ bfs1 "Rolle" "fRole" ["js-property-input"]) Nothing

You'll find the documentation to these functions on hackage... I'd start with reading about selectField and OptionList if you're already familiar with forms in general.

Hope that helps,

- Jakob

[Conee]

Thanks a lot for the reply Jakob.

I will review your code although I have to admit that it is a bit complicated for me because I am a beginner.

I see that you are using applicative forms in this example. My case is that I pre-created forms in the hamlet file:

getManUserR :: Handler Html

getManUserR = do

   (_, user) <- requireAuthPair

   ms <- runDB getAllMan

   let cats = getAllCat

   ads <- runDB getAllAddress

   -- let cities = addressCity ads   -- Couldn't match expected type ‘Address’ with actual type ‘[Entity Address]’

   print $ show cats

   defaultLayout $ do

     setTitle "User manifestations"

     $(widgetFile "man-user")

---Just a part of man-user.hamlet----

<!-- BEGIN FILTER BY CITY -->

<div class="col-md-3">

   <h4>By city:

   <div class="form-group">

     <select class="form-control" id="sel1" name="City">

       <option>--Please choose an city--

       $forall c <- cities

         <option>#{c}

and then try to use input forms: https://www.yesodweb.com/book/forms#forms_input_forms :

postManUserR :: Handler Html

postManUserR = do

(_, user) <- requireAuthPair

filters <- runInputPost $ ManFilter

    <$>iopt (checkboxesFieldList cats) "Cat"

    <*>iopt (selectField cities) "City"

    <*>iopt textField "Search"

   defaultLayout [whamlet|<h1>#{show $ filters}|]

    where

    cats::[(Text, Category)]

    cats = [("Sport", Sport), ("Concert", Concert ), ("Theater", Theater)]

    cities = do

       items <- runDB $ selectList [] [Desc AddressCity]

      optionsPairs $ map (\c -> (addressCity $ entityVal c, addressCity $ entityVal c)) items

From the above mentioned entity I want to take only unique cities from database and display them,

the user would select the option and I would get that answer(city) in the post handler.

I don't know if I imagined it badly but it's an idea.

Thank you ,

Nemanja

[jsch...@gmail.com]

OK, I've not used Input Forms yet. It's a good thing for custom layouts but the book mentions problems with invalid input. Right, here you have to create the options manually in hamlet.

In order to SELECT UNIQUE city_name …

I see a few options but no perfectly easy way.

You can use raw SQL (which can break when the DB structure changes): https://www.yesodweb.com/book/persistent#persistent_persistent_raw_sql

You can use Esqueleto which is also mentioned in the persistent book. But I think it's complicated.

You can use Haskell, something like cities <- nub <$> selectList [] [Desc AddressCity]

(why do you sort in descending order?)

Note that nub only works with sorted lists.

But I think that solution is very memory intense when you have a lot of customer addresses!

[jsch...@gmail.com]

PS: I mistyped, I mean:

SELECT DISTINCT city_name FROM table

An Persistent data SelectOpt = Asc … | … | Distinct

would be a really neat solution, but it doesn't exist.

Probably for a goo reason, Michael Snoyman?

[Conee]

Hi, thank you again.

Yes, I just tried the first option you wrote that is rawSql.

I didn't try the other two options because of the things you mentioned, but if this doesn't work, I'll have to turn to these other options.

rawSql is a great option and I tried it in two ways but I got the same error with both:

1. I first tried with :

getManUserR :: Handler Html

getManUserR = do

   cities <- runDB getUniqueCity

   defaultLayout $ do

   setTitle "User manifestations"

     $(widgetFile "man-user")

getUniqueCity :: DB [Entity Address]

getUniqueCity = rawSql "SELECT DISTINCT city FROM address" []

2.The second way is just another query:

getUniqueCity :: DB [Entity Address]

getUniqueCity = rawSql "SELECT city FROM address GROUP BY city" []

For both cases I got the same error:

[Error#yesod-core] ConnectionError {errFunction = "query", errNumber = 1064, errMessage = "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`id`, `address`.`state`, `address`.`city`, `address`.`street`, `address`.`numbe' at line 1"} @(yesod-core-1.6.18.8-Dh7X2KxJKQo8cWPZxpWOd9:Yesod.Core.Class.Yesod src/Yesod/Core/Class/Yesod.hs:688:5)

Let me mention that when I run these queries in the console, they both work well and return unique cities from the database.

[jsch...@gmail.com]

I guess the type of getUniqueCity is wrong. If you select only the column city that it should be DB [Text], right? Though, I'm not sure if Text is right, maybe there is a DB wrapper type for this... please try to infer that from the examples in the book or on hackage :)

[Conee]

Yes, I assumed that earlier, but also because there is the following error:

getUniqueCity :: DB [Text]

getUniqueCity = rawSql "SELECT DISTINCT city FROM address" []

error :

Could not deduce (persistent-2.10.5.3:Database.Persist.Sql.Class.RawSql

                                  Text)
        arising from a use of ‘rawSql’
      from the context: MonadUnliftIO m
        bound by the type signature for:
                   getUniqueCity :: DB [Text]

[jsch...@gmail.com]

I found a example in my codes:

getAlbumSeries :: Handler [Text]
getAlbumSeries = do
   series <- runDB $ rawSql "select distinct series from album \
                               where series is not null \
                               order by series" [] :: Handler [Single Text]
   return $ map unSingle series

For some reason there must be a additional type annotation.

[Conee]

Here I am to reply and thank you,

it looks like you saved me, Jakob.

After your help, I now have the following:

getManUserR :: Handler Html

getManUserR = do

    (_, user) <- requireAuthPair

    ms <- runDB getAllMan

    let cats = getAllCat

    cities <- getUniqueCity

    liftIO $ print cities                 --printing to console : ["city1", "city2", "city3"]

    defaultLayout $ do

      setTitle "User manifestations"

      $(widgetFile "man-user")

getUniqueCity :: Handler [Text]

getUniqueCity = do

         c <- runDB $ rawSql "SELECT DISTINCT city FROM address" [] :: Handler [Single Text]

         return $ map unSingle c

I hope that this is the right way and that I will be able to finish my form that I am struggling with.

I want to thank you very much again,

all the best,

Nemanja.

[jsch...@gmail.com]

Your welcome ;) Have fun!

- Jakob