search

prowdsponsor / esqueleto

Bare bones, type-safe EDSL for SQL queries on persistent backends.
http://hackage.haskell.org/package/esqueleto
BSD 3-Clause "New" or "Revised" License
178 stars 51 forks source link

Selects with fields of type list #106

Closed sheganinans closed 9 years ago

sheganinans commented 9 years ago

Not a bug, I'm just not sure exactly how to solve this problem with Esqueleto.

Assume I have a DB something like this: 

share [mkPersist sqlSettings, mkMigrate "migrateAll"] [persist|
Category
    name Text
    deriving Eq Ord Show
SuperCategory
    name Text
    categories [CategoryId]
    deriving Eq Ord Show
|]

What I'm trying to do is write a function that takes a single argument cs :: [CategoryId] and returns scs :: [SuperCategory] where all the SuperCategories in scs have in their categories field one or more of the items cs. Persistent has a "contains" and "not contains" (<-. and /<-. respectively), but it doesn't do quite what I want. Any ideas? Thanks!

meteficha commented 9 years ago

Short answer: you can't.

Long answer: persistent's lists are a hack on a relational database. Think of them as strings that are serialized to the DB. You can't do much with them on the SQL side. So, they're useful only if you'll never try to look at them from inside a query.

As this isn't your case, you'll have to go through the orthodox way of implementing N-to-M relationships:

Category
    name Text
    deriving Eq Ord Show
SuperCategory
    name Text
    deriving Eq Ord Show
PickaName
    superCategory SuperCategoryId
    category CategoryId
    deriving Eq Ord Show
sheganinans commented 9 years ago

Ok, makes sense. Sadly I might be able to do something like that but I was wondering if there are alternatives, reason being is that the cs argument is being passed in as a URL argument (I'm using Yesod), so having another DB table wouldn't be very satisfactory to me. I guessed that persistent wont be able to do it, so there is nothing that Eqsueleto can do? I'm using postgres, so maybe postgres-simple might do the job?

meteficha commented 9 years ago

I don't think what you want is possible without a whole lot of pain. But if you show me the raw SQL query you need, I can help you with the esqueleto side of things.

Closing this issue.

sheganinans commented 9 years ago

Thanks, I'll try a few things. If all else fails, you are right I can use raw SQL for my queries.