Get a list of some Entity field

29 views
Skip to first unread message

Conee

unread,
Feb 22, 2021, 12:54:24 PM2/22/21
to Yesod Web Framework
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

unread,
Feb 23, 2021, 5:25:10 AM2/23/21
to Yesod Web Framework
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

unread,
Feb 24, 2021, 9:57:16 AM2/24/21
to Yesod Web Framework
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

unread,
Feb 25, 2021, 3:03:16 PM2/25/21
to Yesod Web Framework
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

unread,
Feb 25, 2021, 3:09:51 PM2/25/21
to Yesod Web Framework
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

unread,
Feb 26, 2021, 9:20:34 AM2/26/21
to Yesod Web Framework

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

unread,
Feb 27, 2021, 3:48:00 AM2/27/21
to Yesod Web Framework
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

unread,
Feb 27, 2021, 7:45:21 AM2/27/21
to Yesod Web Framework
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

unread,
Feb 28, 2021, 3:28:34 AM2/28/21
to Yesod Web Framework
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

unread,
Mar 1, 2021, 9:57:08 AM3/1/21
to Yesod Web Framework
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

unread,
Mar 2, 2021, 3:40:32 AM3/2/21
to Yesod Web Framework
Your welcome ;) Have fun!
- Jakob
Reply all
Reply to author
Forward
0 new messages